RE: How much rollback left to apply?

From: Shamsudeen, Riyaj <>
Date: Fri, 18 Jan 2008 08:15:51 -0600
Message-ID: <6A4102F59ECFA248B81F7D08F031797801F55E8B@TBDCEXCH01.US.Cingular.Net>

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.  


Riyaj Shamsudeen  

[] On Behalf Of Syed Jaffar Hussain Sent: Friday, January 18, 2008 6:49 AM
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.  



On 1/18/08, kyle Hailey <> 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).


Best Regards,
Syed Jaffar Hussain
Oracle ACE
8i,9i & 10g OCP DBA 126

"Winners don't do different things. They do things differently."

-- Received on Fri Jan 18 2008 - 08:15:51 CST

Original text of this message