Re: How much rollback left to apply?
- From: "Syed Jaffar Hussain" <sjaffarhussain@xxxxxxxxx>
- To: kylelf@xxxxxxxxx
- Date: Fri, 18 Jan 2008 15:49:21 +0300
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:
