前言：命令列調優是 DBA 的基本功
DB2 提供豐富的命令列工具 (CLP) 支援效能分析與調優。本文匯總核心調優命令，分類整理執行計劃分析、統計資訊管理、優化等級控制、索引維護、鎖監控、緩衝池調優等實戰指令，附參數解析與輸出解讀指南，作為 DBA 桌邊速查手冊。
一、執行計劃分析命令
1.1 db2exfmt - 格式化 EXPLAIN 輸出 (核心工具)
# 語法
db2exfmt -d -g -w -n -s -# -o
# 實戰範例
db2exfmt -d MYDB -g APP_SCHEMA -w -1 -n % -s % -# 0 -o explain_output.txt
# 參數解析
# -d: 資料庫名
# -g: 綁定圖形 (通常為 Schema)
# -w: WHERE 子句篩選 (-1 表全部)
# -n: 套件名 (% 表全部)
# -s: 陳述式編號 (% 表全部)
# -#: 區段號 (0 表全部)
# -o: 輸出檔案
1.2 db2expln - 靜態 SQL 存取計劃 (套件層級)
# 查看特定套件的存取計劃
db2expln -d MYDB -c APP_SCHEMA -p MY_PACKAGE -o expln_pkg.out
# 參數
# -c: 套件擁有者 (Schema)
# -p: 套件名稱
1.3 dynexpln - 動態 SQL 即時分析
# 直接分析 SQL 字串
dynexpln -d MYDB -q "SELECT * FROM ORDERS WHERE CUST_ID=123" -g -t
# 分析 SQL 檔案
dynexpln -d MYDB -f query.sql -o dyn_explain.out
# 參數
# -q: 直接指定 SQL 字串
# -f: 讀取 SQL 檔案
# -g: 圖形化輸出 (ASCII 藝術)
# -t: 顯示時間統計
1.4 Visual Explain - 圖形化介面 (GUI)
# 啟動方式
# 1. Data Studio / Db2 Developer Extension (VS Code) 內建
# 2. 獨立工具: db2vexp (舊版) 或 db2cc (Control Center)
# 3. 命令列觸發: db2 "SET CURRENT EXPLAIN MODE EXPLAIN" 後執行 SQL，再用 db2exfmt
1.5 存取計劃關鍵解讀指標
| 指標 | 健康閾值 | 異常含義 | 調優方向 |
|---|
| Table Scan | 大表無全掃 | 缺少索引、統計過期、謂詞無法下推 | 建立索引、RUNSTATS、改寫 SQL |
| Index Scan 類型 | IXSCAN (範圍) | INDEX ONLY (覆蓋) 最佳 | INCLUDE 欄位建立覆蓋索引 |
| Join 方法 | NLJ (小驅動大) | MSJ (排序合併)、HSJ (哈希) 適用大表 | 調整優化等級、建立索引、收集分佈統計 |
| Sort 溢位 | 0 | SORTHEAP 不足，溢寫磁碟 | 調大 SORTHEAP/SHEAPTHRES_SHR |
| Predicate Pushdown | Y | N 表謂詞未下推至存取層 | 改寫 SQL、檢查統計資訊、優化等級 |
| Estimated Cost (Timerons) | 越低越好 | 成本模型預估總資源消耗 | 對比不同寫法/索引的成本差異 |
二、統計資訊管理命令
2.1 RUNSTATS - 核心統計收集
# 表 + 所有索引 + 分佈統計 (最完整，耗時最長)
db2 "runstats on table APP_SCHEMA.CUST_TABLE with distribution and detailed indexes all"
# 僅表 + 所有索引 (無分佈，較快)
db2 "runstats on table APP_SCHEMA.CUST_TABLE and indexes all"
# 僅特定索引
db2 "runstats on table APP_SCHEMA.CUST_TABLE for indexes APP_SCHEMA.IDX_CUST_NAME, APP_SCHEMA.IDX_CUST_REGION"
# 允許讀取存取 (線上收集，不加鎖)
db2 "runstats on table APP_SCHEMA.CUST_TABLE allow read access"
# 取樣收集 (大表快速估算，10% 取樣)
db2 "runstats on table APP_SCHEMA.BIG_TABLE tablesample bernoulli (10)"
# 系統自動統計 (AUTO_RUNSTATS=ON 時由後台自動觸發)
2.2 統計資訊查詢與驗證
# 檢視表統計
db2 "select tabname, card, npages, fpages, stats_time from syscat.tables where tabschema='APP_SCHEMA'"
# 檢視索引統計
db2 "select indname, nleaf, nlevels, fullkeycard, clusterratio, stats_time from syscat.indexes where tabschema='APP_SCHEMA'"
# 檢視欄位分佈統計
db2 "select colname, colcard, high2key, low2key, numnulls, avgcolen from syscat.columns where tabschema='APP_SCHEMA' and tabname='CUST_TABLE'"
# 檢測統計過期 (超過 7 天或資料變動 > 10%)
db2 "select tabname, stats_time, current timestamp - stats_time as age from syscat.tables where tabschema='APP_SCHEMA' and (stats_time is null or current timestamp - stats_time > 7 days)"
2.3 統計資訊匯出/匯入 (跨環境同步)
# 匯出統計至檔案
db2 "call sysproc.admin_cmd('export to /tmp/stats.ixf of ixf select * from syscat.tables where tabschema=''APP_SCHEMA''')"
# 同理匯出 syscat.indexes, syscat.columns, syscat.coldist
# 匯入統計 (目標環境)
db2 "call sysproc.admin_cmd('import from /tmp/stats.ixf of ixf insert into sysstat.tables')"
# 注意: 需 SYSADM/DBADM 權限，謹慎操作，建議僅用於測試環境同步
三、優化等級控制
| 等級 | 特性 | 適用場景 | 設定方式 |
|---|
| 0 | 最小優化，禁用大多數重寫 | 除錯、基準測試基線 | SET CURRENT QUERY OPTIMIZATION 0 |
| 1 | 低度優化，基本重寫 | 簡單 OLTP、資源極限環境 | SET CURRENT QUERY OPTIMIZATION 1 |
| 2 | 較低優化 | 輕量級查詢 | SET CURRENT QUERY OPTIMIZATION 2 |
| 3 | 中度優化 | 一般決策支援 | SET CURRENT QUERY OPTIMIZATION 3 |
| 5 | 預設/推薦，平衡優化成本與品質 | 大多數 OLTP/OLAP 混合負載 | SET CURRENT QUERY OPTIMIZATION 5 (預設) |
| 7 | 高度優化，昂貴重寫 (星型結構、複雜子查詢) | 複雜數倉查詢、報表 | SET CURRENT QUERY OPTIMIZATION 7 |
| 9 | 最大優化，嘗試所有可能重寫 | 極複雜查詢、優化器測試 | SET CURRENT QUERY OPTIMIZATION 9 |
# 會話級設定 (斷開失效)
db2 "set current query optimization 7"
# 實例級預設 (永久，需重啟實例)
db2 update dbm cfg using DFT_QUERYOPT 5
# SQL 編譯時指定 (嵌入式 SQL / CLI)
# #pragma optimize(5) -- C/C++ 嵌入式 SQL
四、索引維護命令
# 重組索引 (線上，允許讀寫)
db2 "reorg indexes all for table APP_SCHEMA.CUST_TABLE allow write access"
# 重組索引 (清理模式，僅合併頁面，不重建)
db2 "reorg indexes all for table APP_SCHEMA.CUST_TABLE cleanup only allow write access"
# 重組表 (經典離線)
db2 "reorg table APP_SCHEMA.CUST_TABLE index APP_SCHEMA.IDX_CUST_PK"
# 重組表 (線上, DB2 10.1+)
db2 "reorg table APP_SCHEMA.CUST_TABLE inplace allow write access start"
# 暫停/繼續/停止線上重組
db2 "reorg table APP_SCHEMA.CUST_TABLE inplace pause"
db2 "reorg table APP_SCHEMA.CUST_TABLE inplace resume"
db2 "reorg table APP_SCHEMA.CUST_TABLE inplace stop"
# 檢查重組狀態
db2pd -reorg
五、鎖與並發監控命令
# 即時鎖等待鏈 (最實用)
db2pd -db MYDB -locks wait
# 詳細鎖資訊
db2pd -db MYDB -locks show detail
# 死鎖事件記錄 (需啟用 DB2_CAPTURE_DEADLOCK=ON)
db2pd -db MYDB -deadlock
# 應用程式鎖等待
db2 "select agent_id, appl_id, lock_mode, lock_status, lock_object_type, tabname from table(mon_get_locks(-1,-1)) where lock_wait = 'T'"
# 鎖升級監控
db2 "select * from table(mon_get_database(-2)) where lock_escals > 0"
六、緩衝池調優命令
# 檢視緩衝池配置
db2 "select bpname, npages, pagesize, blocknum, blocksize from syscat.bufferpools"
# 即時命中率監控
db2pd -db MYDB -bufferpools
# 動態調整大小 (即時生效，無需重啟)
db2 "alter bufferpool BP_16K_DSS immediate size 500000" # 頁面數
db2 "alter bufferpool BP_4K_OLTP immediate size automatic" # 自動調整 (STMM)
# 新增緩衝池
db2 "create bufferpool BP_32K_LOB immediate size 100000 pagesize 32k"
# 將表空間關聯至緩衝池
db2 "alter tablespace TS_LARGE_DATA bufferpool BP_16K_DSS"
七、其他實用調優命令
| 命令 | 用途 | 範例 |
|---|
| db2top | 即時效能儀表板 (Top-like) | db2top -d MYDB -i 2 |
| db2pd -db MYDB -tcbstats | 表空間統計 (I/O、命中率) | db2pd -db MYDB -tcbstats |
| db2pd -db MYDB -apinfo | 應用程式詳細資訊 | db2pd -db MYDB -apinfo agentid=123 |
| db2pd -db MYDB -dynamic | 動態 SQL 快取分析 | db2pd -db MYDB -dynamic | head -50 |
| db2mtrk -i -v | 記憶體追蹤 (實例/資料庫/應用) | db2mtrk -i -v |
| MON_GET_* 表函數 | SQL 介面監控 (現代替代 db2pd) | SELECT * FROM TABLE(MON_GET_DATABASE(-2)) |
| db2fopt | 查詢優化器特性測試 | db2fopt -d MYDB -q "SELECT ..." |
八、調優工作流程標準化
- 識別問題 SQL：`db2pd -dynamic` 找高耗時/高頻率 SQL；或應用層 APM 追蹤
- 獲取執行計劃：`SET CURRENT EXPLAIN MODE EXPLAIN` → 執行 SQL → `db2exfmt`
- 分析計劃：檢查掃描類型、Join 方法、排序、謂詞下推、成本
- 驗證統計資訊：`RUNSTATS` 狀態、分佈統計、索引聚簇比
- 制定調優動作：建索引、改寫 SQL、調優化等級、調參數、重組
- 驗證效果：再次獲取計劃、基準測試對比、上線觀察
- 固化最佳實踐：更新 SQL 規範、索引命名標準、參數基線
總結
掌握上述核心命令，配合系統化的調優方法論，可解決 90% 以上的 DB2 效能問題。關鍵在於：以執行計劃為導向、以統計資訊為基石、以工作負載特徵為依歸、以自動化腳本為加速器。建議將常用命令封裝為別名/函數/腳本，納入 DBA 日常工具箱，讓調優從「經驗駭客」轉型為「數據科學」。
