【Oracle】SQL Tuning Advisor完全ガイド｜DBMS_SQLTUNE・SQL チューニングセット・SQL プロファイル・推奨事項の確認まで解説

-- 実行時間が長い SQL の sql_id を特定する
SELECT sql_id, plan_hash_value,
       ROUND(elapsed_time / executions / 1000) AS avg_elapsed_ms,
       executions,
       SUBSTR(sql_text, 1, 80) AS sql_text_head
FROM V$SQLAREA
WHERE executions > 0
  AND elapsed_time / executions > 1000000   -- 平均 1 秒超の SQL
ORDER BY elapsed_time / executions DESC
FETCH FIRST 10 ROWS ONLY;

DECLARE
    v_task_name VARCHAR2(30) := 'TUNING_TASK_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS');
    v_report    CLOB;
BEGIN
    -- ① チューニングタスクを作成する
    v_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
        sql_id      => 'abc123def456g',   -- V$SQLAREA で確認した sql_id
        plan_hash_value => NULL,            -- NULL = すべてのプランを対象
        scope       => 'COMPREHENSIVE',   -- COMPREHENSIVE（完全解析）/ LIMITED（軽量）
        time_limit  => 60,                  -- 解析の最大実行時間（秒）
        task_name   => v_task_name,
        description => '遅いクエリの解析'
    );
    DBMS_OUTPUT.PUT_LINE('タスク名: ' || v_task_name);

    -- ② タスクを実行する
    DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => v_task_name);

    -- ③ レポートを取得する
    v_report := DBMS_SQLTUNE.REPORT_TUNING_TASK(
        task_name    => v_task_name,
        type         => 'TEXT',   -- TEXT / HTML / XML
        level        => 'TYPICAL' -- TYPICAL / BASIC / ALL（詳細度）
    );
    DBMS_OUTPUT.PUT_LINE(SUBSTR(v_report, 1, 32767));
END;
/

-- 実行済みタスクの一覧を確認する
SELECT task_name, status, created, last_modified, description
FROM DBA_ADVISOR_TASKS
WHERE advisor_name = 'SQL Tuning Advisor'
ORDER BY created DESC;

-- 推奨事項を確認する（DBA_ADVISOR_FINDINGS / DBA_ADVISOR_RECOMMENDATIONS）
SELECT
    f.task_name,
    f.finding_id,
    f.type,             -- STATISTICS（統計）/ SQL PROFILE / INDEX / RESTRUCTURE SQL など
    f.message,          -- 推奨事項の内容
    r.benefit,          -- 推定改善率（%）
    r.rec_id
FROM DBA_ADVISOR_FINDINGS f
JOIN DBA_ADVISOR_RECOMMENDATIONS r
    ON f.task_id = r.task_id AND f.finding_id = r.finding_id
WHERE f.task_name = 'YOUR_TASK_NAME'
ORDER BY r.benefit DESC;

-- 不要になったタスクを削除する
BEGIN
    DBMS_SQLTUNE.DROP_TUNING_TASK(task_name => 'YOUR_TASK_NAME');
END;
/

-- レポートで推奨された SQL プロファイルを適用する
DECLARE
    v_profile_name VARCHAR2(50);
BEGIN
    v_profile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(
        task_name    => 'YOUR_TASK_NAME',
        task_owner   => 'SYS',             -- タスクの所有者
        name         => 'PROFILE_SLOW_Q1', -- プロファイル名（省略可）
        force_match  => FALSE,              -- TRUE = バインド変数の値に関係なく適用
        replace      => TRUE               -- TRUE = 同名プロファイルを上書き
    );
    DBMS_OUTPUT.PUT_LINE('適用されたプロファイル名: ' || v_profile_name);
END;
/

-- 適用されたプロファイルを確認する
SELECT name, category, status, sql_text
FROM DBA_SQL_PROFILES
ORDER BY last_modified DESC;

-- プロファイルを無効化する（取り消しではなく一時停止）
BEGIN
    DBMS_SQLTUNE.ALTER_SQL_PROFILE(
        name          => 'PROFILE_SLOW_Q1',
        attribute_name => 'STATUS',
        value          => 'DISABLED'   -- 再有効化は 'ENABLED'
    );
END;
/

-- プロファイルを削除する
BEGIN
    DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'PROFILE_SLOW_Q1');
END;
/

-- ① STS を作成する
BEGIN
    DBMS_SQLTUNE.CREATE_SQLSET(
        sqlset_name => 'STS_SLOW_SQLS',
        description => '過去24時間の遅い SQL 集合'
    );
END;
/

-- ② AWR から STS に SQL を追加する（過去 24 時間・実行時間 1 秒超）
DECLARE
    v_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
    -- AWR の 2 スナップショット間の SQL を取得するカーソルを作成する
    OPEN v_cursor FOR
        SELECT VALUE(p) FROM TABLE(
            DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
                begin_snap  => (SELECT MAX(snap_id) - 24 FROM DBA_HIST_SNAPSHOT),  -- 24 スナップ前
                end_snap    => (SELECT MAX(snap_id) FROM DBA_HIST_SNAPSHOT),        -- 最新スナップ
                basic_filter => 'elapsed_time > 1000000',  -- 1 秒超
                ranking_measure1 => 'elapsed_time',
                result_limit => 50                            -- 上位 50 SQL
            )
        ) p;

    -- STS に追加する
    DBMS_SQLTUNE.LOAD_SQLSET(
        sqlset_name     => 'STS_SLOW_SQLS',
        populate_cursor => v_cursor
    );
    CLOSE v_cursor;
END;
/

-- ③ STS 全体にチューニングタスクをかける
DECLARE
    v_task VARCHAR2(50);
BEGIN
    v_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(
        sqlset_name => 'STS_SLOW_SQLS',
        scope       => 'COMPREHENSIVE',
        time_limit  => 300,
        task_name   => 'STS_TUNING_TASK'
    );
    DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'STS_TUNING_TASK');
END;
/

-- ④ レポートを出力する
SET LONG 100000
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('STS_TUNING_TASK') FROM DUAL;

-- ⑤ STS を削除する（不要になったら）
BEGIN
    DBMS_SQLTUNE.DROP_SQLSET(sqlset_name => 'STS_SLOW_SQLS');
END;
/