优化ASH等待类对象定位SQL：提升性能与可读性的实践方案

-- File Name : ash_object_by_waitclass_19c_optimized.sql
-- Purpose : 根据WAITCLASS值，按EVENT,SQL_ID,CURRENT_OBJ排序，显示TOP 2的信息（兼容10g/11g/12c/19C）
-- 支持版本 : Oracle 10g, 11g, 12c, 19C (长期支持版本优化)
set echo off
set lines 400 pages 1000 heading on verify off
col time               for a28                  heading '时间区间(yyyy-mm-dd hh24:mi-mi)'
col event              for a50                  heading '等待事件/会话状态'
col erow               for 999999999            heading '事件行数'
col erowpercent        for 99.99                heading '事件占比(%)'
col sql_id             for a18                  heading 'SQL ID'
col sqlrow             for 99999999             heading '事件-SQL行数'
col sqlrowpercent      for 99.99                heading 'SQL占比(%)'
col object_id          for 999999               heading '对象ID'
col object_name        for a35                  heading '对象名称'
col object_type        for a15                  heading '对象类型'
col sqlobjrow          for 99999999             heading '事件-SQL-对象行数'
col sqlobjrowpercent   for 99.99                heading '对象占比(%)'

-- 输入参数（保留原默认值，兼容历史使用习惯）
ACCEPT begin_hours     prompt 'Enter Search Hours Ago (i.e. 2(default)) : '  default '2'
ACCEPT interval_hours  prompt 'Enter How Interval Hours  (i.e. 2(default)) : ' default '2'
ACCEPT waitclass       prompt 'Enter Search Wait Class  (i.e. User I/O(default) Or ON CPU) : ' default 'User I/O'
ACCEPT display_time    prompt 'Enter How Display Interval Minute  (i.e. 10(default)) : ' default '10'

-- 绑定变量（重命名冲突变量，确保类型匹配）
variable begin_hours    number;
variable interval_hours number;
variable display_min    number;  -- 替代原:time，避免与列名冲突
variable waitclass      varchar2(200);

begin
  :begin_hours    := &begin_hours;
  :interval_hours := &interval_hours;
  :display_min    := &display_time;  -- 动态时间分段粒度
  :waitclass      := '&waitclass';
end;
/

-- 分组中断设置，优化输出格式
break on time on event on erow on erowpercent on sql_id on sqlrow on sqlrowpercent

/* 核心查询逻辑：分层统计TOP2数据，关联对象详情，兼容19C */
SELECT time,
       event,
       erow,
       erowpercent,
       sql_id,
       sqlrow,
       sqlrowpercent,
       object_id,
       object_name,
       object_type,
       sqlobjrow,
       sqlobjrowpercent
FROM (
  SELECT time,
         event,
         erow,
         erowpercent,
         sql_id,
         sqlrow,
         sqlrowpercent,
         object_id,
         object_name,
         object_type,
         sqlobjrow,
         sqlobjrowpercent,
         -- 分层排序：时间维度TOP2事件、事件维度TOP2 SQL、SQL维度TOP2对象
         dense_rank() OVER (PARTITION BY time ORDER BY erow DESC) erowtop,
         dense_rank() OVER (PARTITION BY time, event ORDER BY sqlrow DESC) sqlrowtop,
         dense_rank() OVER (PARTITION BY time, event, sql_id ORDER BY sqlobjrow DESC) sqlobjrowtop
  FROM (
    SELECT 
           time,
           event,
           -- 事件级别统计：该时间区间内事件总采样数及占比
           SUM(cnt) OVER (PARTITION BY time, event) erow,
           ROUND(
             SUM(cnt) OVER (PARTITION BY time, event) / NULLIF(SUM(cnt) OVER (PARTITION BY time), 0) * 100,
             2
           ) erowpercent,
           sql_id,
           -- 事件-SQL级别统计：该事件下SQL的采样数及占比
           SUM(cnt) OVER (PARTITION BY time, event, sql_id) sqlrow,
           ROUND(
             SUM(cnt) OVER (PARTITION BY time, event, sql_id) / NULLIF(SUM(cnt) OVER (PARTITION BY time, event), 0) * 100,
             2
           ) sqlrowpercent,
           current_obj# AS object_id,
           -- 关联对象名称（左连接避免数据丢失，处理空值）
           NVL(do.object_name, 
               CASE WHEN current_obj# = 0 THEN 'SYSTEM OBJECT' ELSE 'N/A' END) AS object_name,
           NVL(do.object_type, 'N/A') AS object_type,
           -- 事件-SQL-对象级别统计：该SQL下对象的采样数及占比
           SUM(cnt) OVER (PARTITION BY time, event, sql_id, current_obj#) sqlobjrow,
           ROUND(
             SUM(cnt) OVER (PARTITION BY time, event, sql_id, current_obj#) / NULLIF(SUM(cnt) OVER (PARTITION BY time, event, sql_id), 0) * 100,
             2
           ) sqlobjrowpercent
    FROM (
      -- 基础数据提取：合并实时ASH（GV$）与历史ASH（DBA_HIST）数据
      SELECT 
             -- 动态时间分段：根据输入的分钟数生成区间（如10分钟：00-10、10-20）
             TO_CHAR(TRUNC(SAMPLE_TIME, 'HH24'), 'yyyy-mm-dd hh24:')
             || LPAD(FLOOR(EXTRACT(MINUTE FROM SAMPLE_TIME)/:display_min) * :display_min, 2, '0')
             || '-'
             || LPAD(FLOOR(EXTRACT(MINUTE FROM SAMPLE_TIME)/:display_min) * :display_min + :display_min, 2, '0') AS time,
             NVL(event, session_state) AS event,  -- 兼容等待事件（如User I/O）和会话状态（如ON CPU）
             sql_id,
             current_obj#,
             wait_class,
             session_state,
             1 AS cnt  -- 实时ASH：每采样1次计数1
      FROM GV$ACTIVE_SESSION_HISTORY
      UNION ALL
      SELECT 
             TO_CHAR(TRUNC(SAMPLE_TIME, 'HH24'), 'yyyy-mm-dd hh24:')
             || LPAD(FLOOR(EXTRACT(MINUTE FROM SAMPLE_TIME)/:display_min) * :display_min, 2, '0')
             || '-'
             || LPAD(FLOOR(EXTRACT(MINUTE FROM SAMPLE_TIME)/:display_min) * :display_min + :display_min, 2, '0') AS time,
             NVL(event, session_state) AS event,
             sql_id,
             current_obj#,
             wait_class,
             session_state,
             10 AS cnt  -- 历史ASH：聚合数据，每采样1次计数10（Oracle默认聚合规则）
      FROM DBA_HIST_ACTIVE_SESS_HISTORY
    ) a
    -- 关联对象字典表：获取对象名称和类型（过滤系统对象，聚焦业务）
    LEFT JOIN DBA_OBJECTS do 
      ON a.current_obj# = do.object_id
         AND do.OWNER NOT IN ('SYS', 'SYSTEM', 'SYSAUX', 'OUTLN', 'DBSNMP', 'ORDS_METADATA')
    -- 统一筛选条件：时间范围+等待类/会话状态（合并UNION ALL两侧重复条件）
    WHERE SAMPLE_TIME >= SYSDATE - :begin_hours / 24
      AND SAMPLE_TIME <= SYSDATE - (:begin_hours - :interval_hours) / 24
      AND (:waitclass = 'ON CPU' AND a.session_state = :waitclass 
           OR :waitclass != 'ON CPU' AND a.wait_class = :waitclass)
  ) b
) c
-- 筛选各层级TOP2数据
WHERE erowtop <= 2 
  AND sqlrowtop <= 2 
  AND sqlobjrowtop <= 2
-- 最终排序：按时间降序、统计量降序，优先展示近期高负载数据
ORDER BY time DESC, erow DESC, sqlrow DESC, sqlobjrow DESC;
/

-- 恢复默认格式设置
set lines 80 pages 14 verify on