Oracle-L: Parallel Rollbacks

From: Steve Perry <sperry_at_sprynet.com>
Date: Thu, 13 Feb 2003 18:08:37 -0800
Message-ID: <F001.0054D1F6.20030213180837@fatcity.com>

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: sperry_at_sprynet.com

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: 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 Thu Feb 13 2003 - 20:08:37 CST