Re: library cache lock

From: Sami Seerangan <dba.orcl_at_gmail.com>
Date: Mon, 4 Apr 2005 22:14:35 -0400
Message-ID: <f09dd6280504041914257a5a23@mail.gmail.com>

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_at_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_at_jcpenney.com> 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.
>
>

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

Received on Mon Apr 04 2005 - 22:18:35 CDT