Look at the parameter fast_start_parallel_rollback Also check out metalink Note:144332.1 Parallel query servers get 100% cpu
Rob Pegram Oracle Certified DBA - 8i, 9i --- Steve Perry <[EMAIL PROTECTED]> wrote: > I hope somebody can provide me with more information > on parallel rollbacks. > I've checked metalink and google, but haven't found > much information. I read > that starting in 8i, SMON can be used to clean up a > process after it's been > killed. > > Questions first, story last. > > What criteria is used to decide if PMON or SMON is > used? > Is SMON used only for parallel rollbacks, otherwise > PMON is used? > Why was there so many archive logs generated to > rollback the transaction? I > wasn't expecting that. > Is the last query adequate to monitor the progress > or is there a better way? > Are there any init.ora parameters that I can set to > change the behaviour or > improve performance? > > Thanks, > Steve > > --------------------------- > SAP on Oracle 8.1.7.4.1 on Win NT 4.0 > > The other evening, the SAP Basis admin killed a long > running process that > hadn't generated much redo (600 meg), but after he > killed it, it generated > 2.5 gig of redo in about an about an hour. > > I got called about 20 minutes after he did it > because the system started > running slow. > I logged in and ran this query to see if there were > any open transactions, > but nothing came back. > > SELECT vs.username , vs.osuser > , r.name rollback_segment , vt.used_ublk > , vt.used_urec , vt.start_time > , vs.sid , vs.serial# > , p.spid process > FROM v$transaction vt > , v$rollname r > , v$session vs > , v$process p > WHERE vt.addr = vs.taddr and vt.xidusn = r.usn > and p.addr = > vs.paddr > order by r.name > / > > I kept seeing the logs switches, so I started > looking in v$session_wait and > saw the following events. > > EVENT > ---------------------------------------- > PX Deq: Txn Recovery Start > PX Deq: Txn Recovery Start > PX Deq: Txn Recovery Start > Wait for stopper event to be increased > > Metalink pointed me to: > V$fast_start_servers > v$fast_start_transactions > > From there, I ran this (not sure if the joins are > correct) that allowed me > to watch the process slowly comple. > > SELECT T.USN > -- , R.NAME > , USERNAME > -- , SERIAL# > , TERMINAL > , PROGRAM > , T.STATE > , ROUND (UNDOBLOCKSDONE / UNDOBLOCKSTOTAL * > 100, 1 ) PCT_DONE > , T.UNDOBLOCKSDONE > , T.UNDOBLOCKSTOTAL > , T.SLT > , T.SEQ > , T.PID > , T.CPUTIME > -- , T.PARENTUSN > -- , T.PARENTSLT > -- , T.PARENTSEQ > from v$fast_start_transactions T > , v$TRANSACTION R > , v$process p > WHERE T.PARENTUSN = R.XIDUSN > and T.PARENTSLT = R.XIDSLOT > and T.PARENTSEQ = R.XIDSQN > and t.pid = p.pid > / > > --output from query > > USN Oracle User TERMINAL > PROGRAM > STATE PCT_DONE UNDOBLOCKSDONE > UNDOBLOCKSTOTAL SLT > SEQ PID CPUTIME PARENTUSN PARENTSLT > PARENTSEQ > ---------- -------------------- ---------------- > ------------------------- - > --------------- ---------- -------------- > --------------- ---------- ------- > --- ---------- ---------- ---------- ---------- > ---------- > 14 SYSTEM SATSAP26 > ORACLE.EXE (P000) > RECOVERING 98.8 633522 > 641130 89 > 207166 17 1 0 0 > 0 > > > > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Steve Perry > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 > http://www.fatcity.com > San Diego, California -- Mailing list and web > hosting services > --------------------------------------------------------------------- > 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). > __________________________________________________ Do you Yahoo!? Yahoo! Shopping - Send Flowers for Valentine's Day http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Robert Pegram INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- 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).
