回滾的速度快慢通過參數fast_start_parallel_rollback來實現,此參數可以動態調整關於fast_start_parallel_rollback參數,此參數決定了回滾啟動的並行次數,在繁忙的系統或者IO性能較差的系統,如果出現大量回滾操作,會顯著影響系統系統,可以通過調整此參數來降低影響。官方文檔的定義如下
FAST_START_PARALLEL_ROLLBACK specifies the degree of parallelism used when recovering terminated transactions. Terminated transactions are transactions that are active before a system failure. If a system fails when there are uncommitted parallel DML or DDL transactions, then you can speed up transaction recovery during startup by using this parameter. Values: FALSE Parallel rollback is disabled LOW Limits the maximum degree of parallelism to 2 * CPU_COUNT HIGH Limits the maximum degree of parallelism to 4 * CPU_COUNT If you change the value of this parameter, then transaction recovery will be stopped and restarted with the new implied degree of parallelism.回滾過程中,回滾的進度可以通過視圖V$FAST_START_TRANSACTIONS來確定
補充,查詢回滾時間更好的腳本 sys@MS4ADB3(dtydb5)> select undoblockstotal "Total", undoblocksdone "Done", undoblockstotal-undoblocksdone 2 "ToDo",decode(cputime,0,'unknown',to_char(sysdate+(((undoblockstotal-undoblocksdone) 3 / (undoblocksdone / cputime)) / 86400),'yyyy-mm-dd hh24:mi:ss')) 4 "Estimated time to complete",to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') 5 from v$fast_start_transactions; Total MB Done ToDo Estimated time to complete TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:SS' ---------- ---------- ---------- -------------------------------------- -------------------------------------- 36,767 36767 0 2014-03-19 16:59:19 2014-03-19 16:59:19 7,209 7209 0 2014-03-19 16:59:19 2014-03-19 16:59:19 3,428 3428 0 2014-03-19 16:59:19 2014-03-19 16:59:19 34,346 1604 32742 2014-03-19 17:25:31 2014-03-19 16:59:19下面是一次大量wait for a undo record等待事件的處理過程
1,某用戶使用plsql執行某 insert操作異常,導致表空間不斷增長,於是手工kill該回滾停掉,kill後大量wait for a undo record,大約100多個
2,查詢v$fast_start_transactions視圖,由於fast_start_parallel_rollback參數設置為HIGH,且cpu為32個,因此並行進程為32×3=128個SQL> select usn, state, undoblocksdone, undoblockstotal, CPUTIME, pid,xid, rcvservers from v$fast_start_transactions; USN STATE UNDOBLOCKSDONE UNDOBLOCKSTOTAL CPUTIME PID XID RCVSERVERS ---------- ---------------- -------------- --------------- ---------- ---------- ---------------- ---------- 454 RECOVERING 26922 464160 103 3744 01C600210027E0D9 128 468 RECOVERED 430 430 17 01D40000001F3A36 128 SQL> SHOW parameter ROLLBACK NAME TYPE VALUE ------------------------------------ -------------------------------- ------------------------------ fast_start_parallel_rollback string HIGH SQL> show parameter cpu NAME TYPE VALUE ------------------------------------ -------------------------------- ------------------------------ cpu_count integer 323,由於估計還有103/(26922/464160)=30分鐘才能執行完,為了降低對系統性能的影響,對相關表進行了truncate(業務表中的數據不再需要)