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.

Resolving a library cache lock issue

In a testing database, I found there were three sessions waiting for library cache lock:

SECONDS
SID USERNAME   EVENT                             P1         P2  WAIT_TIME  IN WAIT STATE
------ ---------- ------------------------- ---------- ---------- ---------- -------- --------------
248 SYSTEM     library cache lock        1.6015E+10 1.5964E+10          0    41173 WAITING
97 DBSCRUB    library cache lock        1.6098E+10 1.5961E+10          0    29840 WAITING
85 DBSCRUB    library cache lock        1.5989E+10 1.5970E+10          0    27361 WAITING

I followed Metalink note: 122793.1 to find the sessions holding library cache locks by using systemstate analysis.

(1) Open a new session and launch the following statement:

ALETER SESSION SET EVENTS 'IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL 10';

Oracle will create a systemstate tracefile in the USER_DUMP_DEST directory.

(2) Obtain Oracle process PID of the hanging session by the following statement:

SELECT PID FROM V$PROCESS WHERE ADDR=
(SELECT PADDR FROM V$SESSION WHERE SID=&sid_of_hanging_session);

In my case, sid=248, I obtained pid=16

(3) Search PROCESS 16 in the trace file, in the "wait for" line we can obtain the 'handle address':
e.g.

PROCESS 16:
----------------------------------------
SO: 3b41ad6f8, type: 2, owner: 0, flag: INIT/-/-/0x00
(process) Oracle pid=16, calls cur/top: 3b46e43c8/3b46e43c8, flag: (0) -
int error: 0, call error: 0, sess error: 0, txn error 0
(post info) last post received: 0 0 0
last post received-location: No post
( .. cut ...)

waiting for 'library cache lock' blocking sess=0x0 seq=13718 wait_time=0
handle address=3ba93b8b0, lock address=3b78e1880, 100*mode+namespace=c9
temporary object counter: 0

(4) Search the handle address (3ba93b8b0) to find the Oracle process pid, then based on the pid, find the session sid that is holding the library cache lock:
select sid from v$session where paddr =
( select addr from v$process where pid=&pid);

(5) After killing the session holding the library cache lock, the wait event is gone.


This testing database is refreshed every Saturday morning by imp. During refreshing it should be shutdown and re-startup first. However, I found the logon time of the sessions holding the library lock is earlier than last Saturday. So I checked the alert log, found the following line:

...
Active call for process 28031 user 'oracle' program 'oracle@bxxxqsd2 (TNS V1-V3)'
Active call for process 17980 user 'oracle' program 'oracle@bxxxqsd2 (TNS V1-V3)'
SHUTDOWN: waiting for active calls to complete.
Sat May 16 07:56:31 2009
SHUTDOWN: Active sessions prevent database close operation
Starting ORACLE instance (normal)
Sat May 16 07:58:02 2009

It showed that the db had not been shutdown at all. Failed to close the database due to active calls. Next Saturdy I will shutdown it manually.


The Metalink note also describes some situations that can cause the library cache lock:

* A DML operation that is hanging because the table which is accessed is currently
undergoing changes (ALTER TABLE). This may take quite a long time depending on
the size of the table and the type of the modification
(e.g. ALTER TABLE x MODIFY (col1 CHAR(200) on thousands of records).

* The compilation of package will hang on Library Cache Lock and Library Cache Pin
if some users are executing any Procedure/Function defined in the same package.

Another method to find the session holding library cache lock is described here.