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 <[EMAIL PROTECTED]> 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 -- [EMAIL PROTECTED] -- >> 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
