Find the session holding a library cache lock by examining the x$kgllk table

Metalink Note: 122793.1 "HOW TO FIND THE SESSION HOLDING A LIBRARY CACHE LOCK" describes two methods to find the blocking session:

1. SYSTEMSTAT analysis
2. Examine THE X$KGLLK TABLE

In a previous post, I practised method 1. In this post I demonstrate how to use method 2.

First, I created the following two scrpipts based on the note:

rem librarycache_blkng_sess.sql
rem   --  find the blocking session holding library cache lock
rem  input the SID of the session waiting for libary cache lock
rem

col saddr new_val SADDR_OF_BLOCKED_SESSION
select saddr  from v$session where sid= &sid;

col sid format 9999
col username format a15
col terminal format a10
col program format a15

SELECT SID,USERNAME,TERMINAL,PROGRAM FROM V$SESSION
WHERE SADDR in
(SELECT KGLLKSES FROM X$KGLLK LOCK_A
WHERE KGLLKREQ = 0
AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B
WHERE KGLLKSES = '&SADDR_OF_BLOCKED_SESSION'
AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
AND KGLLKREQ > 0)
);

rem librarycache_blked_sess.sql
rem
rem    find the blocked sessions waiting for library cache lock
rem    input the SID of the bocking session
rem    run as sysdba
rem ref: ML122793.1

col saddr new_val SADDR_OF_BLKING_SESS
select saddr  from v$session where sid= &sid;

set linesize 120
col sid format 9999
col username format a15
col terminal format a10
col program format a15

SELECT SID,USERNAME,TERMINAL,PROGRAM FROM V$SESSION
WHERE SADDR in
(SELECT KGLLKSES FROM X$KGLLK LOCK_A
WHERE KGLLKREQ > 0
AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B
WHERE KGLLKSES = '&SADDR_OF_BLKING_SESS'
AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
AND KGLLKREQ = 0)
);

Here is the screen output when using the above scripts in a testing database:

(1) Find the session currently waiting for library cache lock

SQL> @sess_by_event
Enter value for event: library

SECONDS      
SID Username   EVENT                       P1         P2  WAIT_TIME  IN WAIT STATE
---- ---------- ------------------- ---------- ---------- ---------- -------- ---------
160 DBSCRUB    library cache lock  1.6029E+10 1.5969E+10          0     2186 WAITING
260 DBSCRUB    library cache lock  1.6029E+10 1.5969E+10          0     2186 WAITING

(2) Find who is the blocker

SQL> @librarycache_blkng_sess.sql
Enter value for sid: 160
old   1: select saddr  from v$session where sid= &sid
new   1: select saddr  from v$session where sid= 160

SADDR
----------------
00000003B436BE98

old   6:                  WHERE KGLLKSES = '&SADDR_OF_BLOCKED_SESSION'
new   6:                  WHERE KGLLKSES = '00000003B436BE98'

SID USERNAME        TERMINAL   PROGRAM
----- --------------- ---------- ---------------
56 DBSCRUB

SQL> @librarycache_blkng_sess.sql
Enter value for sid: 260
old   1: select saddr  from v$session where sid= &sid
new   1: select saddr  from v$session where sid= 260

SADDR
----------------
00000003B43ADED8

old   6:                  WHERE KGLLKSES = '&SADDR_OF_BLOCKED_SESSION'
new   6:                  WHERE KGLLKSES = '00000003B43ADED8'

SID USERNAME        TERMINAL   PROGRAM
----- --------------- ---------- ---------------
56 DBSCRUB

It can be seen that the blocking session is sid=56

(3) Who is blocked by sid=56

SQL> @librarycache_blked_sess.sql
Enter value for sid: 56
old   1: select saddr  from v$session where sid= &sid
new   1: select saddr  from v$session where sid= 56

SADDR
----------------
00000003B4327418

old   6:                  WHERE KGLLKSES = '&SADDR_OF_BLKING_SESS'
new   6:                  WHERE KGLLKSES = '00000003B4327418'

SID USERNAME        TERMINAL   PROGRAM
----- --------------- ---------- ---------------
160 DBSCRUB
260 DBSCRUB

The above demonstrated that the scripts worked well as expected.