Library Cache Pin – Oracle Wait Event

Library cache pins are used to manage library cache concurrency. Pinning an object causes the heaps to be loaded into memory (if not already loaded). PINS can be acquired in NULL, SHARE or EXCLUSIVE modes and can be considered like a special form of lock. A wait for a “library cache pin” implies some other session holds that PIN in an incompatible mode.

To identify the session that’s holding/blocking the PIN on the object in an incompatible mode:

Step 1: 

select  s.sid, s.saddr, sw.p1raw
from v$session_wait sw, v$session s
where sw.sid = s.sid and sw.event='library cache pin';

Step 2: 

select b.KGLLKUSE from dba_kgllock w , dba_kgllock b
where w.KGLLKHDL  = b.KGLLKHDL
and   w.KGLLKREQ  > 0 and b.KGLLKMOD > 0
and   w.KGLLKTYPE = b.KGLLKTYPE
and   w.KGLLKUSE  = '0000000077204A80' -- Get SADDR from query in Step 1
and   w.KGLLKHDL  = '000000006DBC5BE8' -- Get P1RAW from query in Step 1
;

Step 3:

select sid from v$session s
where saddr='&KGLLKUSE'; -- Get KGLLKUSE from the query in Step 2

Step 4:

select to_owner, to_name from v$object_dependency
where to_address = '&P1RAW'; -- Get P1RAW from query in Step 1

Note that an X request (3) will be blocked by any pins held S mode (2) on the object and an S request (2) will be blocked by any X mode (3) pin held, or may queue behind some other X request.

One thought on “Library Cache Pin – Oracle Wait Event

  1. Pingback: Oracle Wait Events | Oracle Database Internal Mechanism

Leave a Reply

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

WordPress.com Logo

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.