1. 首页
  2. IT资讯

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

今天一个朋友的数据库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

记录一下

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7728585/viewspace-1587103/,如需转载,请注明出处,否则将追究法律责任。

主题测试文章,只做测试使用。发布者:深沉的少年,转转请注明出处:http://www.cxybcw.com/183691.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code