FAQ
Hi
All
I
have a session which is waiting for Library Cache latch for 6 hours. How can I
find who is holding this latch.
This session is
executing a pl/sql script ( Not package ). Latch#  60 is library cache
latch.


Here is from
v$session_wait

1
select * from v$session_wait  2* where sid=1005PROD>/Press
Enter to Continue ....



SID        SEQ#
EVENT
P1TEXT
------- ----------- ------------------------------
-------------------------------------------------P1RAW
P2TEXT
P2 P2RAW----------------
---------------------------------------------------------------- -----------
------

Wait  Sec

inP3TEXT

P3
P3RAW

time   Wait

STATE----------------------------------------------------------------
----------- ---------------- ------
1005        5987 latch
free
address

@992507000001C0A5E228

number

60

000000000000003Ctries

00

-1  32,962 WAITED SHORT





Thanks



size=2>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Brijesh
Gupta
Oracle Production DBA

Search Discussions

  • Rahul at Feb 18, 2002 at 7:48 am
    you are getting the SID from the v$session_wait view.. just query the
    v$session and find our the session details
    of this SID !
    ----------
    From: Gupta, Brijesh[SMTP:Brijesh.Gupta_at_Airliquide.com]
    Reply To: ORACLE-L_at_fatcity.com
    Sent: Friday, February 15, 2002 10:08 PM
    To: Multiple recipients of list ORACLE-L
    Subject: Library Cache wait -- Who is holding this latch

    Hi All
    I have a session which is waiting for Library Cache latch for 6 hours.
    How can I find who is holding this latch.
    This session is executing a pl/sql script ( Not package ). Latch# 60 is
    library cache latch.


    Here is from v$session_wait

    1 select * from v$session_wait
    2* where sid=1005
    PROD>/
    Press Enter to Continue ....

    SID SEQ# EVENT P1TEXT
    ------- ----------- ------------------------------
    -------------------------------------------------
    P1RAW P2TEXT
    P2 P2RAW
    ----------------
    ----------------------------------------------------------------
    ----------- ------

    Wait Sec in
    P3TEXT
    P3 P3RAW time Wait STATE
    ----------------------------------------------------------------
    ----------- ---------------- ------
    1005 5987 latch free address
    @9925
    07000001C0A5E228 number
    60 000000000000003C
    tries
    0 00 -1 32,962 WAITED SHORT



    Thanks


    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Brijesh Gupta
    Oracle Production DBA
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Rahul
    INET: rahul_at_ratelindo.co.id

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Anjo Kolk at Feb 20, 2002 at 8:28 am
    I think that nobody is holding the latch anymore. WAITSTATE = WAITED SHORT.

    Anjo.

    George Schlossnagle wrote:
    You shouldn't need to do that. To find out who the holders are of all latches currently being aited on by ithers you could do
    SELECT lh.sid, ln.name, sq.sql_text
    FROM v$latchholder lh, v$sqlarea sq, v$session se, v$session_wait sw, v$latchname ln
    WHERE sw.event = 'latch free'
    AND sw.p1raw = lh.laddr
    AND ln.latch# = sw.p2
    AND se.sid = lh.sid
    AND se.sql_address = sq.address
    AND se.sql_hash_value = sq.hash_value

    // George Schlossnagle
    // Principal Consultant
    // OmniTI, Inc http://www.omniti.com
    // (c) 301.343.6422 (e) george_at_omniti.com
    // 1024D/1100A5A0 1370 F70A 9365 96C9 2F5E 56C2 B2B9 262F 1100 A5A0

    On Tuesday, February 19, 2002, at 11:28 AM, Diego Cutrone wrote:

    you'll have to take a library cache dump and look for the latch number displayed in the P1 Field of your query.

    HTH
    Greetings
    Diego Cutrone

    ----- Original Message -----
    From: Gupta, Brijesh
    To: Multiple recipients of list ORACLE-L
    Sent: Friday, February 15, 2002 12:08 PM
    Subject: Library Cache wait -- Who is holding this latch

    Hi All
    I have a session which is waiting for Library Cache latch for 6 hours. How can I find who is holding this latch.
    This session is executing a pl/sql script ( Not package ). Latch# 60 is library cache latch.


    Here is from v$session_wait

    1 select * from v$session_wait
    2* where sid=1005
    PROD>/
    Press Enter to Continue ....

    SID SEQ# EVENT P1TEXT
    ------- ----------- ------------------------------ -------------------------------------------------
    P1RAW P2TEXT P2 P2RAW
    ---------------- ---------------------------------------------------------------- ----------- ------
    Wait Sec in
    P3TEXT P3 P3RAW time Wait STATE
    ---------------------------------------------------------------- ----------- ---------------- ------
    1005 5987 latch free address @9925
    07000001C0A5E228 number 60 000000000000003C
    tries 0 00 -1 32,962 WAITED SHORT




    Thanks


    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Brijesh Gupta
    Oracle Production DBA
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Anjo Kolk
    INET: anjo_at_oraperf.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedFeb 15, '02 at 4:13p
activeFeb 20, '02 at 8:28a
posts3
users3
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase