[Oracle 故障处理]记一次大事务回滚导致的数据库奇慢

这个专题讲一些日常运维的异常处理

以前的请查看:

http://www.zhaibibei.cn/oracle/1.1/

今天讲一次大事务回滚导致的数据库奇慢

1. 现象

公司一套测试数据库User反应查询还可以，做DML操作没有反应，会hang住

2. 原因查找

2.1 查看等待事件

select* from v$session_wait order by event desc ;

发现有大量的checkpoint incompleted 和Wait for a undo record等待

2.2 诊断 checkpoint incomplete

通过v$session_wait 对应的SID号对应的进程全部对应到的是并行进程(J000-J016)

2.3 查询并行状态

SELECT QCSID, SID, INST_ID "Inst", SERVER_GROUP "Group", SERVER_SET "Set", DEGREE "Degree", REQ_DEGREE "Req Degree" FROM GV$PX_SESSION ORDER BY QCSID, QCINST_ID, SERVER_GROUP, SERVER_SET;

上面语句可以查找出 coordinator 的SID，通过查询其对应的是SMON进程

该进程是数据库的主进程我们无法对其做操作

2.4 诊断Wait for a undo record

通过搜索我们查出如下网站有所发现

http://expertoracle.com/2017/06/19/wait-for-a-undo-record-or-wait-for-stopper-event-to-be-increased-database-messages/

从上面我们得知当Oracle做大事物回滚时smon进程会作为coordinator 启动并行恢复

这时就可能会导致数据库DML语句无法继续

2.5 查询undo表空间使用率

SELECT round(((SELECT (NVL(SUM(bytes), 0))FROM dba_undo_extents WHERE tablespace_name = (select value from v$parameter where lower(name) = 'undo_tablespace') AND status IN ('ACTIVE', 'UNEXPIRED')) * 100) / (SELECT SUM(bytes) FROM dba_data_files WHERE tablespace_name = (select value from v$parameter where lower(name) = 'undo_tablespace')), 2) PCT_INUSE FROM dual

2.6 查看当前事务占用的undo大小

SELECT s.sid, s.username, s.program, t.name, t.used_ublk * (SELECT value/1024/1024 FROM v$system_parameter2 WHERE name='db_block_size') as "undoMB", flag,space,recursive,noundo,ptx, t.start_time start_mmddyy, t.status FROM v$transaction t, v$session s WHERE t.addr=s.taddr(+) ORDER BY t.used_ublk DESC;

2.7查看undo表空间各个时间段retention大小

select to_char(begin_time, 'DD-MON-RR HH24:MI') begin_time, to_char(end_time, 'DD-MON-RR HH24:MI') end_time, tuned_undoretention from v$undostat order by end_time;

3.问题处理

临时解决方案我们需要调整fast_start_parallel_rollback参数为false

修改后重启数据库

fast_start_parallel_rollback = false scope=spfile;

如果不使用spfile，可能会导致大量enq: PE - contention等待

重启后等待回滚完成可将值修改回来

4.关于FAST_START_PARALLEL_ROLLBACK

该参数用于指定回滚的并行度，当使用fast-start parallel rollback时 smon充当coordinator角色并开启相应的多个J00进程，他可以有三个值

• 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

当修改该参数时rollback会停止并重新启动