Useful documents regarding shared pool/library cache

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 );

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s