【Oracle】適応型クエリ最適化（Adaptive Query Optimization）完全ガイド｜適応プラン・統計フィードバック・動的サンプリングまで解説

-- 適応型クエリ最適化関連パラメータをまとめて確認する
SELECT name, value, description
FROM V$PARAMETER
WHERE name LIKE 'optimizer_adaptive%'
   OR name LIKE 'optimizer_dynamic%'
ORDER BY name;

-- 主要パラメータのデフォルト値（Oracle 12.2 以降）
-- optimizer_adaptive_plans       : TRUE（適応プランを有効）
-- optimizer_adaptive_statistics  : FALSE（統計フィードバック・動的統計は無効）
-- optimizer_dynamic_sampling     : 2（最小限のサンプリング）

-- GATHER_PLAN_STATISTICS ヒントを使って実測値を収集する
SELECT /*+ GATHER_PLAN_STATISTICS */
    d.department_name,
    COUNT(e.employee_id) AS emp_count
FROM departments d
JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name
ORDER BY emp_count DESC;

-- 実行後に ALLSTATS LAST で適応プランを確認する
SELECT * FROM TABLE(
    DBMS_XPLAN.DISPLAY_CURSOR(
        sql_id      => NULL,   -- NULL = 直前の SQL
        cursor_child_no => 0,
        format      => 'ALLSTATS LAST +ADAPTIVE'   -- +ADAPTIVE で適応情報を表示
    )
);
-- Note 欄に以下が表示される:
--   - this is an adaptive plan（適応プランが使用された）
--   - final plan was この plan（実際に使われた結合方法）
-- 実行計画内で "-" 行が非アクティブな分岐（使われなかった結合方法）を示す

-- 統計フィードバックを有効にする（セッションレベル）
ALTER SESSION SET optimizer_adaptive_statistics = TRUE;

-- 統計フィードバックが適用されたかどうかを V$SQL で確認する
SELECT sql_id, executions, is_reoptimizable,
       SUBSTR(sql_text, 1, 80) AS sql_head
FROM V$SQL
WHERE is_reoptimizable = 'Y'   -- 再最適化が必要と判断された SQL
ORDER BY executions DESC;
-- is_reoptimizable = Y → 次回実行時に統計フィードバックを使って再計画される

-- 実行計画の Note 欄で統計フィードバックを確認する
-- "statistics feedback used for this statement" と表示される

-- 現在の動的サンプリングレベルを確認する
SHOW PARAMETER optimizer_dynamic_sampling;

-- レベルの説明:
-- 0: 動的サンプリングなし
-- 1: 統計情報がないテーブルのみ（最小限）
-- 2: デフォルト（統計がないか古い場合に基本サンプリング）
-- 4: より積極的なサンプリング
-- 11: 自動（Oracle 12.2以降 - OPTIMIZER_ADAPTIVE_STATISTICS=TRUE の場合に有効）

-- セッションレベルで変更する（本番への影響を避けるため）
ALTER SESSION SET optimizer_dynamic_sampling = 4;

-- 特定の SQL だけ動的サンプリングを指定するヒント
SELECT /*+ DYNAMIC_SAMPLING(e 4) */ employee_id, salary
FROM employees e
WHERE salary > 10000;

-- 実行計画の Note 欄を確認する
EXPLAIN PLAN FOR
SELECT COUNT(*) FROM employees WHERE hire_date < DATE '2000-01-01';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'TYPICAL'));
-- Note 欄に "dynamic statistics used: dynamic sampling (level=N)" と表示されれば
-- 動的サンプリングが使用されている

-- 適応プランのみ無効化する
ALTER SESSION SET optimizer_adaptive_plans = FALSE;

-- 統計フィードバック・動的統計も含めてすべて無効化する
ALTER SESSION SET optimizer_adaptive_statistics = FALSE;

-- 特定 SQL のみ適応プランを無効化するヒント
SELECT /*+ NO_ADAPTIVE_PLAN */ *
FROM employees e JOIN departments d ON e.department_id = d.department_id;

-- システム全体で適応プランを無効化する（本番適用前に十分検証すること）
ALTER SYSTEM SET optimizer_adaptive_plans = FALSE SCOPE=BOTH;

-- 無効化の影響を受ける固定プランを確認する
-- V$SQL で is_resolved_adaptive_plan 列を確認する
SELECT sql_id, is_resolved_adaptive_plan, SUBSTR(sql_text, 1, 80)
FROM V$SQL
WHERE is_resolved_adaptive_plan IS NOT NULL;