使用 db2look 重建優化器存取計劃（六）：單分區環境模擬生產資料庫完整指南

概述

在單分區測試環境中完整模擬生產資料庫，是驗證 SQL 效能、重現優化器存取計劃問題的關鍵步驟。本文詳細說明如何使用 db2look 搭配 -l 選項收集儲存物件定義，並配合配置參數匯出，在測試機上 1:1 還原生產環境的優化器決策依據。

前置條件與注意事項

• 若測試資料庫名稱與生產環境不同，需手動修改 db2look 輸出腳本中的資料庫名稱。
• 測試環境的目錄路徑、容量、緩衝池設定可能與生產環境差異極大，必須依實際情況調整 DDL。
• 僅模擬優化器行為而非複製完整資料時，可大幅縮減容器大小與數量。

步驟 1：收集並部署儲存物件定義

1.1 產生儲存物件 DDL

db2look -d <dbname> -l -o storage.out

-l 選項會擷取表空間、緩衝池、資料庫分區群組等儲存層級物件。

1.2 修正表空間定義以適應測試環境

生產環境典型的 DMS 表空間定義如下：

CREATE REGULAR TABLESPACE DMS1 IN DATABASE PARTITION GROUP IBMDEFAULTGROUP
  PAGESIZE 4096
  MANAGED BY DATABASE
  USING ( FILE '/data/dms1' 20000, FILE '/data/dms2' 20000, FILE '/data/dms3' 20000 )
  EXTENTSIZE 32 PREFETCHSIZE 32
  BUFFERPOOL IBMDEFAULTBP
  OVERHEAD 12.67 TRANSFERRATE 0.18
  DROPPED TABLE RECOVERY ON;

必須調整項目：

• 容器路徑：測試機若無 /data 掛載點，需改為實際存在的目錄（如 /home/db2inst1/tablespaces）。
• 容器大小：僅模擬優化器時，可將 20,000 頁縮減為 1,000 頁甚至更小。
• 容器數量：依測試機磁碟數調整，至少保持 1 個容器即可。
• 緩衝池名稱：若測試機未建立同名緩衝池，需修改為現有同頁面大小的緩衝池。
• 頁面大小：嚴禁修改，否則會導致優化器成本模型失真。

1.3 執行儲存物件建立腳本

db2 -tvf storage.out

建議重導向輸出以便事後稽核：

db2 -tvf storage.out > storage_results.out

檢查 storage_results.out 確認無錯誤訊息。

步驟 2：收集並套用配置參數與環境變數

2.1 產生配置匯出檔

db2look -d sample -f -fd -o config.out

• -f：匯出資料庫管理器與資料庫配置參數。
• -fd：匯出 db2set 登錄變數與環境變數。

MPP 環境特殊處理： 若生產為多節點，需在每個分區分別執行上述指令；測試為單分區時，僅取執行查詢的那個節點的配置即可，但會導致模擬不完整（如平行度、分區消除等行為差異）。

2.2 在測試機套用配置

db2 -tvf config.out

此步驟會將下列關鍵優化器依據同步至測試環境：

• 緩衝池總大小、排序堆大小（影響 db2fopt 估算）
• 所有 DB CFG / DBM CFG 參數
• 登錄變數與環境變數（如 DB2_SORT_AFTER_TQ、DB2_OPT_MAX_TEMP_SIZE 等）

即使測試機記憶體不足以支撐生產級緩衝池/排序堆，仍需先套用相同數值，再依記憶體限制調整；如此優化器才會依相同成本模型做決策。

常見坑點提醒

1. 頁面大小不一致：表空間與緩衝池頁面大小必須完全一致，否則建立失敗或優化器行為異常。
2. 容器路徑權限：實例擁有者需對容器目錄擁有讀寫權限。
3. 配置參數動態生效：部分參數需 db2stop/db2start 才生效，建議套用後重啟實例。
4. MPP vs. 單分區差異：單分區無法模擬分區裁剪、資料傾斜、跨節點連接等行為，僅適合單機 SQL 調優。

後續驗證建議

完成上述兩步驟後，可在測試機執行 RUNSTATS（或手工注入統計資料），再以 EXPLAIN 驗證目標 SQL 的存取計劃是否與生產一致。若不一致，請依序檢查：統計資料、配置參數、緩衝池大小、頁面大小、容器數量與 PREFETCHSIZE 設定。