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_at_gmail.com> 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).
> --
> http://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."

--
http://www.freelists.org/webpage/oracle-l