【Oracle】ヒストグラム完全ガイド｜オプティマイザ統計のヒストグラム種類・偏りデータへの効果・管理方法まで解説

-- 列の統計とヒストグラムの有無を確認する
SELECT
    column_name,
    num_distinct      AS ndv,              -- 異なる値の数
    num_nulls,                             -- NULL の数
    density,                              -- オプティマイザが使う密度（1/NDV に近い値）
    histogram,                            -- NONE / FREQUENCY / HEIGHT BALANCED / TOP-FREQUENCY / HYBRID
    num_buckets,                          -- ヒストグラムのバケット数
    last_analyzed
FROM USER_TAB_COL_STATISTICS
WHERE table_name = 'ORDERS'
ORDER BY column_name;

-- ヒストグラムの詳細（各バケットのエンドポイント値と頻度）
SELECT
    endpoint_number,     -- バケット番号（Frequency の場合は累積頻度）
    endpoint_value,      -- バケットの上限値（数値の場合）
    endpoint_actual_value,  -- 実際の値（文字列の場合）
    endpoint_repeat_count   -- Hybrid: このエンドポイントが何度繰り返されるか
FROM USER_HISTOGRAMS
WHERE table_name  = 'ORDERS'
  AND column_name = 'STATUS'
ORDER BY endpoint_number;

-- Frequency ヒストグラムでは endpoint_number が累積頻度を表す
-- 例: endpoint_number=100, endpoint_value=1 → 値=1 が 100行ある
--     endpoint_number=150, endpoint_value=2 → 値=2 が 50行ある（150-100）
--     endpoint_number=300, endpoint_value=3 → 値=3 が 150行ある（300-150）

-- デフォルト: Oracle が自動で判断（偏りのある列に自動収集）
-- METHOD_OPT='FOR ALL COLUMNS SIZE AUTO' がデフォルト
EXEC DBMS_STATS.GATHER_TABLE_STATS(
    ownname     => 'HR',
    tabname     => 'ORDERS',
    method_opt  => 'FOR ALL COLUMNS SIZE AUTO'   -- Oracle が自動でヒストグラムを収集
);

-- 特定列に指定バケット数でヒストグラムを収集する
EXEC DBMS_STATS.GATHER_TABLE_STATS(
    ownname     => 'HR',
    tabname     => 'ORDERS',
    method_opt  => 'FOR COLUMNS status SIZE 10'  -- status 列に 10 バケットのヒストグラム
);

-- 複数列を指定する
EXEC DBMS_STATS.GATHER_TABLE_STATS(
    ownname    => 'HR',
    tabname    => 'ORDERS',
    method_opt => 'FOR COLUMNS status SIZE 50 region SIZE 20'
);

-- ヒストグラムを収集しない（偏りがないと分かっている列）
EXEC DBMS_STATS.GATHER_TABLE_STATS(
    ownname    => 'HR',
    tabname    => 'ORDERS',
    method_opt => 'FOR COLUMNS order_id SIZE 1'  -- SIZE 1 = ヒストグラムなし
);

-- すべての列のヒストグラムをなくす
EXEC DBMS_STATS.GATHER_TABLE_STATS(
    ownname    => 'HR',
    tabname    => 'ORDERS',
    method_opt => 'FOR ALL COLUMNS SIZE 1'   -- 全列のヒストグラムを削除
);

-- SIZE AUTO: Oracle が列の使用状況（V$SQL_BIND_CAPTURE）と NDV から自動判断
-- SIZE SKEWONLY: データの偏りを検出した列にのみヒストグラムを収集
-- SIZE REPEAT: 既にヒストグラムがある列のみ更新する（新規収集はしない）

-- バインド変数を使ったクエリで実行計画が安定しない場合の調査

-- ① 問題が起きているクエリの実行計画を確認する
SELECT sql_id, child_number, executions, rows_processed,
       is_bind_sensitive,   -- YES: ヒストグラムがあり値によって最適計画が変わる可能性がある
       is_bind_aware,       -- YES: ACS（適応カーソル共有）が有効（12c 以降）
       plan_hash_value
FROM V$SQL
WHERE sql_text LIKE '%SELECT%orders%WHERE%status%'
ORDER BY executions DESC;

-- ② DBMS_XPLAN で実行計画の詳細を確認する
SELECT * FROM TABLE(
    DBMS_XPLAN.DISPLAY_CURSOR(sql_id => 'xxxx', cursor_child_no => 0, format => 'ALLSTATS LAST')
);

-- ③ ヒストグラムによる推定行数を確認する
-- 推定行数（E-Rows）と実際の行数（A-Rows）が大きく乖離している場合はヒストグラムの問題

-- 対処法:
-- ① ヒストグラムを再収集する（最新のデータ分布を反映する）
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'ORDERS', method_opt => 'FOR COLUMNS status SIZE AUTO');

-- ② バインドピーキングの影響を減らすためにリテラル値を使う（バインド変数を使わない）
-- ③ CARDINALITY ヒントで推定行数を手動指定する（最終手段）
SELECT /*+ CARDINALITY(o, 1000) */ * FROM orders o WHERE status = :1;

-- ④ BIND_AWARE ヒントで ACS を明示的に有効にする（12c 以降）
SELECT /*+ BIND_AWARE */ * FROM orders WHERE status = :1;