程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> 調整oracle回滾的速度

調整oracle回滾的速度

編輯:Oracle教程

回滾的速度快慢通過參數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:總的undo數據塊
CPUTIME:已經回滾的時間,單位是秒
RCVSERVERS:回滾的並行進程數
補充,查詢回滾時間更好的腳本
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                          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;

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved