Oracle等待事件查询语句整理

**–查询阻塞和被阻塞的session，支持RAC，按instance分组

SELECT a.INST_ID,
       a.sid,
       a.SERIAL#,
       a.USERNAME,
       a.SQL_ID,
       a.PROGRAM,
       a.EVENT,
       a.BLOCKING_SESSION,
       a.WAIT_TIME_MICRO
  FROM GV$SESSION A
 WHERE (A.INST_ID, a.SID) in
       (select b.BLOCKING_INSTANCE, b.BLOCKING_SESSION from gv$session b)
union all
select a.INST_ID,
       a.sid,
       a.SERIAL#,
       a.USERNAME,
       a.SQL_ID,
       a.PROGRAM,
       a.EVENT,
       a.BLOCKING_SESSION,
       a.WAIT_TIME_MICRO
  FROM GV$SESSION A
 where a.BLOCKING_SESSION is not null
 order by BLOCKING_SESSION nulls first;

–最近30分钟内ASH采样到的等待事件排名，支持RAC,按instance分组

select *
  from (select inst_id,
               rank() over(partition by inst_id order by cnt desc) rk,
               event,
               cnt
          from (select ash.INST_ID, ash.EVENT, count(*) CNT
                  from gv$active_session_history ash
                 where ash.SAMPLE_TIME > sysdate - 1 / 24 / 60 * 30
                   and event is not null
                 group by ash.INST_ID, ash.EVENT))
 where rk <= 10;

–某段事件内ASM采样到的等待事件排名，并查询采样中，产生此等待事件的SQL，支持rac，按 instance分组

select a.inst_id, a.event, a.sql_id, a.sql_cnt, b.cnt event_cnt, sql_rk
  from (select inst_id,
               event,
               sql_id,
               sql_cnt,
               rank() over(partition by event order by sql_cnt desc) sql_rk
          from (select ash.INST_ID, ash.EVENT, ash.SQL_ID, count(*) sql_cnt
                  from gv$active_session_history ash
                 where to_char(ash.SAMPLE_TIME, 'YYYY-MM-DD HH24:MI:SS') >
                       '2021-07-25 14:00:00'
                   AND to_char(ash.SAMPLE_TIME, 'YYYY-MM-DD HH24:MI:SS') <
                       '2021-07-25 16:00:00'
                   AND EVENT IS NOT NULL
                 GROUP BY ASH.INST_ID, ASH.EVENT, ASH.SQL_ID)) A,
       (SELECT INST_ID, EVENT, CNT
          FROM (SELECT INST_ID,
                       rank() over(partition by INST_ID order by cnt desc) rk,
                       EVENT,
                       CNT
                  FROM (SELECT ASH.INST_ID, ASH.EVENT, COUNT(*) CNT
                          FROM GV$ACTIVE_SESSION_HISTORY ASH
                         where to_char(ash.SAMPLE_TIME,
                                       'YYYY-MM-DD HH24:MI:SS') >
                               '2021-07-25 14:00:00'
                           AND to_char(ash.SAMPLE_TIME,
                                       'YYYY-MM-DD HH24:MI:SS') <
                               '2021-07-25 16:00:00'
                           AND EVENT IS NOT NULL
                         GROUP BY ASH.INST_ID, ASH.EVENT))
         WHERE RK <= 3) B
 WHERE A.INST_ID = B.INST_ID
   AND A.EVENT = B.EVENT
   AND A.SQL_RK <= 5
 ORDER BY INST_ID, EVENT_CNT DESC, SQL_CNT DESC, SQL_RK;

–查看过去15天sql执行的hash_plan_id和资源消耗统计变化

select sql_id,
       endtime,
       plan_hash_value,
       nvl2(exed, exed, 0) "执行次数",
       nvl2(ems, ems, 0) "单次执行时间(ms)",
       nvl2(cms, cms, 0) "平均CCWAIT(ms)",
       nvl2(ams, ams, 0) "平均APWAIT(ms)",
       nvl2(clms, clms, 0) "平均CMWAIT(ms)",
       nvl2(ioms, ioms, 0) "平均IOWAIT(ms)",
       nvl2(ctms, ctms, 0) "平均CPU_WAIT(ms)",
       nvl2(bfgets, bfgets, 0) "平均buffer gets",
       nvl2(drd, drd, 0) "平均disk reads",
       nvl2(sd, sd, 0) "平均sort",
       nvl2(fet, fet, 0) "平均fetch",
       nvl2(rpd, rpd, 0) "平均rows process",
       snap_id
  from (select a.sql_Id,
               to_char(b.end_interval_time, 'yyyymmdd hh24:mi:ss') endtime,
               a.executions_delta exed,
               a.plan_hash_value,
               round(decode(a.executions_delta,0,0,a.elapsed_time_delta / a.executions_delta) / 1000,2) ems,
               round(decode(a.executions_delta,0,0,a.ccwait_delta / a.executions_delta) / 1000,2) cms,
               round(decode(a.executions_delta,0,0,a.apwait_delta / a.executions_delta) / 1000,2) ams,
               round(decode(a.executions_delta,0, 0,a.clwait_delta / a.executions_delta) / 1000,2) clms,
               round(decode(a.executions_delta,0, 0,a.iowait_delta / a.executions_delta) / 1000,2) ioms,
               round(decode(a.executions_delta, 0,0, a.cpu_time_delta / a.executions_delta) / 1000,2) ctms,
               round(decode(a.executions_delta, 0, 0,a.buffer_gets_delta / a.executions_delta),2) bfgets,
               round(decode(a.executions_delta,0, 0, a.disk_reads_delta / a.executions_delta),2) drd,
               round(decode(a.executions_delta,0,0, a.sorts_delta / a.executions_delta),2) sd,
               round(decode(a.executions_delta,0, 0,a.fetches_delta / a.executions_delta),2) fet,
               round(decode(a.executions_delta,0,0, a.rows_processed_delta / a.executions_delta), 4) rpd,
               b.snap_id
          from dba_hist_sqlstat a, dba_hist_snapshot b
           where sql_id = '62qcz0vz20a3t'
           and a.snap_id = b.snap_id
           and a.instance_number = b.instance_number
              --and b.instance_number=1
           and a.executions_delta <> 0
           and b.end_interval_time > sysdate - 15)
 order by snap_id

–查询某个会话的锁

select /*+rule*/SESSION_ID,OBJECT_ID,ORACLE_USERNAME,OS_USER_NAME,PROCESS,LOCKED_MODE from gv$locked_object where session_id=&sid;

–查询TMTX锁

select /*+rule*/* from v$lock where ctime >100 and type in ('TX','TM') order by 3,9;

–查询数据库中的锁

select /*+rule*/s.sid,p.spid,l.type,round(max(l.ctime)/60,0) lock_min,s.sql_id,s.USERNAME,b.owner,b.object_type,b.object_name from v$session s, v$process p,v$lock l,v$locked_object o,dba_objects b where  o.SESSION_ID=s.sid and s.sid=l.sid and o.OBJECT_ID=b.OBJECT_ID and s.paddr = p.addr and l.ctime >100 and l.type in ('TX','TM','FB') group by s.sid,p.spid,l.type,s.sql_id,s.USERNAME,b.owner,b.object_type,b.object_name order by 9,1,3,4;

说明：

dba_hist_active_sess_history/v$active_session_history查看SQL执行历史情况
v$session_wait/v$session_event查看数据库会话的等待事件
v$sga_target_advice数据库sga优化顾问视图
v$pga_target_advice数据库pga优化顾问视图
v$memory_target_advice内存优化顾问视图
dba_data_files查询表空间和对应数据文件
v$session_longops记录执行超过5s的sql，可以查询一个SQL语句执行了多长时间以及还要执行多长时间
v$fast_start_transactions/v$fast_start_servers可以查看所有回滚的事务
v$log视图可以查找到联机重做日志的日志序列号v$archived_log视图或
v$log_history视图中可以查找给定的归档重做日志的日志序列号
user_tab_columns视图将显示当前用户模式下的每张表的每一列的详细数据情况
gv$memory_dynamic_components记录内存变更的视图
gv$sql_monitor SQL监视视图
dba_tab_statistics表的信息统计**