RE: library cache lock
- From: Riyaj Shamsudeen <rshamsud@xxxxxxxxxxxx>
- To: dba.orcl@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Mon, 04 Apr 2005 10:58:56 -0500
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Sami Seerangan
Sent: Monday, April 04, 2005 10:44 AM
To: oracle-l@xxxxxxxxxxxxx
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
--
//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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
