recompiled stored procedure "library cache pin" waited

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$kglob

   WHERE 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 s

   WHERE p.kglpnuse=s.saddr

     AND kglpnhdl = '[some kglhdadr]'

Example Script:

SELECT s.sid, s.serial#, kglpnmod "Mode", kglpnreq "Req"

FROM x$kglpn p, v$session s

WHERE p.kglpnuse=s.saddr

AND 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:

SELECT

substr(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"

FROM

 sys.X$KGLOB  o,

 sys.X$KGLPN  p,

 sys.X$KSUSE  s

WHERE

 o.inst_id = USERENV('Instance') AND

  p.inst_id = USERENV('Instance') AND

 s.inst_id = USERENV('Instance') AND

 o.kglhdpmd = 2 AND

 o.kglobtyp IN (7, 8, 9, 12, 13) AND

 p.kglpnhdl = o.kglhdadr AND

   s.addr = p.kglpnses

ORDER BY 1, 2, 3 ;