Thursday, September 29, 2005

Monitoring rollback progress


When a large transaction takes a long time to rollback, it is good to know how much of the rollback is done and estimate how long it is going to take. Given the session's sid, it can be done with the simple statement below, tested on Oracle9i. When a transaction is rolling back, the t.used_ublk and t.used_urec will decrease until they become 0. By sampling the two measures at different points of time, you can calculate how fast the rollback is and when it is going to complete.

SELECT t.used_ublk, t.used_urec
FROM v$session s, v$transaction t
WHERE s.taddr=t.addr
and s.SID =:sid;

No comments: