ursor_sharing=SIMILAR 引发大量的 cursor: mutex S-阿里云开发者社区

开发者社区> 数据库> 正文

ursor_sharing=SIMILAR 引发大量的 cursor: mutex S

简介: 今天一个朋友的数据库11.2.0.1 遇到这个问题,导致业务大面积的瘫痪查看等待如下:   IN_NUM EVENT_NAME                                                       NOW_SNAP_ID...
今天一个朋友的数据库11.2.0.1 遇到这个问题,导致业务大面积的瘫痪查看等待如下:
  IN_NUM EVENT_NAME                                                       NOW_SNAP_ID NOW_WAITED_TOTAL WAIT_TIME_MICRO    ROW_NUM
---------- ---------------------------------------------------------------- ----------- ---------------- --------------- ----------
         1 cursor: mutex S                                                         8196     574159937294    113469206148          1
         1 DB CPU                                                                  8196     264925348737     94172222209          2
         1 library cache lock                                                      8196     377415652600     58003457267          3
         1 direct path read                                                        8196      16531321401      4584378285          4
         1 db file sequential read                                                 8196       2798779765      1387124732          5 

默认一小时的AWR event
可以看到基本处于崩溃的边缘,其原因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

记录一下

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章