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

  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.

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

 - RECOMMENDATION FOR CURSOR_SHARING PARAMETER WITH ORACLE 11G REPOSITORY

記錄一下