How-to check rollback transactions and give an ETA

I created a pl/sql script to check if any transactions is rolling back by SMON or itself and give an ETA.

--Purpose: check rollback transactions and give ETA
--Created by: Alex Zeng, Sept 11, 2011
set serveroutput on
DECLARE
  type t_undoblocks is table of number index by varchar2(100);
  type t_ublk is table of number index by varchar2(100);
  v_undoblocks t_undoblocks;
  v_ublk t_ublk;
  v_eta number;
  v_sleep number := 3;
BEGIN
  for r in (SELECT cast(b.XID as varchar2(100)) xid, b.used_urec FROM v$transaction b)
  LOOP
     v_ublk(r.xid) := r.used_urec;
  end loop;  
  dbms_output.put_line('Checking if SMON is recovering any transactions');
  for r in (select cast(XID as varchar2(100)) xid, state,undoblocksdone,undoblockstotal,RCVSERVERS from V$FAST_START_TRANSACTIONS where state<>'RECOVERED') 
  LOOP
    v_undoblocks(r.xid) := r.undoblocksdone;
    dbms_output.put_line(rpad('TransactionID',25) || rpad('state',15) || rpad('recover_servers',20) || rpad('undo_blocks_total',20) || rpad('undo_blocks_done',20));
    dbms_output.put_line(rpad(r.XID,25) || rpad(r.state,25) || rpad(to_char(r.RCVSERVERS),20) || rpad(to_char(r.undoblockstotal),20) || rpad(to_char(r.undoblocksdone),20));
  end loop;

  dbms_output.put_line(chr(10) ||'Sleep '||v_sleep||' seconds to check again...');
  dbms_lock.sleep(v_sleep);

  for r in (select cast(XID as varchar2(100)) xid, state,undoblocksdone,undoblockstotal,RCVSERVERS from V$FAST_START_TRANSACTIONS where state<>'RECOVERED') 
  LOOP
    if v_undoblocks.exists(r.xid) then
       if r.undoblocksdone > v_undoblocks(r.xid) then
         v_eta := round((r.undoblockstotal-r.undoblocksdone)*v_sleep/60/(r.undoblocksdone-v_undoblocks(r.xid)),1);
         dbms_output.put_line('SMON is rolling back '||r.xid||'...'||r.undoblocksdone||' out of '||r.undoblockstotal||' blocks are done...ETA
is '||v_eta||' minutes');
       else
         dbms_output.put_line('SMON is rolling back '||r.xid||'...'||r.undoblocksdone||' out of '||r.undoblockstotal||' blocks are done...ETA
is unknown, pls try again');
       end if;
    end if;
  end loop;

  dbms_output.put_line(chr(10) ||'Checking if any transaction is rolling back by itself');
  for r in (SELECT a.sid, cast(b.XID as varchar2(100)) xid, b.used_urec FROM v$session a, v$transaction b WHERE a.saddr = b.ses_addr)
  LOOP
      if v_ublk.exists(r.xid) then
         if v_ublk(r.xid) > r.used_urec THEN
            v_eta := round(r.used_urec * v_sleep/60/(v_ublk(r.xid) - r.used_urec), 1);
            dbms_output.put_line('SID,XID : '||r.sid||','||r.xid||' is rolling back...'||r.used_urec||' blocks to go...ETA is '||v_eta||' minutes');
         end if;
      end if;
  end loop;  
end;
/

Save it in a file named rollback.sql. Here comes output examples:

SYS@DB: SQL> @rollback
Checking if SMON is recovering any transactions

Sleep 3 seconds to check again...

Checking if any transaction is rolling back by itself
SID,XID : 2175,0001002000000F64 is rolling back...857773 blocks to go...ETA is .8 minutes

PL/SQL procedure successfully completed.

SYS@DB: SQL> @rollback
Checking if SMON is recovering any transactions
TransactionID            state          recover_servers     undo_blocks_total   undo_blocks_done
000C000C00000002         RECOVERING               1                   9095                2626

Sleep 3 seconds to check again...
SMON is rolling back 000C000C00000002...4990 out of 9095 blocks are done...ETA is .1 minutes

Checking if any transaction is rolling back by itself

PL/SQL procedure successfully completed.

Advertisements

About Alex Zeng
I would be very happy if this blog can help you. I appreciate every honest comments. Please forgive me if I'm too busy to reply your comments in time.

6 Responses to How-to check rollback transactions and give an ETA

  1. Thank’s. Good script. It helped

  2. Rajan says:

    Hi Alex, Is there a way to find out what was the original transaction being recovered by smon?

    • Alex Zeng says:

      hi Rajan,

      I think if the transaction is being recovered by smon. That transaction session is already dead, be killed or db is bounced or for whatever reason.

      Thanks
      Alex

  3. Anantha says:

    Hi Alex,

    If SMON is recovering the dead transactions and to figure out the SQL’s what is the best method to do the recovery fast. Please add this info on the blog. Oracle does not have the info if appplication does not want the undo rollback to happen and bouncing does not help what can be done.

    Thanks,
    Anantha

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: