Este post si bien es cortito creo que es de bastante de utilidad.
Con los siguientes querys vamos a ser capaces de identificar las sesiones que están generando el evento “Library cache lock”
SQL> select saddr from v$session where sid in (select sid from v$session_wait where event like 'library cache lock');
Sesión Bloqueante
SQL> SELECT SID,USERNAME,TERMINAL,PROGRAM FROM V$SESSION
2 WHERE SADDR in
3 (SELECT KGLLKSES FROM X$KGLLK LOCK_A
4 WHERE KGLLKREQ = 0
5 AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B
6 WHERE KGLLKSES = 'el saddr del primer query' /* BLOCKED SESSION */
7 AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
8 AND KGLLKREQ > 0)
9 );
Sesión Bloqueada
SQL> SELECT SID,USERNAME,TERMINAL,PROGRAM FROM V$SESSION
2 WHERE SADDR in
3 (SELECT KGLLKSES FROM X$KGLLK LOCK_A
4 WHERE KGLLKREQ > 0
5 AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B
6 WHERE KGLLKSES = 'el saddr del primer query' /* BLOCKING SESSION */
7 AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
8 AND KGLLKREQ = 0)
9 );
Espero les haya servido.
Saludos!
Gondalf.
