- 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
Wednesday, May 5, 2010
Oracle rollback - undo monitoring tips
How much time need When a long, running transaction has been rolled back
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment