故障信息:接到用户电话反应某MES系统业务反映慢,登陆数据库所在主机(Windows+Oracle11.2.0.1),发现cpu使用率一直100%,登陆数据库查询等检查操作时也比较缓慢。
总的问题处理思路:
1.查当前等待事件
2.分析AWR中TOP等待cursor: mutex S 等待事件,Time Model Statistics及SQL ordered by Version Count
3.查cursor: mutex S 的P1值对应SQL HASH_VALUE找出相应SQL语句是insert into sys.aud$
4.紧急处理:刷共享池后CPU使用率下降到正常范围
5.监听日志分析,未出现连接的会话明显的增长
6.查异常SQL不能共享原因
7.后续处理措施:停机修改 audit_trail=NONE
8.从MOS上找到对应的BUG信息论证此问题
详细处理过程:
1.远程上去看了下,任务管理器能看到的CPU使用率一直100%,其它内存磁盘等使用正常;登陆数据库查看等待事件主要是cursor: mutex S;又检查切了下归档、做检查点/alert日志等看看均正常,马上17点了,收集了一小时AWR,ASH收的时候报错就没拿到;
2.AWR 分析如下:
--DB Time一看就异常了,继续往下: Cache Sizes/Load Profile/内存使用均未现异常,TOP事件处就异常了:
--可以发现主要等待事件cursor: mutex占了60%,等待次数非常多,library cache lock占25%,继续查看Time Model Statistics部分,
--可以发现主要的时间消耗在连接管理、解析,可是看前面的load profile,平均每秒登陆数量是0.1.不太正常。继续查看sql部分:
--SQL执行部分没大异常,但是 Version Count就异常了,但是没有SQL 文本。继续查找……
3.排查TOP事件cursor: mutex S事件及Version Count异常的SQL_ID 4vs91dcv7u1p6
对于cursor: mutex S事件和P1/P2/P3值,MOS文档:WAITEVENT: "cursor: pin S" Reference Note (文档 ID 1310764.1)上有写, Versions 10.2.0.1 - 11.2.0.2 P1 = idn P2 = value P3 = where (where|sleeps in 10.2) idn Mutex identifier (gives hash_value of SQL) This is the mutex identifier value which matches to the HASH_VALUE of the SQL statement that we are waiting to get the mutex on. 简单来说就是P1的值就是blocker进程的SQL HASH_VALUE
可以发现最近两小时主要等待事件cursor: mutex S被捕获到V$ACTIVE_SESSION_HISTORY中141945次,对应的P1是914163366(查最近2小时的有143424次,也就是问题主要出现在最近一小时)。根据P1值对应的SQL HASH_VALUE来查出导致此等待的SQL语句及进程:
--可以发现出问题的是insert into sys.aud$,此语句有version_count高达5546个,也验证了AWR 中SQL Version Count中SQL的问题。
4.紧急处理办法:alter system flush shared_pool; 刷共享池几分钟后CPU使用率恢复正常。当然这里首先是对这个业务我有一些了解正常时候业务压力不算太大,其次当时系统cpu100%业务也进行不了,所以果断刷了共享池,高负载多业务并存的系统慎用。
5.监听日志分析, 分析了当天及出现问题的1小时的监听日志,未出现连接会话明显的增长 使用语句:
6.后续处理措施:停机修改 audit_trail=NONE,关闭审计。 --当前版本是11.2.0.1,还建议升级(当然近期做不了),升级到11.2.0.3就可以修复后面提到与此类似的BUG 11936699。 查看当前的配置是使用了默认的审计级别audit_trail=DB:当晚又协调了停机时间,将audit_trail关闭,audit_trail=none;
7.查异常SQL不能共享原因 在紧急处理alter system flush shared_pool;几分钟后,系统CPU使用率已经恢复正常,后续又查询4vs91dcv7u1p6 insert into sys.aud$的多版本情况,发现又有了10几个version,从v$sql_shared_cursor查询游标失效原因是optimizer_mismatch,未找到是什么原因引起.
8.从MOS上找到对应的BUG信息论证此问题关于游标不能共享原因的分析: 从MOS上以aud$ cursor: mutex S关键字搜索,有文档:
BUG中涉及的数据库版本、SQL语句也与本次相同,不同之处是本次数据库未完全hang住,还是能登陆的,只是操作时反应比较慢。 由于当时没有相应的errorstack Trace来核对 call stack,只能说与此BUG比较类似,不能完全确认。 当然此BUG的后续处理里介绍除了打补丁,也是修改audit_trail=DB -> audit_trail=OS; 我直接简单粗爆的关闭:audit_trail=DB -> audit_trail=NONE,过了将近一周复查一下未再出现high version count的insert into aud$ SQL语句。 参考文档: