【Oracle】自動統計収集のスケジュールと制御方法｜メンテナンスウィンドウ・統計ロック・手動収集・夜間バッチ対策まで解説

-- 自動タスクの状態を確認
SELECT client_name, status
FROM dba_autotask_client
WHERE client_name = 'auto optimizer stats collection';
-- status: ENABLED / DISABLED

-- メンテナンスウィンドウの一覧
SELECT window_name, repeat_interval, duration, enabled
FROM dba_scheduler_windows
WHERE window_name LIKE '%WINDOW%'
ORDER BY window_name;

-- 実行履歴（直近 7 日）
SELECT window_name, window_start_time, window_duration, window_end_time
FROM dba_autotask_window_history
WHERE window_start_time >= SYSDATE - 7
ORDER BY window_start_time DESC;

-- 月曜のウィンドウを 1:00 開始に変更（夜間バッチの後に実行）
BEGIN
    DBMS_SCHEDULER.SET_ATTRIBUTE(
        name      => 'MONDAY_WINDOW',
        attribute => 'repeat_interval',
        value     => 'FREQ=WEEKLY; BYDAY=MON; BYHOUR=1; BYMINUTE=0; BYSECOND=0'
    );
END;
/

-- 月曜のウィンドウを 2 時間に短縮
BEGIN
    DBMS_SCHEDULER.SET_ATTRIBUTE(
        name      => 'MONDAY_WINDOW',
        attribute => 'duration',
        value     => INTERVAL '2' HOUR
    );
END;
/

-- 自動統計収集を無効化
BEGIN
    DBMS_AUTO_TASK_ADMIN.DISABLE(
        client_name => 'auto optimizer stats collection',
        operation   => NULL,
        window_name => NULL
    );
END;
/

-- 再度有効化する場合
BEGIN
    DBMS_AUTO_TASK_ADMIN.ENABLE(
        client_name => 'auto optimizer stats collection',
        operation   => NULL,
        window_name => NULL
    );
END;
/

-- 月曜のウィンドウだけ無効化
BEGIN
    DBMS_SCHEDULER.DISABLE('MONDAY_WINDOW');
END;
/

-- 再度有効化
BEGIN
    DBMS_SCHEDULER.ENABLE('MONDAY_WINDOW');
END;
/

-- 特定テーブルの統計をロック（自動収集対象から除外）
EXEC DBMS_STATS.LOCK_TABLE_STATS('HR', 'EMPLOYEES');

-- スキーマ全体の統計をロック
EXEC DBMS_STATS.LOCK_SCHEMA_STATS('HR');

-- ロックを解除
EXEC DBMS_STATS.UNLOCK_TABLE_STATS('HR', 'EMPLOYEES');
EXEC DBMS_STATS.UNLOCK_SCHEMA_STATS('HR');

-- ロック状態の確認
SELECT table_name, stattype_locked FROM dba_tab_statistics
WHERE owner = 'HR' AND stattype_locked IS NOT NULL;

-- STALE 統計のテーブルを検出
SELECT owner, table_name, last_analyzed, stale_stats, num_rows
FROM dba_tab_statistics
WHERE owner NOT IN ('SYS','SYSTEM','AUDSYS','XDB')
  AND stale_stats = 'YES'
ORDER BY num_rows DESC;

-- stale_stats = YES: 最後の統計収集以降に 10% 以上のデータ変更
-- → 手動で GATHER すべき候補

-- 30 日以上統計が更新されていないテーブル
SELECT owner, table_name, last_analyzed, num_rows
FROM dba_tables
WHERE owner = 'HR'
  AND (last_analyzed IS NULL OR last_analyzed < SYSDATE - 30)
ORDER BY last_analyzed;

-- テーブル単位
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');

-- スキーマ全体
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('HR');

-- STALE のテーブルだけ収集
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('HR', OPTIONS => 'GATHER STALE');

-- 空のテーブルだけ収集
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('HR', OPTIONS => 'GATHER EMPTY');

-- データベース全体の STALE テーブルを収集
EXEC DBMS_STATS.GATHER_DATABASE_STATS(OPTIONS => 'GATHER STALE');

-- 並列度 4、サンプリング率は自動、カスケードあり
BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(
        ownname          => 'HR',
        tabname          => 'ORDERS',
        estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
        method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
        degree           => 4,
        cascade          => TRUE
    );
END;
/

-- CONCURRENT: 複数テーブルの統計を同時に収集（12c+）
EXEC DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT', 'TRUE');

-- 確認
SELECT DBMS_STATS.GET_PREFS('CONCURRENT') FROM DUAL;

-- CONCURRENT + GATHER STALE でスキーマ全体を高速収集
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('HR', OPTIONS => 'GATHER STALE');
-- 複数テーブルが同時に収集される

#!/bin/bash
# nightly_batch.sh

# (1) バッチ投入前: 統計をロック
sqlplus hr/password <<EOF
EXEC DBMS_STATS.LOCK_TABLE_STATS('HR', 'ORDERS');
EOF

# (2) バッチ投入
sqlldr hr/password control=orders.ctl ...

# (3) バッチ完了後: アンロック + 統計収集
sqlplus hr/password <<EOF
EXEC DBMS_STATS.UNLOCK_TABLE_STATS('HR', 'ORDERS');
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'ORDERS', DEGREE => 4);
EOF

-- PENDING モードを有効化
EXEC DBMS_STATS.SET_TABLE_PREFS('HR', 'ORDERS', 'PUBLISH', 'FALSE');

-- 統計を収集（まだ反映されない）
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'ORDERS');

-- PENDING 統計を使って実行計画をテスト
ALTER SESSION SET OPTIMIZER_USE_PENDING_STATISTICS = TRUE;
-- テスト SQL を実行して計画を確認

-- 問題なければ PENDING 統計を公開（反映）
EXEC DBMS_STATS.PUBLISH_PENDING_STATS('HR', 'ORDERS');

-- 問題があればPENDING 統計を削除（元に戻す）
EXEC DBMS_STATS.DELETE_PENDING_STATS('HR', 'ORDERS');

-- PUBLISH モードを元に戻す
EXEC DBMS_STATS.SET_TABLE_PREFS('HR', 'ORDERS', 'PUBLISH', 'TRUE');

-- 統計変更前の状態に戻す（デフォルトで 31 日分保持）
BEGIN
    DBMS_STATS.RESTORE_TABLE_STATS(
        ownname    => 'HR',
        tabname    => 'ORDERS',
        as_of_timestamp => SYSDATE - 1  -- 1 日前の統計に戻す
    );
END;
/

-- 統計の履歴を確認
SELECT table_name, stats_update_time
FROM dba_tab_stats_history
WHERE owner = 'HR' AND table_name = 'ORDERS'
ORDER BY stats_update_time DESC;

-- 保持期間の確認
SELECT DBMS_STATS.GET_STATS_HISTORY_RETENTION FROM DUAL;
-- デフォルト: 31 日