DB2 執行計畫優化實戰：統計信息收集、RUNSTATS、REORGCHK、LOAD STATISTICS 與 db2look 遷移

前言

DB2 採用基於成本的優化器（CBO），其生成的存取計畫品質高度依賴系統目錄中的統計信息。本文系統闡述統計信息類型、收集時機、核心工具（RUNSTATS/REORGCHK/LOAD STATISTICS/db2look）用法與自動化策略，確保優化器始終擁有最新、精確的數據畫像。

優化器決策依據：四大統計信息維度

何時必須 RUNSTATS？

1. 建立新索引後：DB2 8.2+ 可 CREATE INDEX ... COLLECT STATISTICS 同步更新；舊版必須手動 RUNSTATS。
2. REORG 表/索引後：高水位線、頁密度、聚簇因子變化，必須重收統計。
3. 數據量變化超過 10-20%：大量 INSERT/UPDATE/DELETE 後。
4. 執行 REDISTRIBUTE DATABASE PARTITION GROUP：分區數據分布改變。
5. 大表採樣統計：數據倉庫事實表可用 TABLESAMPLE BERNOULLI (20) 或 RUNSTATS ... TABLESAMPLE SYSTEM (20)（需數據分布均勻）。

RUNSTATS 完整語法範例

-- 完整統計：表 + 分布 + 所有索引詳細
RUNSTATS ON TABLE db2inst1.staff
  WITH DISTRIBUTION AND DETAILED INDEXES ALL
  ALLOW WRITE ACCESS;

-- 採樣統計（大表）
RUNSTATS ON TABLE big_fact_table
  WITH DISTRIBUTION AND SAMPLED DETAILED INDEXES ALL
  TABLESAMPLE SYSTEM (20);

REORGCHK：判斷是否需 REORG

-- 檢查用戶表
REORGCHK UPDATE STATISTICS ON TABLE USER

-- 檢查系統表
REORGCHK UPDATE STATISTICS ON TABLE SYSTEM

-- 僅生成報告不更新統計
REORGCHK CURRENT STATISTICS ON TABLE USER

輸出報告中 F1-F8 公式值超標（通常 > 100 或 < 0）建議 REORG。

易變表處理：VOLATILE CARDINALITY

對於短時間內數據量劇烈波動的表（如電信月末匯總表），統計信息失真。標記為易變表：

ALTER TABLE volatile_table VOLATILE CARDINALITY;

效果：優化器傾向選擇索引掃描而非全表掃描，無視統計信息中的基數估算。

LOAD 統計選項：裝載同步收集統計

-- 完整統計
LOAD FROM mobile_number.del OF DEL REPLACE INTO mobile
  STATISTICS YES WITH DISTRIBUTION AND DETAILED INDEXES ALL;

-- 簡略統計
LOAD FROM mobile_number.del OF DEL REPLACE INTO mobile
  STATISTICS YES AND INDEXES ALL;

-- 使用預設統計配置檔（DB2 9.7+）
LOAD FROM mobile_number.del OF DEL REPLACE INTO mobile
  STATISTICS USE PROFILE;

db2look：統計信息遷移利器

場景：開發/測試環境需複製生產環境統計信息，確保存取計畫一致。

1. 源端收集並導出：

   db2 RUNSTATS ON TABLE prod_schema.target_table
   db2look -d PROD_DB -t TARGET_TABLE -m -o stats.sql
   # stats.sql 含 UPDATE SYSSTAT.TABLES/COLUMNS/INDEXES ... 語句

2. 目標端應用：

   db2 -tvf stats.sql

自動維護配置建議

-- 關閉自動統計收集（生產環境建議手動控制）
UPDATE DB CFG USING AUTO_MAINT OFF AUTO_TBL_MAINT OFF AUTO_RUNSTATS OFF;

理由：自動收集雖方便，但執行時機不可控，可能衝擊高峰期效能。建議納入定時排程作業（如每日凌晨低峰），結合 REORG → RUNSTATS → REBIND 全鏈路自動化。

最佳實踐流程圖

[數據變更/結構變更] 
      ↓
[REORG 表/索引] → [RUNSTATS 完整/採樣] → [REBIND 套件] 
      ↓                    ↓
[VOLATILE 標記]      [db2look 遷移統計] (跨環境)
      ↓
[驗證 EXPLAIN 計畫] → [效能基線對比] → [釋放上線]

結語

統計信息是優化器的「眼睛」。建立定時、分級、自動化的統計收集體系，配合 REORGCHK 判斷重組、VOLATILE 處理易變表、db2look 跨環境同步統計，能讓 DB2 優化器始終基於真實數據畫像做決策，消除計畫退化帶來的效能驚喜。