调整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 unmitted 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：回滚的并行进程数 补充，查询回滚时间更好的脚本 sysMS4ADB3(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 plete，to_char(sysdate,yyyy-mm-dd hh24:mi:ss) 5 from v$fast_start_transactions; Total MB Done ToDo Estimated time to plete TO_CHAR(SYSDATE,YYYY-MM-DDHH24:MI:SS - - - - - 36,767 36767 0 xx-03-19 16:59:19 xx-03-19 16:59:19 7,209 7209 0 xx-03-19 16:59:19 xx-03-19 16:59:19 3,428 3428 0 xx-03-19 16:59:19 xx-03-19 16:59:19 34,346 1604 32742 xx-03-19 17:25:31 xx-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个，因此并行进程为323=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_xx0318; 4，truncate时，短时间内出现了row cache lock异常等待，大约几十秒之后，