How much rollback left to apply?
- From: "kyle Hailey" <kylelf@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 17 Jan 2008 16:20:44 -0800
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
Other related posts:
