Optim Query Tuner 實戰指南（三）：應用層級 Workload 整體調優與持續監控體系建設

從單查詢到應用整體：為什麼需要 Workload 級調優？

生產環境中，應用包含成百上千查詢，單點優化可能因計劃翻轉、資源爭用導致整體效能倒退。OQT 引入 Workload (工作負載) 概念：一組查詢 + 執行頻率/耗時/資源消耗等運行時上下文。本文完整展示：Workload 採集 → 統計/查詢/索引三大 Advisor 整體建議 → 監控器部署 → 持續健康檢查閉環。

第一階段：Workload 採集與準備 — 向導化全流程

1. 啟動 Workload Wizard

Workload List 視圖 → New Workload → Wizard

2. 指定查詢來源

• Statement Cache：動態 SQL (緩存中)
• Catalog：靜態 SQL (Package/Plan 存於系統表)

3. 定義過濾條件 — 精準切片

針對來源類型提供專屬過濾器。範例：從 Statement Cache 抓取 AUTHID=ADMF001 且 執行次數 ≥ 3 的查詢。

4. 選擇採集模式 — 靜態 vs 動態差異

5. 檢視與分頁管理

採集完成 → Workload Tuning Editor 顯示：SQL 文本、執行次數、累計/平均耗時。預設 50 條/頁，支援自訂過濾再縮減。

6. 可選：合併參數化查詢

僅 Literal Value 不同的查詢 (如 WHERE NAME='T1' / 'T2' / 'T3') 合併為單條 WHERE NAME=?，減少分析噪音。

7. 產生 EXPLAIN 資訊 — 分析前提

• 靜態 SQL 若綁定時 EXPLAIN YES 已有計劃
• 否則對 Workload 定義 EXPLAIN 任務：全量重生或僅補缺失

8. 大規模 Workload 加速：Stored Procedure 後台執行

• 主機端裝 Java + JCC Driver
• 部署 OQT 提供的採集/合併/EXPLAIN 預存程序
• 客戶端 Preferences → 勾選 Use Stored Procedures

第二階段：三大 Advisor 整體出擊

Workload Statistics Advisor (WSA) — 統計資訊維護總綱

• 逐條分析每查詢用到的統計項 → 合併去重 產出最小 RUNSTATS 集合
• 依查詢在 Workload 中權重 (頻率×耗時) 推薦高被引欄位/組合
• 優先級反轉：維護級 (高優先) > 效能級 (低優先) — 鼓勵定期全維護
• 一鍵執行推薦 RUNSTATS → 驗證新統計 → 衝突檢測 (如表行數 1000 卻有欄位 1500 distinct) → 存入/取出 Profile 表版本管理

Workload Query Advisor (WQA) — SQL 重寫機會全景圖

• 逐條分析 → 綜合報告：總查詢數、含警告查詢數、警告條數、低嚴重度查詢/警告數
• 聚焦「含警告查詢」清單：顯示警告數、執行頻率、累計耗時、CPU 時間
• Drill-down 至單查詢 Query Advisor 詳細建議 (波浪線標記、解釋、範例)

Workload Index Advisor (WIA) — 索引設計全域優化

• 預設無磁盤限制 → 推薦最大效能增益索引集 (例：5 表 13 索引 380MB 空間換 69% 提升)
• What-If Analysis：設磁盤上限 (如 200MB) → 重新推薦 (4 表 8 索引 198.7MB 換 61% 提升，僅犧牲 8% 效能省 47% 空間)
• 磁盤緊張環境的最佳折衷決策工具

第三階段：應用效能監控器 — 生產環境持續雷達

建立 Normal Monitor 向導四步驟

1. Monitor List → Monitor Profile → New，選 Normal 類型
2. SQL 類型：動態 (需 AUTHID + IP) / 靜態 (需 Plan 名或 Package 全限定名)
3. 過濾深度：基本資訊 (耗時/CPU，低開銷) vs 全運行時資訊 (+10% CPU)
4. 啟動策略：立即 / 排程 (開始/結束時間) / 手動啟動 / 停用待激活

監控器運行後的價值

• 自動採集生產 SQL 執行統計 → 歷史趨勢圖譜
• 異常突增 (耗時/頻率/資源) 即時告警
• 定期 (周/月) 產出 Workload 健康報告 → 驅動下一輪 WSA/WQA/WIA 優化迭代
• 配合 DB2 Profile 基礎設施，零代碼侵入

閉環總結：從被動救火到主動經營

1. 採集：Workload Wizard 多源、多模式、參數化合併、EXPLAIN 完備
2. 診斷：WSA (統計) / WQA (SQL) / WIA (索引) 三維整體建議，What-If 支援約束決策
3. 執行：一鍵 RUNSTATS、SQL 重寫指導、索引 DDL 生成
4. 監控：Normal Monitor 持續採集生產運行實況
5. 迭代：監控數據餵回 Workload → 觸發新一輪診斷優化

建立這套「採集→診斷→執行→監控→迭代」閉環，即可將資料庫效能管理從事後諸葛亮轉型為持續優化的工程體系。