RE: library cache lock

From: Riyaj Shamsudeen <rshamsud_at_jcpenney.com>
Date: Mon, 04 Apr 2005 22:06:35 -0500
Message-id: <0IEG00JMAE05VU@i006.jcpenney.com>

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_at_gmail.com] Sent: Monday, April 04, 2005 9:15 PM
To: rshamsud_at_jcpenney.com
Cc: oracle-l_at_freelists.org
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_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.

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 - 23:11:24 CDT