LIBRARY CACHE PIN event (Lock y Enqueues) | ExpoDBA

LIBRARY CACHE PIN event (Lock y Enqueues)

LIBRARY CACHE PIN event (Lock y Enqueues)

  /*Muestra sesiones en library cache pin y sus holders*/
   SELECT s.sid, serial#,
   decode (kglpnmod,
    0, 'none (0)',
    2, 'Share (2)',
    3, 'Exclusive (3)') "HOLDED",
   decode (kglpnreq,
    0, 'none (0)',
    2, 'Share (2)',
    3, 'Exclusive (3)') "Required",
  kglpnhdl,
  w.event,
  w.seconds_in_wait
    FROM x$kglpn p, v$session s,  gv$session_wait w, x$kglob
   WHERE p.kglpnuse=s.saddr
   and s.sid=w.sid
     AND kglpnhdl in (select w.p1raw
  FROM gv$session s, gv$session_wait w
WHERE s.sid=w.sid
and w.event='library cache pin')
and kglhdadr=kglpnhdl
order by kglpnhdl, kglpnmod desc;