RE: library cache lock
- From: Riyaj Shamsudeen <rshamsud@xxxxxxxxxxxx>
- To: "'Sami Seerangan'" <dba.orcl@xxxxxxxxx>
- Date: Mon, 04 Apr 2005 22:06:35 -0500
-- Attached file included as plaintext by Ecartis --
Sami
Processes are not currently waiting, when you ran this SQL. Please see
the state : waited short time.. Only if the state is WAITING, then the
sessions are actually waiting for that specific event. This is the same
reason why x$kgllk and systemstate did not give you any information. You can
also see the systemstate tells 'last wait for library cache lock'. If the
sessions are waiting, then you will see lines like 'waiting for library
cache lock..' etc.
You need to run these statements, while the sessions are truly WAITING.
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 <http://www.dougu.org/>
* Now teaching Oracle 9i performance tuning
_____
From: Sami Seerangan [mailto:dba.orcl@xxxxxxxxx]
Sent: Monday, April 04, 2005 9:15 PM
To: rshamsud@xxxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: library cache lock
Hi Riyaj
Thanks for your response. I did do systemstat and ran your query too.
Select * from gv$session_wait where event='library cache lock';
1 237 165 library cache lock
handle address 3285451564
C3D4032C
lock address 3188447124
BE0BD794
10*mode+namespace 31
0000001F -1 13812
WAITED SHORT TIME
SID PID SPID SERIAL#
---------- ---------- --------- ----------
237 689 19837 17691
PROCESS 689:
----------------------------------------
SO: de1bbe40, type: 1, owner: 0, pt: 0, flag: INIT/-/-/0x00
(process) Oracle pid=689, calls cur/top: cb473bc0/abc32958, 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
last process to post me: de136c54 1 2
last post sent: 42061 0 13
last post sent-location: ksasnd
last process posted by me: de13c680 1 2
(latch info) wait_event=0 bits=0
Process Group: DEFAULT, pseudo proc: de686740
O/S info: user: oracle, term: UNKNOWN, ospid: 19837
OSD pid info: 19837
----------------------------------------
SO: de70da54, type: 3, owner: de1bbe40, pt: 0, flag: INIT/-/-/0x00
(session) trans: 0, creator: de1bbe40, flag: (41) USR/- BSY/-/-/-/-/-
DID: 0000-0000-00000000, short-term DID: 0000-0000-00000000
txn branch: 0
oct: 3, prv: 0, user: 84/HIBM_TAXONOMY
O/S info: user: atg, term: , ospid: 17998, machine: njprdcsmcts05
program: java@njprdcsmcts05 (TNS V1-V3)
last wait for 'library cache lock' blocking sess=0x0 seq=165
wait_time=-1
handle address=c3d4032c, lock address=be0bd794,
10*mode+namespace=1f
----------------------------------------
SO: bf71ea90, type: 36, owner: de70da54, flag: INIT/-/-/0x00
LIBRARY OBJECT PIN: pin=bf71ea90 handle=c11b3134 mode=S lock=bef2abdc
user=de70da54 session=de70da54 count=1 mask=0041 savepoint=11
flags=[00]
----------------------------------------
SO: bef2abdc, type: 35, owner: de70da54, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=bef2abdc handle=c11b3134 mode=N
call pin=0 session pin=bf71ea90
user=de70da54 session=de70da54 count=1 flags=BRO/PNS/[09] savepoint=10
LIBRARY OBJECT HANDLE: handle=c11b3134
>>>> handle address=c3d4032c,
Using the above 'handle address' I did look up the process that is keeping
a lock on my resource by doing a search on the address within the same
tracefile.
But I couldn't find any.
The sql you gave me
{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 returned no rows.
I have 5 such session waiting for 'library cache lock'. All of them are in
similar situation.
Could someone through some light on this.
On Apr 4, 2005 11:58 AM, Riyaj Shamsudeen <rshamsud@xxxxxxxxxxxx> wrote:
Sami
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.
-- 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:
