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:
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';
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 ;
select sid from v$session s where saddr='&KGLLKUSE'; -- Get KGLLKUSE from the query in Step 2
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.