Re: Library Cache wait -- Who is holding this latch

From: George Schlossnagle <george_at_omniti.com>
Date: Tue, 19 Feb 2002 11:29:15 -0800
Message-ID: <F001.0041357F.20020219112915@fatcity.com>

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: George Schlossnagle
  INET: george_at_omniti.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).

Received on Tue Feb 19 2002 - 13:29:15 CST