Good post about library cache pin waits

by Granville Bonyata on June 15, 2016

I was going to write up a post about researching library cache pin locks, but someone else has done a very thorough job already: https://orainternals.wordpress.com/2009/06/02/library-cache-lock-and-library-cache-pin-waits/.

Especially useful is this query:


select
distinct
ses.ksusenum sid, ses.ksuseser serial#, ses.ksuudlna username,ses.ksuseunm machine,
ob.kglnaown obj_owner, ob.kglnaobj obj_name
,pn.kglpncnt pin_cnt, pn.kglpnmod pin_mode, pn.kglpnreq pin_req
, w.state, w.event, w.wait_Time, w.seconds_in_Wait
from
x$kglpn pn, x$kglob ob,x$ksuse ses
, v$session_wait w
where pn.kglpnhdl in
(select kglpnhdl from x$kglpn where kglpnreq >0 )
and ob.kglhdadr = pn.kglpnhdl
and pn.kglpnuse = ses.addr
and w.sid = ses.indx
order by seconds_in_wait desc
/

What started my delving into the problem was library cache pin wait events was the database was brought to a halt by them when creating a logon trigger. Interestingly, the above query showed the object being pinned was the database itself. My resolution was to create the logon trigger as an editioned object, using EBR.

Previous post:

Next post: