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.