所有等待事件

一、前言
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
如果问题已经过去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;
1
如果问题已经过去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;     --可以根据实际情况加入其它条件
1
2
3
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
2
如果问题已经过去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;
1
如果问题已经过去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;     --可以根据实际情况加入其它条件
1
2
3
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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
备注：问题时间点前后关注点如下：

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

执行次数是否有突增

物理逻辑读是否有突增

cpu和执行耗时是否有突增

4、获取SQL执行计划
select * from table(dbms_xplan.display_awr('&sql_id',&plan_hash_value,null,'ADVANCED'));
1
备注：如果期间执行计划变化，请逐一获取执行计划信息，确认具体变化内容。

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
————————————————
版权声明：本文为CSDN博主「Mr.阿门」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/wc3083/article/details/120203506