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 <[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 >> >>> >> > >> >> >>> -- >> >>> P1RAWP2TEXT >> >>> >> >>> >> > >> >>> P2 P2RAW >> >>> -- >> >>> P3TEXT >> >> >>> P3 >> >>> >> > >> >> >>> -- >> >>> P3RAW WAIT_TIME SECONDS_IN_WAIT STATE >> >>> -- --- >> --- >> >>> 62 1239 >> >>> latch free >> >>> address >> >> >>> 805352248 >> >>> 3000B338 number >> >>> 88 0058 >> >>> tries >> >> >>> 923 >> >>> 039B 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
Re: diagnosing latch free
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 > >>> > > > >>> -- > >>> P1RAWP2TEXT > >>> > >>> > > >>> P2 P2RAW > >>> -- > >>> P3TEXT > > >>> P3 > >>> > > > >>> -- > >>> P3RAW WAIT_TIME SECONDS_IN_WAIT STATE > >>> -- --- > --- > >>> 62 1239 > >>> latch free > >>> address > > >>> 805352248 > >>> 3000B338 number > >>> 88 0058 > >>> tries > > >>> 923 > >>> 039B 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: [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).
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 -- [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 -- P1RAWP2TEXT P2 P2RAW -- P3TEXT P3 -- P3RAW WAIT_TIME SECONDS_IN_WAIT STATE -- --- --- 62 1239 latch free address 805352248 3000B338 number 88 0058 tries 923 039B 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: [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). >>> >>> -- Please see the official ORACLE-L FAQ: http://www.orafaq.com >>> -- Author: George Schlossnagle >>> 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). > > -- > 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: George Schlossnagle 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
Re: diagnosing latch free
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 >>> >>> -- >>> P1RAWP2TEXT >>> >>> >>> P2 P2RAW >>> -- >>> P3TEXT >>> P3 >>> >>> -- >>> P3RAW WAIT_TIME SECONDS_IN_WAIT STATE >>> -- --- --- >>> 62 1239 >>> latch free >>> address >>> 805352248 >>> 3000B338 number >>> 88 0058 >>> tries >>> 923 >>> 039B 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: [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). >> >> -- Please see the official ORACLE-L FAQ: http://www.orafaq.com >> -- Author: George Schlossnagle >> 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). -- 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).
Re: diagnosing latch free
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 -- P1RAWP2TEXT P2 P2RAW -- P3TEXT P3 -- P3RAW WAIT_TIME SECONDS_IN_WAIT STATE -- --- --- 62 1239 latch free address 805352248 3000B338 number 88 0058 tries 923 039B 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: [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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: George Schlossnagle 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).
Re: diagnosing latch free
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 > > -- > P1RAWP2TEXT > > > P2 P2RAW > -- > P3TEXT > P3 > > -- > P3RAW WAIT_TIME SECONDS_IN_WAIT STATE > -- --- --- > 62 1239 > latch free > address > 805352248 > 3000B338 number > 88 0058 > tries > 923 > 039B 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: [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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: George Schlossnagle 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).
