Large, long-running transactions can generate large volumes of UNDO, which is how Oracle restores the data to its starting point should the transaction fail or be cancelled. Of course, this also generates long rollback times that can seem interminable when someone is waiting for the rollback to complete before other work can begin. Monitoring the rollback progress isn’t difficult since v$transaction, in conjunction with v$session, can report on the UNDO blocks that have yet to be applied. Let’s see how that can be done.
V$TRANSACTION records various pieces of information about active and cancelled transactions, with one of those pieces being the blocks of UNDO that are used (either allocated or applied, depending on whether the transaction is running or being rolled back). That information can be used to great advantage when monitoring the progress of a rollback. The queries used are shown below:
set echo on pagesize 100 linesize 132
select
case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
else 'Not Rolling Back'
end as "Roll Status"
from v$transaction
where addr in (select taddr from v$session where username = upper('&&1'));
select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
from v$transaction t, v$session s
where t.addr = s.taddr
group by s.sid, s.serial#, s.client_info, t.addr;
The first query returns the rollback status of the transaction; the second returns the used UNDO blocks being generated by the transaction or read to undo the work that has already been done. The client_info column may or may not be populated (it isn’t in these examples) but it is provided to help differentiate different sessions an application user may have running so that the transaction or transactions of interest can be monitored. Looking at an example should help; a long-running transaction is in progress in session #1; looking at the UNDO blocks being allocated (from a second session) we see the USED_UBLK column increasing in value and that the transaction is not rolling back:
BING @ quanghoo >
BING @ quanghoo > set echo on pagesize 100 linesize 132
BING @ quanghoo >
BING @ quanghoo > select
2 case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
3 else 'Not Rolling Back'
4 end as "Roll Status"
5 from v$transaction
6 where addr in (select taddr from v$session where username = upper('&&1'));
old 6: where addr in (select taddr from v$session where username = upper('&&1'))
new 6: where addr in (select taddr from v$session where username = upper('bung'))
Roll Status
----------------
Not Rolling Back
BING @ quanghoo >
BING @ quanghoo > select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
2 from v$transaction t, v$session s
3 where t.addr = s.taddr
4 group by s.sid, s.serial#, s.client_info, t.addr;
SID SERIAL# CLIENT_INFO ADDR SUM(T.USED_UBLK)
---------- ---------- ---------------------------------------------------------------- ---------------- ----------------
133 30106 000007FF8EF7DC88 430
BING @ quanghoo >
BING @ quanghoo > @rollback_lrg_update_ex2 bung
BING @ quanghoo > connect bing/bong
Connected.
BING @ quanghoo >
BING @ quanghoo > set echo on pagesize 100 linesize 132
BING @ quanghoo >
BING @ quanghoo > select
2 case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
3 else 'Not Rolling Back'
4 end as "Roll Status"
5 from v$transaction
6 where addr in (select taddr from v$session where username = upper('&&1'));
old 6: where addr in (select taddr from v$session where username = upper('&&1'))
new 6: where addr in (select taddr from v$session where username = upper('bung'))
Roll Status
----------------
Not Rolling Back
BING @ quanghoo >
BING @ quanghoo > select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
2 from v$transaction t, v$session s
3 where t.addr = s.taddr
4 group by s.sid, s.serial#, s.client_info, t.addr;
SID SERIAL# CLIENT_INFO ADDR SUM(T.USED_UBLK)
---------- ---------- ---------------------------------------------------------------- ---------------- ----------------
133 30106 000007FF8EF7DC88 739
BING @ quanghoo >
BING @ quanghoo > @rollback_lrg_update_ex2 bung
BING @ quanghoo > connect bing/bong
Connected.
BING @ quanghoo >
BING @ quanghoo > set echo on pagesize 100 linesize 132
BING @ quanghoo >
BING @ quanghoo > select
2 case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
3 else 'Not Rolling Back'
4 end as "Roll Status"
5 from v$transaction
6 where addr in (select taddr from v$session where username = upper('&&1'));
old 6: where addr in (select taddr from v$session where username = upper('&&1'))
new 6: where addr in (select taddr from v$session where username = upper('bung'))
no rows selected
BING @ quanghoo >
BING @ quanghoo > select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
2 from v$transaction t, v$session s
3 where t.addr = s.taddr
4 group by s.sid, s.serial#, s.client_info, t.addr;
no rows selected
BING @ quanghoo >
...
BING @ quanghoo > @rollback_lrg_update_ex2 bung
BING @ quanghoo > connect bing/bong
Connected.
BING @ quanghoo >
BING @ quanghoo > set echo on pagesize 100 linesize 132
BING @ quanghoo >
BING @ quanghoo > select
2 case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
3 else 'Not Rolling Back'
4 end as "Roll Status"
5 from v$transaction
6 where addr in (select taddr from v$session where username = upper('&&1'));
old 6: where addr in (select taddr from v$session where username = upper('&&1'))
new 6: where addr in (select taddr from v$session where username = upper('bung'))
Roll Status
----------------
Not Rolling Back
BING @ quanghoo >
BING @ quanghoo > select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
2 from v$transaction t, v$session s
3 where t.addr = s.taddr
4 group by s.sid, s.serial#, s.client_info, t.addr;
SID SERIAL# CLIENT_INFO ADDR SUM(T.USED_UBLK)
---------- ---------- ---------------------------------------------------------------- ---------------- ----------------
133 18316 000007FF8EF38688 6475
BING @ quanghoo >
BING @ quanghoo > @rollback_lrg_update_ex2 bung
BING @ quanghoo > connect bing/bong
Connected.
BING @ quanghoo >
BING @ quanghoo > set echo on pagesize 100 linesize 132
BING @ quanghoo >
BING @ quanghoo > select
2 case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
3 else 'Not Rolling Back'
4 end as "Roll Status"
5 from v$transaction
6 where addr in (select taddr from v$session where username = upper('&&1'));
old 6: where addr in (select taddr from v$session where username = upper('&&1'))
new 6: where addr in (select taddr from v$session where username = upper('bung'))
Roll Status
----------------
Not Rolling Back
BING @ quanghoo >
BING @ quanghoo > select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
2 from v$transaction t, v$session s
3 where t.addr = s.taddr
4 group by s.sid, s.serial#, s.client_info, t.addr;
SID SERIAL# CLIENT_INFO ADDR SUM(T.USED_UBLK)
---------- ---------- ---------------------------------------------------------------- ---------------- ----------------
133 18316 000007FF8EF38688 6755
BING @ quanghoo >
At the time the transaction is cancelled, the used UNDO block total was:
--
-- Transaction still active and generating UNDO
--
BING @ quanghoo >
BING @ quanghoo > select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
2 from v$transaction t, v$session s
3 where t.addr = s.taddr
4 group by s.sid, s.serial#, s.client_info, t.addr;
SID SERIAL# CLIENT_INFO ADDR SUM(T.USED_UBLK)
---------- ---------- ---------------------------------------------------------------- ---------------- ----------------
133 18316 000007FF8EF38688 7914
BING @ quanghoo >
From the second session, we can now monitor the progress of the rollback using the same queries as before. This time the first query reports a rollback is in progress:
--
-- Transaction cancelled
--
-- Successive executions of these queries return a decreasing used undo block count
-- thus tracking the rollback progress
--
BING @ quanghoo > @rollback_lrg_update_ex2 bung
BING @ quanghoo > connect bing/bong
Connected.
BING @ quanghoo >
BING @ quanghoo > set echo on pagesize 100 linesize 132
BING @ quanghoo >
BING @ quanghoo > select
2 case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
3 else 'Not Rolling Back'
4 end as "Roll Status"
5 from v$transaction
6 where addr in (select taddr from v$session where username = upper('&&1'));
old 6: where addr in (select taddr from v$session where username = upper('&&1'))
new 6: where addr in (select taddr from v$session where username = upper('bung'))
Roll Status
----------------
Rolling Back
BING @ quanghoo >
BING @ quanghoo > select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
2 from v$transaction t, v$session s
3 where t.addr = s.taddr
4 group by s.sid, s.serial#, s.client_info, t.addr;
SID SERIAL# CLIENT_INFO ADDR SUM(T.USED_UBLK)
---------- ---------- ---------------------------------------------------------------- ---------------- ----------------
133 18316 000007FF8EF38688 5110
BING @ quanghoo >
BING @ quanghoo > @rollback_lrg_update_ex2 bung
BING @ quanghoo > connect bing/bong
Connected.
BING @ quanghoo >
BING @ quanghoo > set echo on pagesize 100 linesize 132
BING @ quanghoo >
BING @ quanghoo > select
2 case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
3 else 'Not Rolling Back'
4 end as "Roll Status"
5 from v$transaction
6 where addr in (select taddr from v$session where username = upper('&&1'));
old 6: where addr in (select taddr from v$session where username = upper('&&1'))
new 6: where addr in (select taddr from v$session where username = upper('bung'))
Roll Status
----------------
Rolling Back
BING @ quanghoo >
BING @ quanghoo > select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
2 from v$transaction t, v$session s
3 where t.addr = s.taddr
4 group by s.sid, s.serial#, s.client_info, t.addr;
SID SERIAL# CLIENT_INFO ADDR SUM(T.USED_UBLK)
---------- ---------- ---------------------------------------------------------------- ---------------- ----------------
133 0 000007FF8EF38688 3491
BING @ quanghoo >
BING @ quanghoo > @rollback_lrg_update_ex2 bung
BING @ quanghoo > connect bing/bong
Connected.
BING @ quanghoo >
BING @ quanghoo > set echo on pagesize 100 linesize 132
BING @ quanghoo >
BING @ quanghoo > select
2 case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
3 else 'Not Rolling Back'
4 end as "Roll Status"
5 from v$transaction
6 where addr in (select taddr from v$session where username = upper('&&1'));
old 6: where addr in (select taddr from v$session where username = upper('&&1'))
new 6: where addr in (select taddr from v$session where username = upper('bung'))
Roll Status
----------------
Rolling Back
BING @ quanghoo >
BING @ quanghoo > select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
2 from v$transaction t, v$session s
3 where t.addr = s.taddr
4 group by s.sid, s.serial#, s.client_info, t.addr;
SID SERIAL# CLIENT_INFO ADDR SUM(T.USED_UBLK)
---------- ---------- ---------------------------------------------------------------- ---------------- ----------------
133 0 000007FF8EF38688 1363
BING @ quanghoo >
BING @ quanghoo > @rollback_lrg_update_ex2 bung
BING @ quanghoo > connect bing/bong
Connected.
BING @ quanghoo >
BING @ quanghoo > set echo on pagesize 100 linesize 132
BING @ quanghoo >
BING @ quanghoo > select
2 case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
3 else 'Not Rolling Back'
4 end as "Roll Status"
5 from v$transaction
6 where addr in (select taddr from v$session where username = upper('&&1'));
old 6: where addr in (select taddr from v$session where username = upper('&&1'))
new 6: where addr in (select taddr from v$session where username = upper('bung'))
Roll Status
----------------
Rolling Back
BING @ quanghoo >
BING @ quanghoo > select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
2 from v$transaction t, v$session s
3 where t.addr = s.taddr
4 group by s.sid, s.serial#, s.client_info, t.addr;
SID SERIAL# CLIENT_INFO ADDR SUM(T.USED_UBLK)
---------- ---------- ---------------------------------------------------------------- ---------------- ----------------
133 0 000007FF8EF38688 70
BING @ quanghoo >
--
-- Transaction rollback complete
--
BING @ quanghoo > @rollback_lrg_update_ex2 bung
BING @ quanghoo > connect bing/bong
Connected.
BING @ quanghoo >
BING @ quanghoo > set echo on pagesize 100 linesize 132
BING @ quanghoo >
BING @ quanghoo > select
2 case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
3 else 'Not Rolling Back'
4 end as "Roll Status"
5 from v$transaction
6 where addr in (select taddr from v$session where username = upper('&&1'));
old 6: where addr in (select taddr from v$session where username = upper('&&1'))
new 6: where addr in (select taddr from v$session where username = upper('bung'))
no rows selected
BING @ quanghoo >
BING @ quanghoo > select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
2 from v$transaction t, v$session s
3 where t.addr = s.taddr
4 group by s.sid, s.serial#, s.client_info, t.addr;
no rows selected
BING @ quanghoo >
The used UNDO block count eventually goes away (since the transaction is neither active nor rolling back) which signals the end of the rollback phase. Now other work can proceed against the table or tables involved in the previous transaction. And, having this data available usually makes the wait seem shorter as progress is registered for the rollback phase.
Waiting seems to be the hardest part, especially when no apparent progress can be monitored. Now, for rollbacks, a method is available to produce usable information regarding the progress of the rollback, which can make everyone’s life (at least for those who are waiting on a rollback to finish) a bit easier.
