SQL 調優非單一技巧，乃**系統工程**。本文整合 **優化器模式選擇、表存取路徑決策、共享游標機制、連接順序優化** 四大支柱，構建從「寫對 SQL」到「寫快 SQL」的完整實戰知識體系。
支柱一：優化器模式與統計資訊基石
- 模式： 生產環境**強制 CBO** (`OPTIMIZER_MODE=ALL_ROWS` 或 `FIRST_ROWS_n`)，**禁用 CHOOSE/RULE**。
- 統計資訊： CBO 的眼睛。核心表**定期/增量收集**，斜欄位**直方圖** (`METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO'`)，分區表**全域+分區級**同步收集。
- 目標模式： 批次/報表 → `ALL_ROWS` (最小化總成本)；互動/分頁首頁 → `FIRST_ROWS_n` (最小化首筆回應)。
支柱二：表存取路徑 —— FTS vs Index Access
| 路徑 | 機制 | 適用選擇率 | 關鍵因子 | 執行計畫關鍵字 |
|---|
| Full Table Scan | 多區塊讀取 (`db file scattered read`)，順序掃描 HWM 內所有區塊 | > 5-10% | 聚簇因子差、無索引、並行查詢、小表 Keep Pool | TABLE ACCESS FULL |
| Index Access + ROWID | 索引掃描取得 ROWID → 單區塊讀取 (`db file sequential read`) 回表 | < 1-5% | 高選擇性、聚簇因子好 (接近 Blocks)、前導欄位匹配 | INDEX RANGE SCAN → TABLE ACCESS BY INDEX ROWID |
聚簇因子 (Clustering Factor) 是索引效能的隱形決定者： 接近 `BLOCKS` = 好 (資料依索引序排列)；接近 `ROWS` = 差 (回表隨機跳躍)。`DBA_INDEXES.CLUSTERING_FACTOR` 監控。
支柱三：共享游標與 Bind Variable 鐵律
精確匹配三條件 (滿足才能軟解析)
- 字元級完全相同： 大小寫、空格、換行、註釋皆敏感。
- 參考物件解析一致： 同義詞/Schema 解析到同一 `OBJECT_ID`。統一用 Public Synonym 或 `SCHEMA.TABLE`。
- 綁定變數名稱/類型一致： `:b1` vs `:b2` 即不可共享。框架層統一命名規範。
硬解析危害量化
- CPU 消耗大 (語法/語義/優化/行源生成)
- Library Cache Latch / Shared Pool Latch 熱點爭用
- Shared Pool 記憶體碎片化、ORA-04031 風險
- 執行計畫不穩 (Bind Peeking、統計資訊變更)
解決方案：Bind Variable 100% 覆蓋率
-- 惡例
String sql = "SELECT * FROM orders WHERE id = " + id;
-- 佳例 (Java/JDBC)
PreparedStatement ps = conn.prepareStatement("SELECT * FROM orders WHERE id = ?");
ps.setLong(1, id);
-- 佳例 (PL/SQL)
OPEN cur FOR 'SELECT * FROM orders WHERE id = :b1' USING v_id;
參數策略
- `CURSOR_SHARING = EXACT` (常態，**根治靠代碼規範**)
- `SESSION_CACHED_CURSORS = 300` (Session 級游標緩存，極速軟解析)
- 緊急救援：`CURSOR_SHARING = FORCE` (短期止血，直方圖失效風險，改碼後必關回 EXACT)
支柱四：連接順序與多表優化 (RBO 遺產 / CBO 參考)
雖 CBO 自動決定連接順序，但理解 **FROM 子句右到左解析 (RBO)** 與 **驅動表選擇** 原理，有助於 Hint 干預與邏輯重構。
驅動表選擇原則
- 小表驅動大表： 記錄數最少的表作為驅動表 (最後出現在 FROM 列表)。
- 交叉表優先： 3+ 表連接時，被多表引用的「中間表」優先驅動 (如 `EMP` 連 `DEPT`、`LOC`)。
實戰範例
-- 低效：大表在最後 (被優先掃描/排序)
SELECT * FROM big_table b, small_table s WHERE b.id = s.id;
-- 高效：小表在最後 (驅動表)
SELECT * FROM small_table s, big_table b WHERE s.id = b.id;
-- 3 表連接：交叉表 EMP 在最後
SELECT * FROM dept d, loc l, emp e
WHERE e.deptno = d.deptno AND e.loc_id = l.loc_id;
CBO 時代的連接順序干預
- `LEADING(t1 t2 t3)` Hint 強制連接順序。
- 統計資訊準確時，CBO 通常能自動選出最優順序，**優先修統計資訊，再用 Hint**。
綜合實戰檢查清單 (Code Review 標準)
- [ ] **無字串拼接 SQL** → 100% Bind Variable。
- [ ] **統計資訊最新** → 核心表日收集、斜欄位直方圖、分區表全域+分區級。
- [ ] **優化器模式正確** → `ALL_ROWS` / `FIRST_ROWS_n`，無 `CHOOSE/RULE`。
- [ ] **執行計畫存取路徑合理** → 選擇率 <5% 走 Index，>10% 走 FTS，聚簇因子驗證。
- [ ] **共享游標友善** → SQL 格式常數化、Public Synonym、綁定變數命名規範。
- [ ] **連接順序邏輯清晰** → 小表/交叉表驅動，必要時 `LEADING` Hint。
- [ ] **無隱式轉換** → `WHERE date_col = '2024-01-01'` → `WHERE date_col = DATE '2024-01-01'` 或 `TO_DATE`。
- [ ] **索引可用性** → 無函數包裝索引欄位、無 `!=`/`IS NULL` 導致失效 (除非 FBI)。
- [ ] **分頁查詢** → 12c+ `OFFSET/FETCH`，舊版 ROWNUM 雙層或 Keyset Pagination。
- [ ] **Hint 審計** → 每個 Hint 註釋「原因、預期、風險、到期評估日期」。
進階工具鏈
- SQL Tuning Advisor / SQL Access Advisor： 自動推薦索引、Profile、物化視圖。
- SQL Plan Management (SPM) / SQL Profile： 固化優良計畫，免受統計資訊波動影響。
- Real-Time SQL Monitoring (11g+)： 長跑 SQL 即時監控並行度、IO、等待事件。
- DBMS_XPLAN.DISPLAY_CURSOR： 終極執行計畫分析利器 (`FORMAT=>'ALL +OUTLINE +PEEKED_BINDS'`)。
SQL 優化沒有銀彈，只有**體系化的工程紀律**。將上述清單納入開發規範、代碼審查模板、上線驗收標準，才能從根源上消除 80% 的效能隱患。
