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).
