【Oracle】AWR・ASH完全ガイド｜スナップショット管理・レポートの読み方・ASHで過去の問題を特定する方法まで解説

-- 取得間隔と保存期間を確認
SELECT
    snap_interval,            -- スナップショット取得間隔
    retention,                -- 保存期間
    topnsql                   -- AWR に保存するトップSQL件数
FROM DBA_HIST_WR_CONTROL;

-- スナップショットの一覧と取得時刻を確認
SELECT
    snap_id,
    instance_number,
    TO_CHAR(begin_interval_time, 'YYYY-MM-DD HH24:MI') AS begin_time,
    TO_CHAR(end_interval_time,   'YYYY-MM-DD HH24:MI') AS end_time
FROM DBA_HIST_SNAPSHOT
ORDER BY snap_id DESC
FETCH FIRST 10 ROWS ONLY;

-- 取得間隔を 30分、保存期間を 14日に変更
BEGIN
    DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
        retention => 14 * 24 * 60,   -- 保存期間（分）= 14日
        interval  => 30,             -- 取得間隔（分）= 30分
        topnsql   => 100             -- トップSQL保存件数（デフォルト30）
    );
END;
/

-- 即座にスナップショットを手動取得する
BEGIN
    DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;
END;
/
-- または
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;

-- 古いスナップショットを手動削除する
BEGIN
    DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(
        low_snap_id  => 100,  -- 削除開始スナップショットID
        high_snap_id => 150   -- 削除終了スナップショットID
    );
END;
/

-- SQL*Plus から対話式でレポートを生成（スナップショットIDを選択する）
@$ORACLE_HOME/rdbms/admin/awrrpt.sql

-- HTML 形式のレポートを直接生成する（snap_id を直接指定）
SELECT OUTPUT
FROM TABLE(
    DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
        l_dbid       => (SELECT dbid FROM V$DATABASE),
        l_inst_num   => 1,
        l_bid        => 200,   -- 開始スナップショットID（begin）
        l_eid        => 210    -- 終了スナップショットID（end）
    )
);

-- sql_id を指定して日次の平均実行時間を確認する
SELECT
    TO_CHAR(sn.begin_interval_time, 'YYYY-MM-DD HH24') AS hour,
    s.plan_hash_value,
    s.executions_delta                                  AS exec_count,
    ROUND(s.elapsed_time_delta
          / NULLIF(s.executions_delta, 0) / 1000000, 2) AS avg_elapsed_sec,
    ROUND(s.buffer_gets_delta
          / NULLIF(s.executions_delta, 0), 0)           AS avg_gets
FROM DBA_HIST_SQLSTAT s
JOIN DBA_HIST_SNAPSHOT sn
  ON s.snap_id = sn.snap_id AND s.instance_number = sn.instance_number
WHERE s.sql_id = 'abc123xyz'   -- 確認したい sql_id を指定
  AND sn.begin_interval_time >= SYSDATE - 7
ORDER BY sn.snap_id;

-- ある期間のトップ SQL（経過時間合計の Top 10）
SELECT
    s.sql_id,
    ROUND(SUM(s.elapsed_time_delta) / 1000000, 2)   AS total_elapsed_sec,
    SUM(s.executions_delta)                          AS total_execs,
    ROUND(SUM(s.elapsed_time_delta)
          / NULLIF(SUM(s.executions_delta), 0) / 1000000, 4) AS avg_elapsed_sec,
    MAX(SUBSTR(t.sql_text, 1, 60))                  AS sql_preview
FROM DBA_HIST_SQLSTAT s
JOIN DBA_HIST_SNAPSHOT sn
  ON s.snap_id = sn.snap_id AND s.instance_number = sn.instance_number
LEFT JOIN DBA_HIST_SQLTEXT t ON s.sql_id = t.sql_id
WHERE sn.begin_interval_time BETWEEN
      TO_DATE('2024-04-01', 'YYYY-MM-DD') AND
      TO_DATE('2024-04-02', 'YYYY-MM-DD')
  AND s.executions_delta > 0
GROUP BY s.sql_id
ORDER BY total_elapsed_sec DESC
FETCH FIRST 10 ROWS ONLY;

-- 指定した時刻帯（例: 昨夜2時台）に最も待機が多かったイベントを確認
SELECT
    event,
    wait_class,
    COUNT(*)                AS sample_count,
    ROUND(COUNT(*) * 100.0
        / SUM(COUNT(*)) OVER (), 1) AS pct
FROM DBA_HIST_ACTIVE_SESS_HISTORY
WHERE sample_time BETWEEN
      TO_TIMESTAMP('2024-04-01 02:00', 'YYYY-MM-DD HH24:MI') AND
      TO_TIMESTAMP('2024-04-01 03:00', 'YYYY-MM-DD HH24:MI')
  AND session_type = 'FOREGROUND'    -- ユーザーセッションのみ
  AND session_state = 'WAITING'      -- 待機中のサンプルのみ
GROUP BY event, wait_class
ORDER BY sample_count DESC;

-- 同時刻帯でどの SQL が最も多くサンプリングされたか（= 最も処理時間を使ったか）
SELECT
    ash.sql_id,
    COUNT(*)                    AS samples,
    MAX(SUBSTR(t.sql_text, 1, 70)) AS sql_preview
FROM DBA_HIST_ACTIVE_SESS_HISTORY ash
LEFT JOIN DBA_HIST_SQLTEXT t ON ash.sql_id = t.sql_id
WHERE ash.sample_time BETWEEN
      TO_TIMESTAMP('2024-04-01 02:00', 'YYYY-MM-DD HH24:MI') AND
      TO_TIMESTAMP('2024-04-01 03:00', 'YYYY-MM-DD HH24:MI')
  AND ash.session_type = 'FOREGROUND'
GROUP BY ash.sql_id
ORDER BY samples DESC
FETCH FIRST 10 ROWS ONLY;

-- 直近 10 分間のトップ待機（インメモリの ASH）
SELECT
    event,
    COUNT(*)  AS samples
FROM V$ACTIVE_SESSION_HISTORY
WHERE sample_time >= SYSDATE - 10/1440   -- 過去10分
  AND session_type = 'FOREGROUND'
  AND session_state = 'WAITING'
GROUP BY event
ORDER BY samples DESC
FETCH FIRST 10 ROWS ONLY;

-- 直近 10 分でアクティブだったユーザー・SQL を確認
SELECT
    ash.user_id,
    u.username,
    ash.sql_id,
    COUNT(*) AS samples
FROM V$ACTIVE_SESSION_HISTORY ash
JOIN DBA_USERS u ON ash.user_id = u.user_id
WHERE ash.sample_time >= SYSDATE - 10/1440
  AND ash.session_type = 'FOREGROUND'
GROUP BY ash.user_id, u.username, ash.sql_id
ORDER BY samples DESC;

-- 最近の ADDM タスクの一覧
SELECT
    task_name,
    description,
    created,
    status
FROM DBA_ADVISOR_TASKS
WHERE advisor_name = 'ADDM'
ORDER BY created DESC
FETCH FIRST 5 ROWS ONLY;

-- 特定の ADDM タスクの診断結果（推奨事項）を確認
SELECT
    f.type,
    f.impact,           -- ベネフィット推定（DB Time の削減率 %）
    f.message
FROM DBA_ADVISOR_FINDINGS f
WHERE f.task_name = 'ADDM:12345678'  -- DBA_ADVISOR_TASKS の task_name を指定
ORDER BY f.impact DESC;

-- 特定の ADDM 推奨事項（アクション）を確認
SELECT
    r.rank,
    r.benefit,
    r.message
FROM DBA_ADVISOR_RECOMMENDATIONS r
WHERE r.task_name = 'ADDM:12345678'
ORDER BY r.benefit DESC;

-- 障害発生時刻（例: 2024-04-01 02:30）を挟むスナップショットを特定
SELECT
    snap_id,
    TO_CHAR(begin_interval_time, 'YYYY-MM-DD HH24:MI') AS begin_time,
    TO_CHAR(end_interval_time,   'YYYY-MM-DD HH24:MI') AS end_time
FROM DBA_HIST_SNAPSHOT
WHERE begin_interval_time BETWEEN
      TO_DATE('2024-04-01 02:00', 'YYYY-MM-DD HH24:MI') AND
      TO_DATE('2024-04-01 04:00', 'YYYY-MM-DD HH24:MI')
ORDER BY snap_id;
-- → 得られたスナップショットIDを使って AWR レポートを生成

-- SYSAUX 内の AWR コンポーネントの使用量を確認
SELECT
    occupant_name,
    ROUND(space_usage_kbytes / 1024, 1) AS usage_mb
FROM V$SYSAUX_OCCUPANTS
WHERE occupant_name LIKE 'SM/%'   -- SM/AWR, SM/ASH など
ORDER BY space_usage_kbytes DESC;