Undo Tracker

Monitor long-running rollback processes

If you've cancelled a long-running DML operation such as an UPDATE or DELETE and it is taking a while to come back to the prompt, most likely it is rolling back its changes. (You may also see this for sessions that have been killed but are still listed in v$session.) Querying v$transaction a couple of times will conform this, as you will see the number of undo blocks in use by the session going down, and when it reaches zero the rollback will be complete.

The idea behind undo_tracker.sql is to calculate the rate at which the undo block count is going down based on the amount of change and the time elapsed, and extrapolate how long it will take to complete at the current rate.

Rather than relying on DBMS_LOCK.SLEEP to pause between the two samples (inconveniently, the only 'sleep' commands in Oracle are in privileged system packages to which standard users do not have access), undo_tracker.sql pauses and prompts you to press Return. This means the only privilege needed to run it is SELECT_CATALOG_ROLE or equivalent.

col undo_records    new_value undo_records_start
col time_now        new_value time_start noprint
col session_id      new_value session_id
col session_serial  new_value session_serial
col session_status  format a14
col transaction_status format a19
col osuser          format a30
col username        format a30

col 1 new_value 1
select 1 from dual where 1=2;
def session_specifier = '&1'
 
select dbms_utility.get_time as time_now
     , se.sid         as session_id
     , se.serial#     as session_serial
     , se.username
     , se.status      as session_status
     , tr.status      as transaction_status
     , tr.start_time  as transaction_start
     , tr.used_urec   as undo_records
from   v$session      se
       join v$transaction tr on tr.addr = se.taddr
where  se.type = 'USER'
and    (   se.sid = nvl(to_number('&session_specifier'),se.sid) -- and    se.status = 'KILLED'
        or se.username = nvl('&session_specifier',se.username) )
order by sid;
 
pause >>> wait a few seconds then press Enter...
 
col undo_records  new_value undo_records_end
col time_now      new_value time_end
 
select se.sid         as session_id
     , tr.used_urec   as undo_records
     , round((dbms_utility.get_time - to_number('&time_start'))/100,1) sample_interval
     , to_number('&undo_records_start') - tr.used_urec as undo_rec_change
     , round(100 * abs(to_number('&undo_records_start') - tr.used_urec) / (dbms_utility.get_time - to_number('&time_start')),1) undo_rec_change_per_sec
     , round(tr.used_urec / (100 * nullif(abs(to_number('&undo_records_start') - tr.used_urec),0) / (dbms_utility.get_time - to_number('&time_start'))) / 60,1) minutes_to_undo
     , round(tr.used_urec / (100 * nullif(abs(to_number('&undo_records_start') - tr.used_urec),0) / (dbms_utility.get_time - to_number('&time_start'))) / 3600,1) hours_to_undo
     , to_char(sysdate + numtodsinterval(tr.used_urec / nullif(100* abs(to_number('&undo_records_start') - tr.used_urec) / nullif(dbms_utility.get_time - to_number('&time_start'),0),0) / 60, 'MINUTE'), 'fmDay HH24:MI') as est_completion
from   v$session      se
       join v$transaction tr on tr.addr = se.taddr
where  se.sid = to_number('&session_id')
and    se.serial# = to_number('&session_serial')
order by sid;