Re: diagnosing latch free

From: A. Bardeen <abardeen1_at_yahoo.com>
Date: Sun, 09 Dec 2001 22:37:35 -0800
Message-ID: <F001.003D891A.20011209222017@fatcity.com>

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
-- 
Author: A. Bardeen
  INET: abardeen1_at_yahoo.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 Mon Dec 10 2001 - 00:37:35 CST