重庆思庄Oracle、Redhat认证学习论坛

标题: Cursor Mutex X Wait Events: After Upgrading To 12.2 (文档 ID 2298504.1) [打印本页]

作者: 郑全 时间: 2018-3-3 17:25
标题: 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

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)