Quest SQL Tuning for Oracle 實戰教程：從自動優化到專家級 SQL 重寫

Quest Central (現屬 Dell/Quest) 的 SQL Tuning 模組是圖形化自動 SQL 優化利器，支援 Oracle/DB2/SQL Server 跨平台管理。本文以 SQL Server 為例演示全流程，核心邏輯對 Oracle 同樣適用。

工具定位

• 非侵入式 SQL 採集
• 自動生成等價優化方案
• 專家建議與執行計劃對比
• 最佳 SQL 自動選優

完整操作流程

1. 建立連接

Quest Central → Database 樹選 SQL Server → Tools 選 SQL Tuning → New Connection → 輸入實例/認證 → Connect

2. 分析原始 SQL

「Original SQL」文本框貼入待優化語句 → 選擇目標資料庫 → Execute → 工具自動解析執行計劃、統計信息、成本估算

3. 自動優化

點擊 Optimize Statement → 引擎基於規則/成本/語義等價變換生成候選方案 (如示例產生 19 條)

4. 執行驗證與選優

1. 列表全選 → 下拉 Execute Selected
2. 全部跑完 → 點擊 Tuning Resolution
3. 查看最優方案：示例中 運行時間提升 21%

5. 專家級 SQL 學習

點擊 Compare Scenarios：

• 任意兩條 SQL 彩色差異高亮
• 執行計劃並排對比 (操作類型、訪問路徑、成本、Cardinality)
• 從「工具給什麼」進化到「自己會寫什麼」

優化後最佳 SQL 範例 (節選)

SELECT dbo.Person_BasicInfo.*,
       dbo.Graduater_GraduaterRegist.RegistNO AS RegistNO,
       ...
       CASE WHEN dbo.Graduater_Business.ComeFrom = 'WS' THEN '網上登記'
            WHEN dbo.Graduater_Business.ComeFrom = 'HP' THEN '華普大廈'
            ...
       END AS ComeFromName,
       ...
FROM dbo.Person_BasicInfo
INNER JOIN dbo.Graduater_Business ON ...
LEFT OUTER JOIN dbo.Graduater_GraduaterRegist ON ...
INNER JOIN dbo.Person_Contact ON ...
INNER JOIN dbo.Person_Skill ON ...
INNER JOIN dbo.Person_EmployPurpose ON ...
OPTION (FORCE ORDER);  -- 強制連接順序 Hint

Oracle SQL Tuning 核心目標與指導原則

目標

最小化物理讀 (PIO) 與邏輯讀 (LIO)，以最少訪問獲取最多目標行，生成最優執行計劃。

四大指導原則

1. 移除不必要的大表全表掃描：加索引 (B-tree/Bitmap/Function-based) 或 Hint 干預
2. 小表全表掃描緩存化：ALTER TABLE ... CACHE 或 KEEP POOL (10g+)
3. 使用最佳索引：多索引選擇時驗證 CBO 選擇是否最優
4. 物化聚合靜態化統計：10g SQL Access Advisor 推薦 Materialized View 預連接/預聚合

優化器模式選擇策略

關鍵：統計信息缺失時 CBO 會動態採樣估算，導致嚴重誤判。DBA 首要動作：檢查並修正優化器模式與統計信息完整性。

三步調優戰略

1. 捕獲高影響 SQL：V$SQLAREA 按 EXECUTIONS/BUFFER_GETS/DISK_READS/CPU_SECS 排序
2. 獲取執行計劃：EXPLAIN PLAN + PLAN_TABLE (utlxplan.sql) → CONNECT BY PRIOR 顯示樹狀結構
3. 調整手段：
   • Hint 干預 (FIRST_ROWS_10, ORDERED, OPT_PARAM)
   • 全局臨時表 (GTT) 重寫複雜子查詢
   • PL/SQL 封裝存儲過程 (特定查詢可獲 20x 提升)

高效 SQL 編寫 12 條鐵律

1. 臨時表/GTT + WITH 重寫複雜子查詢
2. MINUS 替代 NOT IN/EXISTS
3. 分析函數 (ROLLUP/CUBE) 單掃描多維聚合
4. NOT EXISTS 改外連接 IS NULL
5. NULL 值列建函數索引
6. 避免索引列運算 (或建函數索引)
7. 避免 NOT IN、HAVING
8. 避免 LIKE '%...' 前導通配
9. 避免隱式類型轉換
10. DECODE/CASE 減少表訪問
11. 不盲目恐懼全表掃描 (評估訪問比例、並行度、緩存)
12. 全程使用表別名