Re: diagnosing latch free

Hey Anita!
Thanks for the useful info.
Actually, after the stats were hanging I started querying various tables and those queries also started hanging; queries on v$latch and a couple of others. I finally shutdown abort and got an ORA-600 error. I looked it up on the extremely handy ORA-600 tool and found that the first argument - 1113 indicated the following...

VERSIONS:
  versions 7.3.X to 8.1.7  

DESCRIPTION:   We are freeing a state object but it already appears to be on the free list.

  This is generally an in memory (SGA) corruption or due to a bug   in mishandling the state objects.    

FUNCTIONALITY:
  STATE OBJECT MANAGER   IMPACT:
  PROCESS FAILURE
  POSSIBLE INSTANCE FAILURE IF DETECTED BY PMON PROCESS   NON CORRUPTIVE - No underlying data corruption.  

  Bug 1307247: ORA-600 [1113] WHEN AN ANALYZE OPERATION FAILS OR IS CANCELLED   fixed in 8.0.6.3, 8.1.7.1 and 9.0.0 releases.

So I think it had something to do with bailing out of an analyze. I'm not sure how things got gummed up in the first place but the db is fine now.

• Doug

On Sun, 09 Dec 2001 22:20:18 -0800, you wrote:

>Hi Doug!
>
>It sounds like SMON is busy doing something else, most
>likely coalescing free space or deallocating temp
>segments. See metalink Note: 61997.1 "SMON -
>Temporary Segment Cleanup and Free Space Coalescing"
>
>While there are events that can be set to prevent smon
>from coalescing or cleaning up temp segments, they are
>only a temporary measure to allow one to defer the
>cleanup to a more convenient time. The best bet is to
>let smon finish its job and then set proper extent
>sizes for temp tablespaces or use locally managed temp
>tablespaces.
>
>Did the db crash or was a shutdown abort done? SMON
>could be doing instance recovery. I've seen cases
>where SMON was "stalled" when doing recovery when
>FAST_START_PARALLEL_ROLLBACK was set. Shutting down
>and setting FAST_START_PARALLEL_ROLLBACK = FALSE
>allowed SMON to finish recovery.
>
>As a workaround in any of the above situations, you
>can create a permanent tablespace and redirect users'
>temporary tablespace to that permanent tablespace.
>
>HTH,
>
>-- Anita
>
>--- Doug C <dcowles_at_i84.net> 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
>
>
>
>__________________________________________________
>Do You Yahoo!?
>Send your FREE holiday greetings online!
>http://greetings.yahoo.com
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com

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