https://blog.csdn.net/hijk139/article/details/21543127

回滾的速度快慢透過引數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來確定

SQL> select usn, state, undoblocksdone, undoblockstotal, CPUTIME, pid,xid, rcvservers from v$fast_start_transactions;

       USN STATE            UNDOBLOCKSDONE UNDOBLOCKSTOTAL    CPUTIME        PID XID              RCVSERVERS
---------- ---------------- -------------- --------------- ---------- ---------- ---------------- ----------
       454 RECOVERED                110143          110143        210            01C600210027E0D9          1
       468 RECOVERED                   430             430         17            01D40000001F3A36        128

USN：事務對應的undo段
STATE：事務的狀態，可選的值為（BE RECOVERED, RECOVERED, or RECOVERING）
UNDOBLOCKSDONE：事物中已經完成的undo塊
UNDOBLOCKSTOTAL：總的需要recovery的undo資料塊
CPUTIME：已經回滾的時間，單位是秒
RCVSERVERS：回滾的並行程式數

--補充，查詢回滾時間更好的指令碼
SQL> select undoblockstotal "Total",
       undoblocksdone "Done",
       undoblockstotal - undoblocksdone "ToDo",
       decode(cputime,
              0,
              'unknown',
              to_char(sysdate + (((undoblockstotal - undoblocksdone) /
                      (undoblocksdone / cputime)) / 86400),
                      'yyyy-mm-dd hh24:mi:ss')) "Estimated time to complete"，to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss')
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×4=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                          32

3，由於估計還有103/(26922/464160)=30分鐘才能執行完，為了降低對系統效能的影響，對相關表進行了truncate（業務表中的資料不再需要）

SQL> truncate table user1.JT_t1_20140318;

4，truncate時，短時間內出現了row cache lock異常等待，大約幾十秒之後，恢復正常，truncat操作能結束undo回滾操作嗎？

5，其實為了減少undo的影響，可以透過設定fast_start_parallel_rollback，可以線上修改，立即生效

alter system set fast_start_parallel_rollback= FALSE;

引數fast_start_parallel_rollback調整oracle回滾的速度

相關文章