Wednesday, May 5, 2010

Oracle rollback - undo monitoring tips

How much time need When a long, running transaction has been rolled back
  • In Oracle 9i Database and below, you can issue the query which returns the number of undo records used by the current transaction, and if executed repeatedly, will show continuously reduced values because the rollback process will release the undo records as it progresses. You can then calculate the rate by taking snapshots for an interval and then extrapolate the result to estimate the finishing time.

    SELECT USED_UREC FROM V$TRANSACTION;
  • Also  we  can use  v$session_longops OPNAME column ='Transaction Rollback'
    select time_remaining
    from v$session_longops
    where sid = ;
  • v$fast_start_transactions contains the information about the progress of the transactions that the Oracle server is recovering. It also contains information about transactions that the Oracle server has recovered. For transactions that the Oracle server is recovering, the STATE is RECOVERING. For transactions that the Oracle server has recovered, the STATE is RECOVERED. Only limited historical information is kept in this view, and small (in terms of undo blocks) transactions are not tracked.


    SELECT state,undoblocksdone,undoblockstotal,cputime,rcvservers
    FROM v$fast_start_transactions;
  • v$fast_start_servers provides information about all the recovery servers performing, or that have performed parallel transaction recovery.If rcvservers column of the above  query   =1  means  transaction is being rolled back serially by SMON process.If >1  then you can select detailed  information     from  v$fast_start_servers  view 
  • if rcserver=1  means  FAST_START_PARALLEL_ROLLBACK  is  disabled   and  recovery is  done  by one process .we  can  change  this parameter online  in order  to speed the  recovery

    alter system set fast_start_parallel_rollback = HIGH;

    FALSE indicates that parallel rollback is disabled

    LOW limits the number of rollback processes to 2 * CPU_COUNT
    HIGH limits the number of rollback processes to 4 * CPU_COUNT

    Also be careful while setting  fast_start_parallel_rollback   because it can spwan 4*CPU_COUNT process  and  rise  the  cpu usage %100





No comments: