直近数分間の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時間を消費したかを百分率で表示する。AWRスナップショットを待たずにインシデントを診断できる。

使用する場面

性能低下が進行中のとき、AWRの平均では消える短いスパイク、特定のセッションやSQLが何を待っていたかの確認に。

注意

V$ACTIVE_SESSION_HISTORYとDBA_HIST_ACTIVE_SESS_HISTORYのクエリにはDiagnostics Packライセンスが必要。ASHはサンプリングであり、サンプル数は時間の推定値で正確な測定値ではない。非常に短いSQLは表示されない場合がある。メモリ上のASHバッファは、高負荷システムでは数時間分しか保持しない。