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.
> 
>

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

Other related posts: