ASH로 최근 몇 분간의 상위 대기 이벤트와 상위 SQL 조회

명령어

-- Last 30 min: wait event breakdown (each sample ~1 s of active session time)
SELECT NVL(event, 'ON CPU') AS event, wait_class,
       COUNT(*) AS samples,
       ROUND(RATIO_TO_REPORT(COUNT(*)) OVER () * 100, 1) AS pct
FROM   v$active_session_history
WHERE  sample_time > SYSTIMESTAMP - INTERVAL '30' MINUTE
GROUP  BY NVL(event, 'ON CPU'), wait_class
ORDER  BY samples DESC
FETCH FIRST 10 ROWS ONLY;

-- Last 30 min: top SQL (with CPU / User I/O breakdown)
SELECT sql_id, sql_opname,
       COUNT(*) AS samples,
       SUM(CASE WHEN session_state = 'ON CPU' THEN 1 ELSE 0 END) AS cpu,
       SUM(CASE WHEN wait_class = 'User I/O' THEN 1 ELSE 0 END)  AS user_io,
       ROUND(RATIO_TO_REPORT(COUNT(*)) OVER () * 100, 1) AS pct
FROM   v$active_session_history
WHERE  sample_time > SYSTIMESTAMP - INTERVAL '30' MINUTE
  AND  sql_id IS NOT NULL
GROUP  BY sql_id, sql_opname
ORDER  BY samples DESC
FETCH FIRST 10 ROWS ONLY;

-- Older periods: DBA_HIST_ACTIVE_SESS_HISTORY (one sample every 10 s; COUNT(*)*10 = seconds)

-- Ready-made ASH report (last 30 min):
SET LINESIZE 8000 PAGESIZE 0 TRIMSPOOL ON HEADING OFF FEEDBACK OFF
SELECT output
FROM   TABLE(DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_TEXT(
         l_dbid     => (SELECT dbid FROM v$database),
         l_inst_num => (SELECT instance_number FROM v$instance),
         l_btime    => SYSDATE - 30/1440,
         l_etime    => SYSDATE));
-- Interactive: @?/rdbms/admin/ashrpt.sql

하는 일

Active Session History 샘플을 사용하여 최근 몇 분간 어떤 대기 이벤트와 어떤 SQL이 DB time을 소비했는지 백분율로 보여 준다. AWR 스냅샷을 기다리지 않고 장애를 진단한다.

사용 시점

실시간 성능 저하 중, AWR 평균에서 사라지는 짧은 스파이크, 특정 세션이나 SQL이 무엇을 기다렸는지 확인할 때.

주의

V$ACTIVE_SESSION_HISTORY와 DBA_HIST_ACTIVE_SESS_HISTORY 조회에는 Diagnostics Pack 라이선스가 필요하다. ASH는 샘플링 방식이다. 샘플 수는 시간을 추정할 뿐 정확한 측정값이 아니며, 매우 짧은 SQL은 나타나지 않을 수 있다. 메모리 내 ASH 버퍼는 바쁜 시스템에서 몇 시간 분량만 보관한다.