查询当前系统正在运行的sql

select a.USERNAME,
       a.BLOCKING_INSTANCE,
       a.PROGRAM,
       a.SID,
       a.SERIAL#,
       a.EVENT,
       a.P1,
       a.P2,
       a.P3,
       a.SQL_ID,
       a.SQL_CHILD_NUMBER,
       b.SQL_TEXT
  from v$session a, v$sql b
 where a.SQL_ADDRESS = b.ADDRESS
   and a.SQL_HASH_VALUE = b.HASH_VALUE
   and a.SQL_CHILD_NUMBER = b.CHILD_NUMBER;


--查询阻塞和被阻塞的session
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采样到的等待事件排名
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采样到的等待事件排名
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') >
                       '2024-06-20 14:00:00'
                   AND to_char(ash.SAMPLE_TIME, 'YYYY-MM-DD HH24:MI:SS') <
                       '2024-06-20 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') >
                               '2024-06-20 14:00:00'
                           AND to_char(ash.SAMPLE_TIME,
                                       'YYYY-MM-DD HH24:MI:SS') <
                               '2024-06-20 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;


--查看过去7天sql执行的hash_plan_id和资源消耗统计变化
select sql_id,
       endtime,
       plan_hash_value,
       nvl2(exed, exed, 0) exec_avg_times_ms,
       nvl2(ems, ems, 0) exec_once_time_ms,
       nvl2(cms, cms, 0) "avg_CCWAIT(ms)",
       nvl2(ams, ams, 0) "avg_APWAIT(ms)",
       nvl2(clms, clms, 0) "avg_CMWAIT(ms)",
       nvl2(ioms, ioms, 0) "AVG_IOWAIT(ms)",
       nvl2(ctms, ctms, 0) "AVG_CPU_WAIT(ms)",
       nvl2(bfgets, bfgets, 0) "AVG_buffer gets",
       nvl2(drd, drd, 0) "avg_disk reads",
       nvl2(sd, sd, 0) "avg_sort",
       nvl2(fet, fet, 0) "avg_fetch",
       nvl2(rpd, rpd, 0) "avg_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 = 'xxxxxxxxx'
           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 - 7)
 order by snap_id



select sq.SQL_ID,
       sq.EXECUTIONS,
       sq.ELAPSED_TIME,
       sq.CPU_TIME / 1000 / 1000 s
  from v$sql sq
 order by sq.CPU_TIME desc;