-- 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