概述

oracle等待事件是衡量oracle运行状况的重要依据及指示，等待事件分为两类：空闲等待事件和非空闲等待事件。

空闲等待事件是oracle正等待某种工作，在诊断和优化数据库时候，不用过多注意这部分事件，非空闲等待事件专门针对oracle的活动，指数据库任务或应用程序运行过程中发生的等待，

视图v$session，v$session_wait，v$session_wait_history，v$session_event，v$system_event提供了等待哪些资源以及每种资源等待的时间（timed_statistics参数为TRUE）等信息。

下面是之前总结的一些关于等待事件比较多用到的sql，大家可以看下。

1、查询数据库中过去30分钟引起最多等待的sql语句

select ash.USER_ID,
 u.username,
 sum(ash.WAIT_TIME) ttl_wait_time,
 s.SQL_TEXT
 from v$active_session_history ash, v$sqlarea s, dba_users u
 where ash.SAMPLE_TIME between sysdate - 60 / 2880 and sysdate
 and ash.SQL_ID = s.SQL_ID
 and ash.USER_ID = u.user_id
 group by ash.USER_ID, s.SQL_TEXT, u.username
 order by ttl_wait_time desc;

2、查询数据库过去15分钟最重要的等待事件

select ash.EVENT, sum(ash.WAIT_TIME + ash.TIME_WAITED) total_wait_time
 from v$active_session_history ash
 where ash.SAMPLE_TIME between sysdate - 30 / 2880 and sysdate
 group by event
 order by total_wait_time desc;

3、在过去15分钟哪些用户经历了等待

select s.SID,
 s.USERNAME,
 sum(ash.WAIT_TIME + ash.TIME_WAITED) total_wait_time
 from v$active_session_history ash, v$session s
 where ash.SAMPLE_TIME between sysdate - 30 / 2880 and sysdate
 and ash.SESSION_ID = s.SID
 group by s.SID, s.USERNAME
 order by total_wait_time desc;

4、查询过去15分钟等待时间最长的对象

select a.CURRENT_OBJ#,
 d.object_name,
 d.object_type,
 a.EVENT,
 sum(a.WAIT_TIME + a.TIME_WAITED) total_wait_time
 from v$active_session_history a, dba_objects d
 where a.SAMPLE_TIME between sysdate - 30 / 2880 and sysdate
 and a.CURRENT_OBJ# = d.object_id
 group by a.CURRENT_OBJ#, d.object_name, d.object_type, a.EVENT
 order by total_wait_time desc;

5、查询过去15分钟等待时间最长的sql语句

select a.USER_ID,
 u.username,
 s.SQL_TEXT,
 sum(a.WAIT_TIME + a.TIME_WAITED) total_wait_time
 from v$active_session_history a, v$sqlarea s, dba_users u
 where a.SAMPLE_TIME between sysdate - 30 / 2880 and sysdate
 and a.SQL_ID = s.SQL_ID
 and a.USER_ID = u.user_id
 group by a.USER_ID, s.SQL_TEXT, u.username
 order by total_wait_time desc;

后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注一下~