Oracle Database -- Resolving "library cache pin" wait event

Problem

Altering a procedure is taking too long. Checking the wait events for the session shows that the session is waiting on the library cache pin wait event.

Solution

The compilation of a procedure will hang on the Library Cache Pin wait event if any users are executing the procedure being altered.

On Oracle Database 10g you can easily identify the blocking session with V$SESSION.BLOCKING_SESSION column and on Oracle Database 11g with even more convenient V$SESSION_BLOCKERS data dictionary views.

Oracle Database 9i lacks the above mentioned functionality and the V$LOCK view will not show the blockers for the library cache pin wait event.


To find the blocker first find the handle address of the locked object and the session address of the waiting session:

SQL> 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';

       SID SADDR            P1RAW
---------- ---------------- ----------------
        53 0000000077204A80 000000006DBC5BE8  

Now using the P1RAW and SADDR values find the the address of the user session that is holding the lock

SQL> 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' -- SADDR
     and   w.KGLLKHDL  = '000000006DBC5BE8' -- P1RAW
;

KGLLKUSE
----------------
0000000077207A28


Now using the KGLLKUSE value find the blocking session

SQL> select sid from v$session s
     where saddr='0000000077207A28';

       SID
----------
        52


To find the locked object use V$OBJECT_DEPENDENCY view with the P1RAW value which was found previously

SQL> select to_owner, to_name from v$object_dependency
     where to_address = '000000006DBC5BE8';

TO_OWNER TO_NAME
-------- -------
SYSTEM   PROC1 




Note that the above guidance is applicable to versions 8.1.6+. On Oracle Database 11g you can even use V$LIBCACHE_LOCKS to get the same info.

Also more information can be found in the following Metalink articles:

• How to Analyze Library Cache Timeout with Associated: ORA-04021 'timeout occurred while waiting to lock object %s%s%s%s%s.' Errors (Doc ID 1486712.1)
• How to Find which Session is Holding a Particular Library Cache Lock (Doc ID 122793.1)
• WAITEVENT: "library cache pin" Reference Note (Doc ID 34579.1)
• How to Find the Blocker of the 'library cache pin' in a RAC environment? (Doc ID 780514.1)