RE: How much rollback left to apply?
- From: "Shamsudeen, Riyaj" <RS2273@xxxxxxx>
- To: <sjaffarhussain@xxxxxxxxx>, <kylelf@xxxxxxxxx>
- Date: Fri, 18 Jan 2008 08:15:51 -0600
As JLewis pointed out, If smon is performing rollback, it doesn't show
up in either v$fast_start_transactions or v$transaction. Only x$ktuxe
shows an entry. Here is the SQL showing rollback by smon.
select * from x$ktuxe where ktuxecfl = 'DEAD';
This behavior is also controlled by fast_Start_parallel_rollback. Since
smon has other duties, setting this to HIGH accelerates rollback by
smon. Of course, parallel* parameters need to be setup since parallel
slaves are used by smon.
Thanks
Riyaj Shamsudeen
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Syed Jaffar Hussain
Sent: Friday, January 18, 2008 6:49 AM
To: kylelf@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: How much rollback left to apply?
I don't know whether this would be helpful or not, when we ran into
similar situation, to know how long SMON finish the recovery, we use the
following to estimate:
select usn, state, undoblockstotal "Total", undoblocksdone "Done",
undoblockstotal-undoblocksdone "ToDo",
decode(cputime,0,'unknown',sysdate+(((undoblockstotal-undoblocksdone) /
(undoblocksdone / cputime)) / 86400)) "Estimated time to complete"
from v$fast_start_transactions;
The above query gives us an idea about the recovery completion time.
Regards
Jaffar
On 1/18/08, kyle Hailey <kylelf@xxxxxxxxx> wrote:
Is there a way to see how much rollback is left to apply?
Currently SMON seems to be busily applying rollback. I can see SMON
reading both the UNDO and then the tablespace that contains a 2 gig
table which had a big delete statement (no commit until the end)
running and then canceled yesterday. The statistic "rollback changes -
undo records applied" is ever increasing for SMON. There are no
entries in v$transaction. The database has been restarted since the
delete statement was canceled.
Doing further deletes on the table produces lots of enqueue TX 4 locks
waiting for SMON. I can create a copy of the table no problem and
then do deletes on the copy with out any lock contention. It seems
that it would be much more efficient just to recreate the table and
drop the original, but I'm curious if there is a way to see how long
the SMON cleanup will take.
The delete was canceled yesterday and SMON is still applying UNDO 24
hours later. I'm curious if I can see how much more work SMON has to
do to complete the application of rollback. It's interesting that I
can create a copy of the table but not do deletes on it.Acutally the
deletes work but after waiting for a TX 4 enqueue on the order of a
second or two. The table has 3 indexes on it which I can see also
involved in the rollback applied by SMON (that's logical).
SMON has been pegged for as long as I have history ( a few hours) at
almost 100% IO wait (little bit of CPU) reading the table, UNDO and
indexes block by block (sequential reads).
--
//www.freelists.org/webpage/oracle-l
--
Best Regards,
Syed Jaffar Hussain
Oracle ACE
8i,9i & 10g OCP DBA
http://jaffardba.blogspot.com/
http://apex.oracle.com/pls/otn/f?p=19297:4:1579866181463918::NO:4:P4_ID:
126
------------------------------------------------------------------------
----------
"Winners don't do different things. They do things differently."
Other related posts:
