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 <sperry_at_sprynet.com> 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: 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).
>
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: pegramrg_at_yahoo.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 Fri Feb 14 2003 - 09:48:57 CST
