11g bug event 'cursor: mutex S'-引发的CPU冲高问题

问题背景：客户反应数据库服务器CPU占用过高

1> 确认问题根源
登录客户DB服务器： top 查看当前负载（几乎100%）
top - 10:47:55 up 29 days, 21:51, 3 users, load average: 54.02, 54.00, 54.00
Tasks: 528 total, 55 running, 473 sleeping, 0 stopped, 0 zombie
Cpu(s): 99.2%us, 0.7%sy, 0.0%ni, 0.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 65977660k total, 23209792k used, 42767868k free, 349208k buffers
Swap: 8232952k total, 0k used, 8232952k free, 20103832k cached

2> 查看CPU top sql

EVENT事件为 cursor：mutex S （这里做个标记）某个进程以SHRD S mode申请一个Mutex，而该Mutex要么被其他进程已EXCL X mode所持有，要么其他进程正在更新mutex 上的Ref Count。

3> 根据sql_id 查看问题sql

SQL>select * from table(dbms_xplan.display_awr('66xctbkxrrt7v'));
no rows selected --(查不出sql)
查不出问题sql，从event：cursor：mutex S入手

查看AWR报告

4> 查看客户DB版本

查看官方MOS
GOAL
Customer upgraded the DB Repository for Oracle Waveset from Oracle 10g to Oracle 11g.
Cursor sharing was set to SIMILAR as per the Oracle Waveset Documentation in 10g.
Child cursors were getting released in 10g. Customer noticed child cursors were not getting released in 11g.

Cursor Mutex S wait event and too many child cursors open when cursor sharing is set to similar.
As per the documentation in MOS 1169017.1 cursor sharing should be exact or force.

SOLUTION
The recommendation to set the 'cursor_sharing' option as 'SIMILAR' was to remedy the known issues when 'cursor_sharing' was set to be the default 'EXACT' option in previous versions of Oracle Waveset and Oracle 10g as repository.

Since the Oracle 11g database deprecates the 'SIMILAR' option, the recommendation now is to set 'cursor_sharing' to 'FORCE'.

REFERENCES
BUG:13983028 - RECOMMENDATION FOR CURSOR_SHARING PARAMETER WITH ORACLE 11G REPOSITORY --确认11.2.0.1引发bug

这个BUG 影响 12.2 以下的主要版本，包括 11.2.0.2 和 11.2.0.4。（仅供参考）
确认的影响版本是：
11.2.0.3.5 Database Patch Set Update
11.2.0.3.4 Database Patch Set Update
11.2.0.3 Bundle Patch 11 for Exadata Database
11.2.0.2.9 Database Patch Set Update
11.2.0.2.8 Database Patch Set Update
11.2.0.2 Bundle Patch 18 for Exadata Database
11.2.0.3 Patch 11 on Windows Platforms
11.2.0.2 Patch 22 on Windows Platforms

确认的修复版本是：
12.2.0.1 (Base Release)
12.1.0.2 (Server Patch Set)
11.2.0.4 (Server Patch Set)
11.2.0.3.6 Database Patch Set Update
11.2.0.3 Bundle Patch 16 for Exadata Database
11.2.0.2.10 Database Patch Set Update
11.2.0.2 Bundle Patch 20 for Exadata Database
11.2.0.3 Patch 18 on Windows Platforms
11.2.0.2 Patch 25 on Windows Platforms

5> 解决问题：修改隐含参数
alter system set "_cursor_features_enabled"=34 scope=spfile sid='*';
alter system set event='106001 trace name context forever,level 1024' scope=spfile sid='*'; --需重启数据库

客户同意重启数据库后检查

问题解决

巴特西

11g bug event 'cursor: mutex S'-引发的CPU冲高问题

最新文章

热门文章