Friday, December 26, 2008

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 ( help to to list all sessions that are currently executing stored code:
 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"
 sys.X$KGLOB  o,
 sys.X$KGLPN  p,
 sys.X$KSUSE  s
 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 ;


Coskan Gundogar said...

this trick made my day today.

Thanks for sharing mate.

Was it you who wrote the sql or you got it from somewhere else ?

Surachart said...

Good to hear like that.

When you/applications/... call stored procedures., they pin in library cache and after worked... we
won't see in library cache.

However on OLTP, I will check in library before deploy some store procedres.

>Was it you who wrote the sql or you got it from somewhere else ?

In script...
select ...
s.indx "SID",
s.ksuseser "SERIAL"

we can use SID and SERIAL to find who doing ... use v$session (SID/SERIAL#)

Wish My Contents helpful -)