记一次故障诊断:事物回滚
客户数据库运行缓慢,告诉都在等待物理读事件,远程连接查看statspack报告
DB Name DB Id Instance Inst Num Release Cluster Host |
这里发现Block changes 高达12000以上,也就是每秒修改的12000块以上。
Top SQL
逻辑读
Buffer Gets Executions Gets per EXEC %Total TIME (s) TIME (s) Hash VALUE |
物理读
Physical Reads Executions Reads per EXEC %Total TIME (s) TIME (s) Hash VALUE |
表空间IO问题
Tablespace
------------------------------ |
数据文件
Tablespace Filename |
从上面可以看出Tablespace data、undotbs1的IO使用比较严重,严重了上面的update语句导致。
查看下undo的使用
ROLLBACK Segment Storage FOR DB: FSJZ Instance: fsjz Snaps: 18 -26 |
上面的信息可以得出6号undo段已经使用了4G。
查看事物开始时间
会话的信息
SELECT |
从上面来看这个事物的Undo使用达到了17G
查看Update 语句的执行计划
SQL> EXPLAIN plan FOR |
已选择16行。
这里得知SQL走的全表扫,仔细查看SQL 发现
《AND "A1"."col1" IS NULL OR "A1"."col1"<>'0'》 |
这两个条件应该增加括号,否则会oracle会找到col1不等于某个值的所有结果进行更新。
手工KILL掉这个进程后,数据库事物回滚异常缓慢
查看数据库参数
SQL> SHOW parameter cpu_count |
fast_start_parallel_rollback参数是加快回滚速度,这个参数默认是low,即2倍cpu数的并发度,进行并发的回滚。这个参数可以设置成high,即为4倍cpu数的并发度进行回滚。
如果设置了此参数为high或low,也就是4倍或2倍的cpu数,也会受到另外一些参数的影响,如PARALLEL_MAX_SERVERS,这个才是真正最大的并发度设置。
PARALLEL_MAX_SERVERS这个参数的默认值为PARALLEL_THREADS_PER_CPU * CPU_COUNT * concurrent_parallel_users * 5。PARALLEL_THREADS_PER_CPU 和CPU_COUNT都是初始化参数。
在一般情况下,并发的回滚总是比串行的快,但是当并发的子进程之间存在资源冲突的情况。
在并发子进程之间需要的资源冲突时,往往此时smon的等待事件是长期处于
Wait for stopper event to be increased |
而子进程的等待事件是较多出现
Wait for a undo record。 |
此时,就是子进程冲突了。并发的回滚速度反而不如串行的回滚速度。
查询数据库得知现在最大并行进程设置5,远远不够并行恢复需要的进程。查看smon进程等待事件时,竟然没有记录。
通过关闭并行进程恢复,提高恢复速度
ALTER system SET fast_start_parallel_rollback=FALSE; |
查看恢复进度
SQL> DECLARE |