Few of the times during deployment users complain of their session almost hanged and that is when is start digging. Recently i was pinged by a user where she was trying to run .sql script which was doing create or replace procedure and she got the error “ORA-04021: timeout occurred while waiting to lock object”.I asked her to run the sql again and her session showed “Library Cache Pin” wait event.
Library cache pins are used to manage library cache concurrency.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.
So, how to diagnose it –
Identify the session
select sid, event, p1raw from v$session_wait where event = 'library cache pin';
Identify the object that is being waited for
SELECT kglnaown "Owner", kglnaobj "Object" FROM x$kglob WHERE kglhdadr='value of p1raw'
Find the session pining the object
SELECT s.sid, s.serial#, s.username, s.osuser, s.machine, s.status, kglpnmod "Mode", kglpnreq "Req" FROM x$kglpn p, v$session s WHERE p.kglpnuse=s.saddr AND kglpnhdl='value of p1raw' OR select sid, serial#, sql_text from dba_kgllock w, v$session s, v$sqlarea a where w.kgllkuse = s.saddr and w.kgllkhdl='value of p1raw' and s.sql_address = a.address and s.sql_hash_value = a.hash_value;
The “Mode” and “Request” can be either exclusive (3) or shared (2). Try an check with the application owners/users if the session holding the pin can be killed. For m, i was was lucky enough to kill the holding session.