Troubleshooting: Understanding and Tuning the Shared Pool (Doc ID 62143.1)
Troubleshooting Library Cache: Lock, Pin and Load Lock (Doc ID 444560.1)
What is “Library cache lock” ?
What is “Library cache pin”?
Troubleshooting: High Version Count Issues (Doc ID 296377.1)
What do the reasons given in v$sql_shared_cursor mean?
How to Find which Session is Holding a Particular Library Cache Lock (Doc ID 122793.1)
Method 1: Systemstate Analysis
$sqlplus '/ as sysdba' oradebug setmypid oradebug unlimit oradebug dump systemstate 266
In trace file, search “waiting for” to find handle address (such as 62d064dc) and then search handle to determine the holder(blocker) and the request session.
Method 2: Examine the x$KGLLK table
— find handle address according to session address
select kgllkhdl Handle, -- handle address kgllkreq Request, kglnaobj Object from x$kgllk where kgllkses = '572ed244' -- session address waiting for 'library cache lock' and kgllkreq > 0;
— find the blocker
select kgllkses saddr, kgllkhdl handle, kgllkmod mod, kglnaobj object from x$kgllk lock_a where kgllkmod > 0 and exists ( select lock_b.kgllkhdl from x$kgllk lock_b where kgllkses = '572ed244' /* blocked session */ and lock_a.kgllkhdl = lock_b.kgllkhdl and kglkreq > 0 );