Cursor With Many Versions And Locking The Db Up
Last updated on SEPTEMBER 17, 2013
Applies to:Oracle Database - Enterprise Edition - Version 220.127.116.11 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.
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.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms