liuziyi

-- File Name : ash_object_by_waitclass_19c_optimized.sql
-- Purpose : 根據(jù)WAITCLASS值，按EVENT,SQL_ID,CURRENT_OBJ排序，顯示TOP 2的信息（兼容10g/11g/12c/19C）
-- 支持版本 : Oracle 10g, 11g, 12c, 19C (長期支持版本優(yōu)化)
set echo off
set lines 400 pages 1000 heading on verify off
col time               for a28                  heading '時(shí)間區(qū)間(yyyy-mm-dd hh24:mi-mi)'
col event              for a50                  heading '等待事件/會(huì)話狀態(tài)'
col erow               for 999999999            heading '事件行數(shù)'
col erowpercent        for 99.99                heading '事件占比(%)'
col sql_id             for a18                  heading 'SQL ID'
col sqlrow             for 99999999             heading '事件-SQL行數(shù)'
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-對象行數(shù)'
col sqlobjrowpercent   for 99.99                heading '對象占比(%)'

-- 輸入?yún)?shù)（保留原默認(rèn)值，兼容歷史使用習(xí)慣）
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;  -- 動(dòng)態(tài)時(shí)間分段粒度
  :waitclass      := '&waitclass';
end;
/

-- 分組中斷設(shè)置，優(yōu)化輸出格式
break on time on event on erow on erowpercent on sql_id on sqlrow on sqlrowpercent

/* 核心查詢邏輯：分層統(tǒng)計(jì)TOP2數(shù)據(jù)，關(guān)聯(lián)對象詳情，兼容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,
         -- 分層排序：時(shí)間維度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,
           -- 事件級(jí)別統(tǒng)計(jì)：該時(shí)間區(qū)間內(nèi)事件總采樣數(shù)及占比
           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級(jí)別統(tǒng)計(jì)：該事件下SQL的采樣數(shù)及占比
           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,
           -- 關(guān)聯(lián)對象名稱（左連接避免數(shù)據(jù)丟失，處理空值）
           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-對象級(jí)別統(tǒng)計(jì)：該SQL下對象的采樣數(shù)及占比
           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 (
      -- 基礎(chǔ)數(shù)據(jù)提取：合并實(shí)時(shí)ASH（GV$）與歷史ASH（DBA_HIST）數(shù)據(jù)
      SELECT 
             -- 動(dòng)態(tài)時(shí)間分段：根據(jù)輸入的分鐘數(shù)生成區(qū)間（如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）和會(huì)話狀態(tài)（如ON CPU）
             sql_id,
             current_obj#,
             wait_class,
             session_state,
             1 AS cnt  -- 實(shí)時(shí)ASH：每采樣1次計(jì)數(shù)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：聚合數(shù)據(jù)，每采樣1次計(jì)數(shù)10（Oracle默認(rèn)聚合規(guī)則）
      FROM DBA_HIST_ACTIVE_SESS_HISTORY
    ) a
    -- 關(guān)聯(lián)對象字典表：獲取對象名稱和類型（過濾系統(tǒng)對象，聚焦業(yè)務(wù)）
    LEFT JOIN DBA_OBJECTS do 
      ON a.current_obj# = do.object_id
         AND do.OWNER NOT IN ('SYS', 'SYSTEM', 'SYSAUX', 'OUTLN', 'DBSNMP', 'ORDS_METADATA')
    -- 統(tǒng)一篩選條件：時(shí)間范圍+等待類/會(huì)話狀態(tài)（合并UNION ALL兩側(cè)重復(fù)條件）
    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
-- 篩選各層級(jí)TOP2數(shù)據(jù)
WHERE erowtop <= 2 
  AND sqlrowtop <= 2 
  AND sqlobjrowtop <= 2
-- 最終排序：按時(shí)間降序、統(tǒng)計(jì)量降序，優(yōu)先展示近期高負(fù)載數(shù)據(jù)
ORDER BY time DESC, erow DESC, sqlrow DESC, sqlobjrow DESC;
/

-- 恢復(fù)默認(rèn)格式設(shè)置
set lines 80 pages 14 verify on