RE: library cache lock

Sami

        There are many reasons, why library cache locks might be held. Usually, holder of the library cache lock is waiting for another resource or you are hitting a bug in 8.1.7. Before considering this as a bug, find which session is holding the lock requested by the waiting session and what mode. Find why that session is holding the library cache lock.

        Following SQL will give you a map of sessions waiting and holding library cache locks.

col kgllkmod heading "lockmode"
col kgllkreq heading "reqmode"
col kglnaobj heading "objname"
select ses.sid, ses.serial#,lck.* from x$kgllk lck , v$session ses where kgllkhdl in
 (select kgllkhdl from x$kgllk where kgllkreq >0) and lck.KGLLKUSE = ses.saddr
Order by lck.KGLNAOBJ
/         

        Also, before killing the waiting session next time, take a systemstate dump at level 4. If you know how to read the systemstate dump, you can figure out why another session is holding the lock.

Thanks  

Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA (ver 7.0 - 9i)
Adjunct faculty El Centro College*
Dallas Oracle User Group board member www.doug.org  

• Now teaching Oracle 9i performance tuning

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sami Seerangan
Sent: Monday, April 04, 2005 10:44 AM
To: oracle-l_at_freelists.org
Subject: library cache lock

Hi:

One or two sessions waiting for library cache lock for long time(in days). Most of the time the session waiting for library cache lock is trying to parse (based on v$sql.sql_text) a SQL statement. Everyday we identify the session and kill the session in both oracle and unix. There are two databases with 2 node OPS and all 4 nodes has the same config. The library cache lock is always on the Node-1 of the 1st database from application A1.

Java application is connecting to the database using connection pool(OCI client). There are 3 connection pools for 3 different applications.

Version 8.1.7.3
Could someone through light on this?

Thanks
-Sami
--

http://www.freelists.org/webpage/oracle-l

• Attached file included as plaintext by Ecartis --
• Desc: Signature

The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If the reader of this message is not the intended recipient, you are hereby notified that your access is unauthorized, and any review, dissemination, distribution or copying of this message including any attachments is strictly prohibited. If you are not the intended recipient, please contact the sender and delete the material from any computer.

--

http://www.freelists.org/webpage/oracle-l Received on Mon Apr 04 2005 - 12:05:06 CDT