SQL 調優黃金目標
**最小化 I/O、最小化 CPU、最小化等待** — 所有技巧皆服務於此。
法則一：消除不必要的大表全表掃描 (FTS)
判斷標準
- 有序表 (Index Organized / Clustered)：回傳行數 < **40%** → 應用索引。
- 無序表 (Heap Table)：回傳行數 < **7%** → 應用索引。
對策工具箱
- 建立索引：B-Tree (高選擇度)、Bitmap (低選擇度/資料倉庫)、Function-Based (函數/運算式欄位)。
- Hint 強制索引：
/*+ INDEX(t idx_name) */ — 臨時手段，根治靠統計資訊。
- 重寫 SQL：避免隱性轉換、
NOT IN→NOT EXISTS、OR→UNION ALL、啟用 Partition Pruning。
法則二：小表全表掃描 → 快取化
| 版本 | 語法 | 機制 |
|---|
| 7/8i | ALTER TABLE small_tbl CACHE; | 放入 LRU 熱端 (MRU)，但仍可能被淘汰 |
| 8i+ | ALTER TABLE small_tbl STORAGE (BUFFER_POOL KEEP); | **KEEP Pool 永駐**，不被淘汰，**首選** |
| 12c+ | INMEMORY PRIORITY HIGH | In-Memory Column Store，極致效能 |
**小表定義**：BLOCKS < _SMALL_TABLE_THRESHOLD (預設 ~2% Buffer Cache)，通常 < 10,000 Blocks。
法則三：確保最優索引使用
- 多索引競爭：檢查
V$SQL_PLAN 實際用哪個，必要時 /*+ INDEX(t idx_best) */ 或 INDEX_COMBINE。
- Bitmap 索引：僅適合 **資料倉庫、低並發、多維度組合過濾** (星型查詢)。OLTP 會造成嚴重鎖競爭。
- Function-Based Index：
CREATE INDEX idx_upper_name ON emp(UPPER(ename)); 解決 WHERE UPPER(ename) = 'KING' 無法用索引。
- Invisible Index (11g+)：
ALTER INDEX idx INVISIBLE; 測試新索引不影響現有計畫，驗證後 VISIBLE。
法則四：確保最優 Join 方法
| Join 方法 | 適用場景 | 驅動條件 | Hint |
|---|
| Nested Loops | 小結果集驅動大表精準存取 (索引) | 外層行數少、內層有索引 | /*+ USE_NL(t1 t2) */ |
| Hash Join | 大表關聯大表、無索引、等值連接 | 記憶體足夠建 Hash Table (PGA) | /*+ USE_HASH(t1 t2) */ |
| Sort Merge Join | 大表關聯、非等值/範圍連接、已排序輸入 | 雙方都需排序、或索引全掃描已有序 | /*+ USE_MERGE(t1 t2) */ |
**驅動表選擇**：CBO 依統計資訊估算輸出行數，**輸出行數少者為外層 (Driving/Outer)**。Hint 指定 LEADING(t1 t2) 強制順序。
排序優化專題
排序觸發場景
ORDER BY, GROUP BY, DISTINCTCREATE INDEX, UNION (非 ALL), INTERSECT, MINUS- Join 缺索引退而求其次
SORT MERGE JOIN
記憶體 vs 磁碟排序
- 記憶體排序：
SORT_AREA_SIZE (手動) / PGA_AGGREGATE_TARGET (自動) 分配。**快 ~14,000 倍**。
- 磁碟排序：超出記憶體 → Temp 表空間分頁。**極慢、佔 Temp、產生 I/O、推高 free buffer waits**。
排序優化清單
- 設定合理
PGA_AGGREGATE_TARGET (總 RAM 20~30%) + PGA_AGGREGATE_LIMIT (硬上限)。
- 避免不必要的
ORDER BY (應用層排序)、DISTINCT (改唯一索引/約束)。
- 大排序操作 (索引建立、大表 Group By) 設定
PARALLEL 分散壓力。
- 監控
V$SYSSTAT sorts (memory) vs sorts (disk)，**磁碟排序比率 > 5% 即需調優**。
結語
SQL 調優無銀彈，**四大法則覆蓋 90% 場景**：消除大表 FTS、小表快取化、精準索引/Join、排序留在記憶體。建立 **SQL 基線庫 (SPM)**、自動化 **AWR/ASH Top SQL 審核機制**、開發階段 **EXPLAIN PLAN 強制審查**，將調優左移，讓資料庫永遠跑在最優路徑上。
