核心論點：SQL 是效能的決定性因素
應用程式執行最終歸結為資料庫中的 SQL 執行。SQL 消耗 70%-90% 的資料庫資源，因此 SQL 執行效率最終決定了 Oracle 資料庫的性能。
常見迷思破解
| 迷思 | 事實 |
|---|
| 「查詢優化是 DBMS 的事，跟我寫的 SQL 無關」 | 優化器依賴 SQL 寫法、統計資訊、索引設計。糟糕的 SQL 無法靠優化器彌補 |
| 「加上 Hint 就能解決效能問題」 | Hint 是強制手段，維護成本高，升級可能失效。應優先從寫法、索引、統計資訊入手 |
| 「硬體升級比 SQL 調優划算」 | 硬體有上限且成本線性增長；好的 SQL 調優可帶來指數級效能提升，成本一次性 |
| 「調優要改應用程式碼，風險太大」 | SQL 與程式邏輯分離，調優 SQL 風險遠低於改程式碼，且可在資料庫端獨立驗證 |
SQL 調優四大核心途徑
途徑一：有效索引建立策略
| 場景 | 索引策略 | 注意事項 |
|---|
| 頻繁 JOIN 但非外鍵的欄位 | 建立 B-tree 索引 | 外鍵應自動建立索引 (12c+ 自動)，手動補齊舊版 |
| 頻繁 ORDER BY / GROUP BY 欄位 | 建立索引支援排序避免 SORT | 複合索引欄位順序需匹配查詢順序 |
| WHERE 條件高選擇性欄位 (值分布廣) | 建立索引 | 選擇性 = 不同值數/總列數，> 0.1 考慮索引 |
| 低選擇性欄位 (性別、狀態、布林) | 一般不建索引 | 位圖索引適用數據倉庫低並發環境 |
| 多欄位組合查詢 | 複合索引 (最多 32 欄位) | 前導欄位原則：最常用/最高選擇性在前 |
索引物理設計原則
- 表空間分離：索引建立在與表資料不同磁碟的表空間，降低 I/O 爭用
- 索引類型選擇：
- B-tree：OLTP 標準、高選擇性、排序支援
- Bitmap：數據倉庫、低基數、星型查詢
- Function-based：WHERE UPPER(name)='ABC'、表達式查詢
- Partitioned：大表分區對齊、分區修剪
- Invisible (11g+)：測試索引效果不影響生產計畫
- 選擇性測量：建立前先測量
COUNT(DISTINCT col)/COUNT(*)
途徑二：叢集索引考量 (Oracle 為 Index-Organized Table, IOT)
Oracle 無 SQL Server 風格的叢集索引，但提供 IOT (Index-Organized Table)：
CREATE TABLE iot_example (
id NUMBER PRIMARY KEY,
data VARCHAR2(100)
) ORGANIZATION INDEX TABLESPACE idx_ts;
- 資料直接儲存在 B-tree 索引葉節點，無 Heap Table
- 適合：主鍵查詢佔 90%+、表較小、需按 PK 範圍掃描
- 不適合：頻繁 UPDATE PK、大量非 PK 欄位、大型 LOB
途徑三：複合索引設計
-- 查詢: WHERE a=1 AND b=2 AND c>3 ORDER BY d
-- 索引: (a, b, c, d) -- 等值在前、範圍在後、排序在最後
-- 避免: (c, a, b) -- 範圍欄位在前導致後續欄位無法使用
-- 避免: 過多欄位 (維護成本、大小、選擇性遞減)
途徑四：運算式優化與優化器調整
| 原則 | 說明 | 範例 |
|---|
| 優先範圍查詢，少用 LIKE | LIKE '%abc%' 無法使用索引；前導 % 失效 | WHERE col BETWEEN 'A' AND 'Z' 優於 LIKE 'A%' |
| 避免隱性轉換 | WHERE num_col = '123' 導致全表掃描 | WHERE num_col = 123 |
| 函數索引支援運算式 | WHERE UPPER(name) = 'JOHN' | CREATE INDEX idx_upper_name ON t(UPPER(name)) |
| 統計資訊及時收集 | DBMS_STATS 定期、適當採樣 | DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT', tabname=>'EMP', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt=>'FOR ALL COLUMNS SIZE AUTO') |
| 優化器模式選擇 | ALL_ROWS (批次) vs FIRST_ROWS_n (互動) | ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_10 |
| 行鎖管理 | SELECT FOR UPDATE NOWAIT/SKIP LOCKED | 高併發佇列處理 |
調優工具鏈
| 工具 | 用途 | 適用階段 |
|---|
EXPLAIN PLAN / DBMS_XPLAN | 查看執行計畫、成本、基數估算 | 開發、測試 |
| SQL Trace (10046) + TKPROF | 實際執行統計：等待事件、CPU、I/O、列數 | 生產問題診斷 |
| SQL Tuning Advisor | 自動分析、建議索引、Profile、重寫 | 例行調優、難解 SQL |
| SQL Access Advisor | 建議索引、MV、分區 | 工作負載分析 |
| Real-Time SQL Monitoring (11g+) | 長跑 SQL 即時監控：進度、並行、等待 | 生產監控 |
| AWR/ASH 報表 | 歷史負載分析、Top SQL 識別 | 容量規劃、趨勢分析 |
實戰調優流程
- 識別：AWR/ASH/用戶回報 → 找出 Top SQL (高耗時、高頻率、高資源)
- 分析：
EXPLAIN PLAN、SQL Trace、實際執行統計
- 診斷：全表掃描? 索引缺失? 統計過舊? 連接順序錯? 基數估算偏差?
- 優化：改寫 SQL、建索引、收集統計、調整參數、Hint (最後手段)
- 驗證：測試環境執行計畫對比、實際執行時間、資源消耗
- 部署：變更管理流程、生產窗口、回滾計畫
- 持續監控：SQL Plan Baseline 固化、定期複查
關鍵指標閾值參考
| 指標 | 警戒閾值 | 嚴重閾值 |
|---|
| Buffer Cache Hit Ratio | < 95% | < 90% |
| Library Cache Hit Ratio | < 99% | < 95% |
| Hard Parse Rate | > 100/sec | > 1000/sec |
| Full Table Scan Ratio | > 20% | > 40% |
| Index Clustering Factor | 接近表塊數 | 接近表列數 (無序) |
| Avg Elapsed Time / Exec | > 1s (OLTP) | > 10s |
總結：調優心法
- 測量優於猜測：用工具數據說話
- 系統性優於點狀：從工作負載整體著手，非單一 SQL
- 根因優於症狀：修補統計資訊優於加 Hint
- 簡單優於複雜：改寫 SQL 優於建複雜索引/分區
- 持續優於一次性：建立 Baseline、自動化監控、定期複查
