Long Delay before rolling back

I have learned that when you kill a session, you can monitor any rollback that occurs by query the v$transaction view and observing the used undoblock column- if it is decreasing, it is rolling back a transaction, if it is increasing, it is generating rollback for a transaction.

Here is a typical query that I would use:

SELECT a.sid, a.username, b.xidusn, b.used_urec, b.used_ublk   FROM v$session a, v$transaction b
  WHERE a.saddr = b.ses_addr;

Apparently when used_ublk decreases to zero, then the transaction has completed rolling back.

However, I am wondering why it often takes so long before this begins to decrease. Yesterday, we killed a session and after 1.5 hours, the used_urec and used_ublk values did not budge. Returning to work this morning, it is apparent that it did complete the rollback.

Is there any way to speed it up? What's Oracle doing before used_ublk begins to decrease?

Thanks,
Roger Received on Thu Feb 03 2005 - 08:08:08 CST