I recompiled some stored procedure on oracle database, that waited a long time.
when I traced this session, i found 'library cache pin' waited.
WAIT #1: nam='library cache pin' ela= 2930919 handle address=2947728616 pin address=1668978256 100*mode+namespace=302 obj#=-1 tim=1201391759045052
What is 'library cache pin'?
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.
Why the 'library cache pin' waited?
that stored procedure is called from another stored procedure on some sessions.
This case i check from x$kglob and x$kglpn tables.
SELECT kglnaown "Owner", kglnaobj "Object", kglhdadr "Addr"FROM x$kglobWHERE lower(kglnaobj) like '%[package_name]%';
This query i get kglnaown (Owner), kglnaobj (Object) and kglhdadr; i can use "kglhdadr" field to find some informations from x$session view, when join with x$kglpn table.
SELECT s.sid, kglpnmod "Mode", kglpnreq "Req"FROM x$kglpn p, v$session sWHERE p.kglpnuse=s.saddrAND kglpnhdl = '[some kglhdadr]'
SELECT s.sid, s.serial#, kglpnmod "Mode", kglpnreq "Req"FROM x$kglpn p, v$session sWHERE p.kglpnuse=s.saddrAND kglpnhdl in (SELECT kglhdadr FROM x$kglob where lower(kglnaobj) like '%&stored_procedure_name%');
i got sid and another informations... i can trace and solve the problem on that session.
after that i can recompiled stored procedure.
Anyway below script from Steve Adams (www.ixora.com.au) help to to list all sessions that are currently executing stored code:
SELECTsubstr(DECODE(o.kglobtyp,7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE', 12, 'TRIGGER', 13,'CLASS'),1,15) "TYPE",substr(o.kglnaown,1,30) "OWNER",substr(o.kglnaobj,1,30) "NAME",s.indx "SID",s.ksuseser "SERIAL"FROMsys.X$KGLOB o,sys.X$KGLPN p,sys.X$KSUSE sWHEREo.inst_id = USERENV('Instance') ANDp.inst_id = USERENV('Instance') ANDs.inst_id = USERENV('Instance') ANDo.kglhdpmd = 2 ANDo.kglobtyp IN (7, 8, 9, 12, 13) ANDp.kglpnhdl = o.kglhdadr ANDs.addr = p.kglpnsesORDER BY 1, 2, 3 ;