2018-03-08 Oracle 宅必备
这个专题讲一些日常运维的异常处理
今天讲一次大事务回滚导致的数据库奇慢
## 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会停止并重新启动