Cursor With Many Versions And Locking The Db Up
(Doc ID 1573799.1)
Last updated on SEPTEMBER 29, 2021
Applies to:
Oracle Database - Enterprise Edition - Version 11.2.0.3 and laterOracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
Occurs on Exadata x2-8 and x3-8 machine, but this issue also can happen on other oracle database running on Linux with huge number of CPU.
Symptoms
Random SQLs statements can cause other statements to lock up with “library cache lock”. Blocking session appears to be LMON or LCK0 most of the time.
When executing the following SQL text:
SQL> SELECT INSTANCE_NAME, HOST_NAME, STATUS, DATABASE_STATUS FROM gv$instance;
There will be a huge number of versions, most will be obsolete, and for most of them GV$SQL_SHARED_CURSOR claims the reason was PX_MISMATCH or optimizer mismatch mentioning different requested and default DOP.
The following warning is noted in the alert logs (while the instances are up) on the related Nodes:
Detected change in CPU count to 160
The Physical CPUs available are dropping out and being added back from time to time in Database, but the actual CPU does not change.
Oracle detects changes in CPU count and adjusts internal defaults to accommodate for the change, this obviously affects default DOP and might cause massive cursor invalidations due to PX or optimizer mismatches.
Changes
None
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Changes |
| Cause |
| Solution |
| References |