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.