Cursor Mutex X Wait Events: After Upgrading To 12.2 (文档 ID 2298504.1)

郑全

12.2 的一个坑,大家注意:

In this Document

Symptoms

Changes

Cause

Solution

References

---

Applies to:   Oracle Database - Enterprise Edition - Version 12.2.0.1 and later
Information in this document applies to any platform.

SymptomsHigh Cursor Mutex X wait events after an upgrade to database 12.2.
The AWR report shows the following:

Top 10 Foreground Events by Total Wait Time

Event              Waits     Total Wait Time (sec)    Avg Wait    % DB time    Wait Class
cursor: mutex X    61,007    3423.4                    56.11ms      57.1       Concurrency
SQL ordered by Version Count

    Only Statements with Version Count greater than 20 are displayed

Version Count     Executions     SQL Id    SQL Module    PDB Name    SQL Text
7,467    38       f0h5rpzmhju11           IPIDEV                     select SYS_CONTEXT('USERENV', ...
3,698             fn0snbuqyccq5           IPIDEV                     SELECT NAME, PATH, READ, WRITE...
3,302             3zmzdajzvjkfw           CDB$ROOT                   select /*+ no_monitor no_state...
3,006             43nuq8ay2jnt7           CDB$ROOT      
2,241             5j7xryzqzqcbk           CDB$ROOT                   SELECT /*jskqjobqlod3*//*+ no_...
1,856             frywjuac2qbbw           CDB$ROOT      
1,533             85waydr30k7fk           CDB$ROOT                   select c.name pdb_name, cp.pr...
1,320             5c24r2fm4b9rh           CDB$ROOT                   SELECT /*jskqjobqlod2*/ /*+ no...
1,032    4        9w50hgp8akpz5           CDB$ROOT                   SELECT /*+ OPT_PARAM('_paralle...


ChangesUpgraded the database to 12.2.0.1.0
CauseThe default value for _cursor_obsolete_threshold is 1024 pre 12.2, but has increased to 8192 in 12.2.

SolutionConsider setting the parameter  _cursor_obsolete_threshold to 1024, the default value for previous versions.
Alter system set "_cursor_obsolete_threshold"=1024

References
NOTE:438755.1 - High SQL Version Counts - Script to determine reason(s)
NOTE:1595092.1 - SRDC - How to Collect Standard Information for a Database Performance Problem
NOTE:1929376.1 - My Oracle Support - Automated Troubleshooting
NOTE:1904807.1 - SRDC - How to Collect Standard Information for an Issue Where 'library cache lock' Waits Are the Primary Waiters on the Database