参数fast_start_parallel_rollback调整oracle回滚的速度
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 hh34:mi:ss')) "Estimated time to complete"，to_char(sysdate, 'yyyy-mm-dd hh34: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回滚的速度
地址分享：http://scyanting.com/article/piohpo.html
回滚的速度快慢通过参数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 hh34:mi:ss')) "Estimated time to complete"，to_char(sysdate, 'yyyy-mm-dd hh34: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回滚的速度
地址分享：http://scyanting.com/article/piohpo.html
