>데이터 베이스 >MySQL 튜토리얼 >调整Oracle回滚的速度

调整Oracle回滚的速度

WBOY
WBOY원래의
2016-06-07 16:47:141320검색

回滚的速度快慢通过参数fast_start_parallel_rollback来实现,此参数可以动态调整 关于fast_start_parallel_rollback参数,此参数

回滚的速度快慢通过参数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

성명:
본 글의 내용은 네티즌들의 자발적인 기여로 작성되었으며, 저작권은 원저작자에게 있습니다. 본 사이트는 이에 상응하는 법적 책임을 지지 않습니다. 표절이나 침해가 의심되는 콘텐츠를 발견한 경우 admin@php.cn으로 문의하세요.