Re: How much rollback left to apply?

Making it up as I go along - I don't happen to have a huge table with slow enough response to test this on.

smon does the rollback after recovery, but any process that hits a block in need of rollback can do it for itself, marking the undo as 'applied'.

Because of the possible concurrency issues, I'd guess that any processes applying the rollback have to take an exclusive lock on the transaction table slot while they work (otherwise smon might hit the block they are fixing, just as they are fixing it, and you get the option for an inconsistency/block corrupt issue).

Most of the rollback is done by smon - so most of the time other processes will have to wait on smon. But smon has other duties, so probably keeps stopping and restarting - say every three seconds - releasing and re-acquiring the TX lock as it does so. This could nearly explain your couple of seconds TX-4 wait.

Is the wait always for the same id1/id2 (in which case it would be the slot for the original transaction and my guess might be right), or does it keep changing ?

To see how much rollback is left - if the TX slot is always the same you could dump the undo block (or check x$ktuxe.ktuxesiz) because maybe smon is updating the block count in the slot as it goes.

If the id1/id2 keep changing, my guess is wrong. But you could still check x$ktuxe.ktuxesiz for any slot that had a steadily decreasing value.

On the other hand - why TX/4, rather than TX/6 which seems to be (a) necessary, and (b) safer. The only thought on that one is that perhaps your delete driven through an index, so the wait is for an index block and the default TX wait on index entries is mode 4.

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

• Original Message ----- From: "kyle Hailey" <kylelf_at_gmail.com> To: <oracle-l_at_freelists.org> Sent: Friday, January 18, 2008 12:20 AM Subject: How much rollback left to apply?

> 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
>

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