Re: diagnosing latch free

A sort extent pool latch? That's weird. You can get the sid of the guy holding the latch by joining v$latch.addr (or v$latchchildren if it's a latch with children - sort extent is not) against v$latchholder.laddr.

something like

select sid from v$latch l, v$latchholder lh where l.addr = lh.laddr and l.name = 'sort extent pool';

On Saturday, December 8, 2001, at 06:20 PM, Doug C wrote:

> Ok.. it's a sort segment latch.. any way to find out why? It's been
> sitting
> around for over an hour ...
>
> On Sat, 08 Dec 2001 14:35:18 -0800, you wrote:
>
>> oops, probably only want the events that are latch frees:
>>
>> select ln.name from v$session_wait sw, v$latchname ln where sw.p2 =
>> ln.latch# and sw.event = 'latch free';
>>
>> On Saturday, December 8, 2001, at 04:50 PM, George Schlossnagle wrote:
>>
>>> Try:
>>>
>>> select ln.name from v$session_wait sw, v$latchname ln where sw.p2 =
>>> ln.latch#.
>>>
>>> Best,
>>>
>>> George
>>>
>>> www.pythian.com -- schlossnagle_at_pythian.com -- 877-PYTHIAN
>>> Smarter than adding another team member, Pythian has new services for
>>> supplementing DBAs: get our help with monitoring, 24x7 on-call, daily
>>> verifications, storage management, performance and more.
>>>
>>>
>>> On Saturday, December 8, 2001, at 04:05 PM, Doug C wrote:
>>>
>>>> I have a session that seems to be hung on a sql_statment.
>>>>
>>>> Here is it's session_wait entry:
>>>>
>>>> SID SEQ#
>>>> ---------- ----------
>>>> EVENT
>>>> ----------------------------------------------------------------
>>>> P1TEXT
>>>> P1
>>>> ----------------------------------------------------------------
>>>> ----------
>>>> P1RAW P2TEXT
>>>> --------
>>>> ----------------------------------------------------------------
>>>> P2 P2RAW
>>>> ---------- --------
>>>> P3TEXT
>>>> P3
>>>> ----------------------------------------------------------------
>>>> ----------
>>>> P3RAW WAIT_TIME SECONDS_IN_WAIT STATE
>>>> -------- ---------- --------------- -------------------
>>>> 62 1239
>>>> latch free
>>>> address
>>>> 805352248
>>>> 3000B338 number
>>>> 88 00000058
>>>> tries
>>>> 923
>>>> 0000039B 0 0 WAITING
>>>>
>>>>
>>>> The seq# goes up from time to time.
>>>> My question is how to determine what kind of latch is bothering it?
>>>> Does P2 (88) indicate what type of latch? Can I join with some other
>>>> table to
>>>> find out what 88 is?
>>>>
>>>> Thanks,
>>>> D
>>>> --
>>>> Please see the official ORACLE-L FAQ: http://www.orafaq.com
>>>> --
>>>> Author: Doug C
>>>> INET: dcowles_at_i84.net
>>>>
>>>> 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).
>>>
>>> -- 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).
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Doug C
> INET: dcowles_at_i84.net
>
> 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).

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