问题分析指引之【异常等待事件】

一、前言

ORACLE等待事件，是ORACLE提供的一种非常强大的功能，能在数据库中指明正在发生的一些事件动作。通常只要ORACLE出现各类性能/异常/稀奇古怪问题，我第一件事情总是查询等待事件，这基本能定位到90%以上的问题原因。但是，处理ORACLE问题的时候不能单凭等待事件来分析，这样很可能进入死胡同。ORACLE毕竟是一个及其复杂的系统，需要把它和其周边的应用、操作系统、硬件、软件版本、配置等来做一个整体的评估才是一个优秀的DBA。

ORACLE等待事件如果细分的话可以分为很多种：

SQL> select wait_class,count(*) from v$event_name group by wait_class order by 2;

但是我们日常中，一般就分为两种：空间等待和非空间等待，即上图的是否等于“Idle”来进行判断

空闲等待：意味着Oracle正在等待某种动作的发生，实际上并不是因为繁忙而等待，而是因为没有事情做所以等待。系统并没有出现性能上的问题。多数的空闲等待对数据库性能的影响不大，可以不必过多的关注。

非空闲等待：表示专门针对oracle的活动，指数据库任务或运行过程中发生的等待，一般表明当前数据库存在某些方面的问题。这些等待事件是我们在日常工作中需要着重关注的。

二、检查步骤简介

1、确认等待事件类型

如果问题在当前：查看当前等待事件

select event,count(*) from v$session group by event order by 2 desc;

如果问题已经过去1：带入问题时间段，查询ash等待事件

select * from (select event,count(*) from v$active_session_history where sample_time>=to_timestamp('&begin_time','yyyy-mm-dd hh24:mi:ss') and sample_time<=to_timestamp('&end_time','yyyy-mm-dd hh24:mi:ss') group by event order by 2 desc)where rownum<=10;

如果问题已经过去2：即ash无法查到，则需要到dba_hist中查询

select * from dba_hist_snapshot order by snap_id desc;                                           --确认snapshot范围

select * from (select event,count(*) from dba_hist_active_sess_history where snap_id between &begin_snap and &end_snap group by event order by 2 desc)where rownum<=10;     --可以根据实际情况加入其它条件

2、确认等待事件的SQL

如果问题在当前：查看当前等待事件

select * from

(select event,sql_id,count(*) from v$session where status='ACTIVE' and sql_id is not null group by event,sql_id order by 3 desc)
where rownum<=10;

如果问题已经过去1：带入问题时间段，查询ash等待事件

select * from (select event,sql_id,count(*) from v$active_session_history where sample_time>=to_timestamp('&begin_time','yyyy-mm-dd hh24:mi:ss') and sample_time<=to_timestamp('&end_time','yyyy-mm-dd hh24:mi:ss') and event='&event_name' group by event,sql_id order by 3 desc)where rownum<=20;

如果问题已经过去2：即ash无法查到，则需要到dba_hist中查询

select * from dba_hist_snapshot order by snap_id desc;                                           --确认snapshot范围

select * from (select event,sql_id,count(*) from dba_hist_active_sess_history where event='&event_name' and snap_id between &begin_snap and &end_snap group by event,sql_id order by 3 desc)where rownum<=20;     --可以根据实际情况加入其它条件

3、检查SQL性能趋势

select * from(
select to_char(a.end_interval_time, 'YYYY-MM-DD HH24:MI:SS') end_interval_time,
s.sql_id,
s.plan_hash_value,
s.executions_delta,
round(s.buffer_gets_delta / executions_delta,3) as buffer_gets_delta_each,
round(s.elapsed_time_delta / executions_delta / 1000000,3) as "elapsed_time_delta/s",
round(s.cpu_time_delta / executions_delta / 1000000,3) as "cpu_time_delta/s",
round(s.physical_read_bytes_delta / executions_delta / 1000000,3) as "physical_read_bytes_delta/s",
round(s.ROWS_PROCESSED_DELTA / executions_delta,3) as ROWS_PROCESSED_DELTA
from dba_hist_sqlstat s, dba_hist_snapshot a
where s.snap_id = a.snap_id
and sql_id = '&sqlid'
and s.executions_delta > 0
order by s.snap_id desc
) where rownum<=2000;

备注：问题时间点前后关注点如下：

1. 执行计划是否有变化（即plan_hash_value是否有变化）。

2. 执行次数是否有突增

3. 物理逻辑读是否有突增

4. cpu和执行耗时是否有突增

4、获取SQL执行计划

select * from table(dbms_xplan.display_awr('&sql_id',&plan_hash_value,null,'ADVANCED'));

备注：如果期间执行计划变化，请逐一获取执行计划信息，确认具体变化内容。

5、根据实际等待事件类型和执行计划进行分析并优化

三、常见异常等待事件原因和处理思路

以等待事件名字进行排序

等待事件名	常见原因	备注
buffer busy waits	1、SQL执行效率慢（执行计划突变，执行计划走错，执行次数过多，索引不合理等） 2、热块
control file parallel write control file sequential read control file single write	大量操作读/写控制文件 1、备份 2、存储IO问题 3、日志切换频繁（reodlog大小和组数可能不合理） 4、dump控制文件
cursor: mutex S cursor: mutex X cursor: pin S cursor: pin S wait on X cursor: pin X	1、业务繁忙期编译存储过程、函数、包等 2、业务繁忙期间DDL表/索引等 3、硬解析过多 4、未使用绑定变量/绑定变量过多 5、传参、字段类型问题导致隐式类型转换 6、version count过高，父游标无法共享 7、执行次数过多 8、共享池大小不合理 9、共享子池数量不合理 10、sga设置不合理，导致内存抖动
db file scattered read	物理IO、多块读、绝大部分为全表扫描和索引快速全扫描 1、db cache过小，内存命中率过低 2、执行计划异常：TABLE ACCESS FULL / INDEX FAST FULL SCAN 3、存储IO性能问题
db file sequential read	物理IO、单块读、索引扫描 1、db cache过小，内存命中率过低 2、sql执行效率有问题 3、存储IO性能问题
direct path read	1、执行计划异常：全表扫描 2、并行操作导致 3、参数不合理导致：_serial_direct_read / _small_table_threshold / _very_large_object_threshold	建议调整参数_serial_direct_read，设置为never
direct path read temp direct path write temp	排序导致，请关注temp表空间使用情况。 1、sql性能问题，执行计划异常，笛卡尔积比较常见：MERGE JOIN CARTESIAN 2、使用了大量的排序操作
enq: CF - contention	Control File锁 1、备份恢复 2、存储IO 3、日志切换、大量DML
enq: HW - contention	高水位锁，段/数据文件扩展频繁 1、大量数据入库，比如：insert 2、bug：非分区表，且单表过大，近100G 3、数据文件自动扩展不合理	手动扩展数据文件 手动allocate extent
enq: HW - contention	高水位锁，段/数据文件扩展频繁 1、大量数据入库，比如：insert 2、bug：非分区表，且单表过大，近100G 3、数据文件自动扩展不合理	手动扩展数据文件 手动allocate extent
enq: SQ - contention	sequence锁 1、cache过小 2、rac下order模式问题	建议规范新建sequence时指定cache 40（一般40可以满足市面上95%的业务系统并发）
enq: TM - contention	表锁 1、全表锁 2、外键没有索引 3、insert /+append/	外键必须有索引
enq: TX - allocate ITL entry	ITL事务槽锁 1、表/索引默认事务槽过小：select ini_trans from dba_tables where table_name=’&table_name’; 2、事务过于频繁	增大该等待对应对象的事务槽，一般6-16足够
enq: TX - contention enq: TX - row lock contention	行锁 1、dml相同行 2、业务逻辑问题 3、sql执行缓慢 4、未及时提交事务
enq: TX - index contention	索引分裂 大量insert导致索引分裂，一般出现在右侧索引上，比如：时间字段索引	调整pct_free 评估整改为反向键索引或hash索引
enq: US - contention	undo segment锁 1、undo空间不足 2、sql导致undo使用过于频繁 3、sql逻辑问题，未批量处理，及时释放undo
free buffer waits	1、db cache不足 2、db writer 不足，导致写脏块慢 3、存储IO差，导致写脏块慢 4、延迟块清除 5、大量dml导致不停产生脏块 6、低效SQL消耗过多内存
latch free	1、硬解析过多 2、未使用绑定变量/绑定变量过多 3、传参、字段类型问题导致隐式类型转换 4、version count过高，父游标无法共享 5、执行次数过多 6、共享池大小不合理 7、共享子池数量不合理 8、sga设置不合理，导致内存抖动 9、参数不合理，可增加session_cached_cursors减少软解析
latch: cache buffers chains	1、SQL执行效率慢（执行计划突变，执行计划走错，执行次数过多，索引不合理等） 2、热块 3、简单sql大量buffer gets，一般是由于大事务相关CR数据被其他事务读取。
latch: undo global data	undo争用 1、undo_retention / undo_management 不合理 2、undo过小 3、sql导致undo使用频繁 4、sql逻辑问题，未批量处理，及时释放undo
library cache lock library cache pin	1、业务繁忙期编译存储过程、函数、包等 2、业务繁忙期间DDL表/索引等 3、密码延迟验证
library cache: mutex ***	1、业务繁忙期编译存储过程、函数、包等 2、业务繁忙期间DDL表/索引等 3、硬解析过多 4、未使用绑定变量/绑定变量过多 5、传参、字段类型问题导致隐式类型转换 6、version count过高，父游标无法共享 7、执行次数过多 8、共享池大小不合理 9、共享子池数量不合理 10、sga设置不合理，导致内存抖动 11、参数不合理，可增加session_cached_cursors减少软解析
log buffer space	1、log buffer 不足 2、大量dml 3、存储IO性能差 4、lgwr进程优先级不够
log file switch ***	1、log buffer 不足 2、大量dml 3、坏块 4、存储IO性能差 5、redolog 大小和组数量不合理 6、fra满 7、卷空间满 8、db writer不足
log file parallel write	1、存储IO性能差 2、CPU处理能力不足 3、lgwr进程优先级不够
log file sync	1、commit次数过多 2、存储IO性能差 3、大量dml+redolog大小和组不合理 4、log buffer不足 5、隐含参数问题：_use_adaptive_log_file_sync 6、CPU处理能力不足 7、lgwr进程优先级不够	1、建议调整参数_use_adaptive_log_file_sync设置为false 2、一般同log file parallel write进行比较，来判断是否IO硬件性能问题
PX Deq ***	并行导致 1、sql文本中加入了并行 2、会话级别加入了并行 3、参数不合理：parallel_min_servers 4、表、索引开启了并行度：degree代表默认并行度： select owner,table_name,degree from dba_tables where degree>1; select owner,index_name,degree from dba_indexes where degree>1;	生产环境不允许使用并行
read by other session	1、SQL执行计划异常（执行计划走错，索引不合理等） 2、db cache内存设置较小	和热块没有关系，因为热块不应该被刷出内存。ORACLE把它从buffer busy waits分离出来，自然是有原因的。
row cache lock	1、共享池大小不合理 2、共享子池数量不合理 3、对象设置不合理。比如sequence的cache，表空间的自动扩展大小等 4、频繁大量grant 5、sga设置不合理，导致内存抖动	V$ROWCACHE DBA_HIST_ROWCACHE_SUMMARY