os: centos 7.6
db: oracle 19.3
oracle在较大事务出现异常后,执行回滚通常很慢,容易让大家失去耐心.
其实可以从如下几个方面入手
undo datafile 放在 ssd 上
现在自己pc都要买个 nvme 的ssd,更何况生产环境的集中式存储.
目前全闪存存储也比较便宜,完全在可接受的范围.
fast_start_parallel_rollback
调整并行回滚进程的数量,待回滚结束,为了减少undo的影响,fast_start_parallel_rollback恢复为false 或者 low.可以在线修改,立即生效.
19c 默认值已经是low了(个人认为19c都不用调整,使用默认值即可).
设置 parallel_force_local 是为了减少对 rac 中其余实例的冲击.
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
SQL> show parameter fast_start_paralleL_rollback;
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
fast_start_parallel_rollback string LOW
SQL> show parameter parallel_force_local;
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
parallel_force_local boolean FALSE
SQL>
SQL> alter system set fast_start_parallel_rollback = high;
alter system set parallel_force_local=true;
alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
select *
from v$fast_start_servers
where 1=1
;
select usn,
state,
undoblockstotal ,
undoblocksdone ,
undoblockstotal - undoblocksdone undoblockstotal_undoblocksdone,
decode(cputime,0,' ',
sysdate + (((undoblockstotal - undoblocksdone) /(decode(UNDOBLOCKSDONE,0,1,UNDOBLOCKSDONE) / cputime)) / 86400)) complete_datetime
from v$fast_start_transactions
where 1=1
;
参考:
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/FAST_START_PARALLEL_ROLLBACK.html#GUID-A94CC4C6-14D2-4B29-8008-163A3778E429
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/PARALLEL_FORCE_LOCAL.html#GUID-844DE3B2-3594-4EBD-B174-D5D47937F501
https://docs.oracle.com/en/database/oracle/oracle-database/19/vldbg/parameters-parallel-exec.html#GUID-7ED3242C-D2FF-4279-AF8A-C7CFB7ED5F2A