使用 db2look 重建優化器存取計劃（五）：手工修改統計資料與 SQL1227N 不一致錯誤排查

背景

在重現優化器存取計劃時，常需手工調整系統統計視圖（SYSSTAT.TABLES、SYSSTAT.INDEXES 等）以模擬特定資料分佈。但統計值之間存在嚴格的一致性約束，任意修改極易觸發 SQL1227N 錯誤。本文以實戰案例示範如何識別並修復此類不一致。

案例：嘗試將索引基數從 35 改為 37

原始 db2look 輸出片段

UPDATE SYSSTAT.INDEXES SET
  NLEAF=1, NLEVELS=1,
  FIRSTKEYCARD=35, FIRST2KEYCARD=35, FIRST3KEYCARD=-1, FIRST4KEYCARD=-1,
  FULLKEYCARD=35, CLUSTERFACTOR=-1.0, CLUSTERRATIO=100,
  SEQUENTIAL_PAGES=0, DENSITY=0,
  AVERAGE_SEQUENCE_GAP=0.0, AVERAGE_SEQUENCE_FETCH_GAP=0.0,
  AVERAGE_SEQUENCE_PAGES=0.0, AVERAGE_SEQUENCE_FETCH_PAGES=0.0,
  AVERAGE_RANDOM_PAGES=1.0, AVERAGE_RANDOM_FETCH_PAGES=0.0,
  NUMRIDS=35, NUMRIDS_DELETED=0, NUM_EMPTY_LEAFS=0
WHERE INDNAME = 'NAME_IND' AND INDSCHEMA = 'SKAPOOR'
  AND TABNAME = 'STAFF' AND TABSCHEMA = 'SKAPOOR';

同檔案中對應的表統計：

UPDATE SYSSTAT.TABLES SET
  CARD=35, NPAGES=1, FPAGES=1, OVERFLOW=0, ACTIVE_BLOCKS=0
WHERE TABNAME = 'STAFF' AND TABSCHEMA = 'SKAPOOR';

修改嘗試與失敗

將 FIRSTKEYCARD、FIRST2KEYCARD、FULLKEYCARD、NUMRIDS 全部改為 37 後執行，報錯：

SQL1227N The catalog statistic "37" for column "FULLKEYCARD" is out of range
for its target column, has an invalid format, or is inconsistent in relation
to some other statistic. Reason Code = "8". SQLSTATE=23521

原因： FULLKEYCARD (37) > TABLES.CARD (35)，違反「索引鍵值唯一數不能超過表基數」的約束（Reason Code 8）。

不一致性根源分析

生產環境若在 RUNSTATS 執行期間發生寫入活動（INSERT/UPDATE/DELETE），表統計與索引統計的採樣時間點不同步，極易產生：

• INDEXES.FIRSTKEYCARD / FULLKEYCARD / NUMRIDS > TABLES.CARD
• INDEXES.NLEAF / NLEVELS 與實際索引結構不符
• 分區表中各分區統計不一致

此類不一致會導致 db2look -m 輸出的 UPDATE 陳述式在測試環境執行失敗，進而使優化器依賴的統計資料不完整，存取計劃失真。

修復策略與最佳實務

1. 以表基數為基準：將所有索引的 FIRSTKEYCARD、FULLKEYCARD、NUMRIDS 設為 ≤ TABLES.CARD。
2. 或反向調整表基數：若確定索引統計較新，可將 TABLES.CARD 提升至 ≥ 最大索引基數。
3. 同步修改相關欄位：調整基數時，同步檢查 CLUSTERRATIO、CLUSTERFACTOR、SEQUENTIAL_PAGES 等衍生欄位是否合理。
4. 分批驗證：修改後先執行 config.out、storage.out，再 db2stop/db2start，最後執行 db2look.out，逐檔確認輸出無錯誤。

驗證存取計劃變化

修復統計後，以解釋模式執行測試查詢：

db2 "select name from staff where id=10 order by name"

觀察存取計劃成本與運算子變化。本案例中，統計修正後由索引掃描轉為表掃描 + 排序，總成本從 8.x 升至 12.97，確認統計資料對優化器決策的決定性影響。

檢查清單：常見統計不一致類型

結語

手工注入統計資料是重現優化器問題的核心技能，但必須嚴守統計一致性約束。建議建立「統計注入驗證腳本」，自動檢查上述規則，並在 db2look -m 輸出後、執行前強制跑驗證，避免人工疏漏導致重現失敗。