【Oracle】DBMS_STATSによる統計情報の手動収集・管理完全ガイド｜GATHER_TABLE_STATS・ロック・エクスポート・ヒストグラムまで解説

BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(
        ownname          => 'HR',              -- スキーマ名（NULLで現在のスキーマ）
        tabname          => 'EMPLOYEES',       -- テーブル名
        estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,  -- サンプリング率（自動推奨）
        method_opt       => 'FOR ALL COLUMNS SIZE AUTO',  -- ヒストグラム制御
        cascade          => TRUE,              -- インデックス統計も同時に収集
        degree           => 4,                 -- 並列度（NULLでデフォルト）
        no_invalidate    => FALSE              -- 依存カーソルを即座に無効化
    );
END;
/

BEGIN
    DBMS_STATS.GATHER_SCHEMA_STATS(
        ownname          => 'HR',
        estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
        cascade          => TRUE,
        degree           => 4
    );
END;
/
-- HR スキーマ内の全テーブル・インデックスの統計を一括収集

BEGIN
    DBMS_STATS.GATHER_DATABASE_STATS(
        estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
        cascade          => TRUE,
        options          => 'GATHER STALE'  -- stale な統計のみ更新（全テーブルではない）
    );
END;
/

SELECT
    table_name,
    num_rows,              -- 推定行数
    blocks,                -- 使用ブロック数
    avg_row_len,           -- 平均行長（バイト）
    sample_size,           -- サンプリングした行数
    last_analyzed,         -- 最終収集日時
    stale_stats            -- YES=古い統計, NO=最新
FROM dba_tab_statistics
WHERE owner = 'HR'
ORDER BY last_analyzed DESC NULLS LAST;

SELECT
    column_name,
    num_distinct,          -- カーディナリティ（一意値の数）
    num_nulls,             -- NULL値の数
    density,               -- 密度（1/num_distinct に近い値）
    low_value,             -- 最小値（RAW形式）
    high_value,            -- 最大値（RAW形式）
    histogram,             -- ヒストグラムの種類（NONE/FREQUENCY/HEIGHT BALANCED/HYBRID/TOP-FREQUENCY）
    num_buckets,           -- ヒストグラムのバケット数
    last_analyzed
FROM dba_tab_col_statistics
WHERE owner = 'HR'
  AND table_name = 'EMPLOYEES'
ORDER BY column_name;

SELECT
    index_name,
    blevel,                -- B*Tree のレベル数
    leaf_blocks,           -- リーフブロック数
    distinct_keys,         -- 一意キーの数
    avg_leaf_blocks_per_key,
    avg_data_blocks_per_key,
    clustering_factor,     -- クラスタリングファクタ（小さいほどインデックススキャンに有利）
    last_analyzed,
    stale_stats
FROM dba_ind_statistics
WHERE owner = 'HR'
  AND table_name = 'EMPLOYEES'
ORDER BY index_name;

-- stale_stats = YES のテーブルが統計更新の対象
SELECT
    owner,
    table_name,
    num_rows,
    last_analyzed,
    stale_stats
FROM dba_tab_statistics
WHERE stale_stats = 'YES'
  AND owner = 'HR'
ORDER BY table_name;

-- DBA_TAB_MODIFICATIONS で最終統計収集以降の変更量を確認
SELECT
    table_name,
    inserts,
    updates,
    deletes,
    timestamp AS last_flush_time
FROM dba_tab_modifications
WHERE table_owner = 'HR'
ORDER BY (inserts + updates + deletes) DESC;

BEGIN
    DBMS_STATS.SET_TABLE_PREFS(
        ownname => 'HR',
        tabname => 'EMPLOYEES',
        pname   => 'STALE_PERCENT',
        pvalue  => '5'   -- デフォルト10%を5%に変更
    );
END;
/

-- 統計をロックする（自動・手動問わず更新されなくなる）
BEGIN
    DBMS_STATS.LOCK_TABLE_STATS('HR', 'EMPLOYEES');
END;
/

-- ロック状態を確認する
SELECT table_name, stattype_locked
FROM dba_tab_statistics
WHERE owner = 'HR'
  AND table_name = 'EMPLOYEES';
-- stattype_locked が 'ALL' ならロック済み、NULL なら未ロック

-- アンロックして統計の更新を再開可能にする
BEGIN
    DBMS_STATS.UNLOCK_TABLE_STATS('HR', 'EMPLOYEES');
END;
/

-- スキーマ内の全テーブルの統計をロックする
BEGIN
    DBMS_STATS.LOCK_SCHEMA_STATS('HR');
END;
/

-- スキーマ内の全テーブルの統計をアンロックする
BEGIN
    DBMS_STATS.UNLOCK_SCHEMA_STATS('HR');
END;
/

-- DBMS_STATS が使用する統計保存テーブルを作成する
BEGIN
    DBMS_STATS.CREATE_STAT_TABLE(
        ownname  => 'HR',
        stattab  => 'STATS_BACKUP'    -- 任意のテーブル名
    );
END;
/

BEGIN
    DBMS_STATS.EXPORT_TABLE_STATS(
        ownname  => 'HR',
        tabname  => 'EMPLOYEES',
        stattab  => 'STATS_BACKUP',
        statid   => 'BEFORE_20260329'   -- 識別用のID（任意の文字列）
    );
END;
/
-- スキーマ単位のエクスポートも可能
-- DBMS_STATS.EXPORT_SCHEMA_STATS('HR', 'STATS_BACKUP', 'BEFORE_20260329');

-- 保存しておいた統計をテーブルに戻す
BEGIN
    DBMS_STATS.IMPORT_TABLE_STATS(
        ownname  => 'HR',
        tabname  => 'EMPLOYEES',
        stattab  => 'STATS_BACKUP',
        statid   => 'BEFORE_20260329',
        no_invalidate => FALSE       -- カーソルを即座に無効化して新しい統計を反映
    );
END;
/

-- 全列に自動判断でヒストグラムを作成（デフォルト）
BEGIN
    DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES',
        method_opt => 'FOR ALL COLUMNS SIZE AUTO');
END;
/

-- 全列でヒストグラムなし（均一分布を前提にする）
BEGIN
    DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES',
        method_opt => 'FOR ALL COLUMNS SIZE 1');
END;
/

-- 特定の列だけヒストグラムを作成する
BEGIN
    DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES',
        method_opt => 'FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE 254 department_id job_id');
END;
/
-- department_id と job_id にだけ最大254バケットのヒストグラムを作成

-- テーブル統計を削除する（オプティマイザは動的サンプリングにフォールバック）
BEGIN
    DBMS_STATS.DELETE_TABLE_STATS('HR', 'EMPLOYEES');
END;
/

-- スキーマ単位で削除
BEGIN
    DBMS_STATS.DELETE_SCHEMA_STATS('HR');
END;
/

-- 列統計だけ削除
BEGIN
    DBMS_STATS.DELETE_COLUMN_STATS('HR', 'EMPLOYEES', 'SALARY');
END;
/

-- ステップ 1: 現在の統計をバックアップする
BEGIN
    DBMS_STATS.EXPORT_TABLE_STATS('HR', 'EMPLOYEES', 'STATS_BACKUP', 'PRE_LOAD');
END;
/

-- ステップ 2: データロード実行
-- （INSERT, SQL*Loader, Data Pump 等）

-- ステップ 3: 統計を再収集する
BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(
        ownname          => 'HR',
        tabname          => 'EMPLOYEES',
        estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
        method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
        cascade          => TRUE,
        degree           => 4,
        no_invalidate    => FALSE
    );
END;
/

-- ステップ 4: 収集結果を確認する
SELECT table_name, num_rows, blocks, last_analyzed, stale_stats
FROM dba_tab_statistics
WHERE owner = 'HR' AND table_name = 'EMPLOYEES';

-- ステップ 5: （万一実行計画が劣化した場合）統計を元に戻す
-- BEGIN
--     DBMS_STATS.IMPORT_TABLE_STATS('HR', 'EMPLOYEES', 'STATS_BACKUP', 'PRE_LOAD');
-- END;
-- /