Oracle SQL 效能調優非單一技巧，而是建立在優化器模式選擇、資料存取路徑理解、共享游標機制掌握三大支柱之上。本文系統梳理這三大核心領域，提供從理論到實戰的調優思維框架。
| 模式 | 適用場景 | 統計資訊需求 | 設定方式 |
|---|---|---|---|
| RULE (RBO) | 舊版相容、無統計資訊環境 | 不需要 | OPTIMIZER_MODE=RULE |
| COST (CBO) | 現代標準、有完整統計資訊 | 必須定期 ANALYZE/DBMS_STATS | OPTIMIZER_MODE=ALL_ROWS/FIRST_ROWS_n |
| CHOOSE | 混合環境 (已棄用) | 有統計用 CBO，無則 RBO | OPTIMIZER_MODE=CHOOSE (預設) |
核心建議：生產環境**明確設定 CBO 模式** (ALL_ROWS 批次/報表、FIRST_ROWS_n OLTP)，避免 CHOOSE 導致計劃不穩定。定期執行 DBMS_STATS.GATHER_SCHEMA_STATS 維持統計資訊鮮度。
| 路徑 | 機制 | 適用條件 | 優化關鍵 |
|---|---|---|---|
| 全表掃描 (FTS) | 多區塊讀取 (DB_FILE_MULTIBLOCK_READ_COUNT) 循序讀取所有區塊 | 小表、高選擇率 (>5-10%)、無可用索引、並行查詢 | 調大 MBRC、考慮分區修剪 |
| 索引存取 (INDEX RANGE/UNIQUE SCAN) | B*Tree 走訪 → ROWID → TABLE ACCESS BY INDEX ROWID | 高選擇性 (<1-2%)、前導欄位在 WHERE/JOIN 條件 | 建立複合索引符合查詢欄位順序、避免隱性轉換 |
ROWID 含義：物理地址編碼 (物件號、檔案號、區塊號、列號)，索引葉塊儲存 (KEY, ROWID) 對應，實現邏輯值到物理位置的快速映射。
共享池 中的 Library Cache 以 SQL 文本為 Key 快取解析樹與執行計劃。完全匹配原則要求：
:b1 vs :b2 視為不同-- 大小寫不同 → 硬解析
SELECT * FROM EMP;
select * from emp;
-- 字面值而非繫結變數 → 硬解析 + Library Cache 壓力
SELECT * FROM ORDERS WHERE ORDER_DATE = '2024-01-15';
SELECT * FROM ORDERS WHERE ORDER_DATE = '2024-01-16';
-- 正確：使用繫結變數
SELECT * FROM ORDERS WHERE ORDER_DATE = :dt;| 欄位 | 含義 | 調優關注點 |
|---|---|---|
| OPERATION | 步驟類型 (TABLE ACCESS FULL, INDEX RANGE SCAN, NESTED LOOPS...) | 是否出現非預期 FTS、CARTESIAN JOIN |
| OPTIONS | 細節 (ANALYZED, PARTITION RANGE...) | 統計資訊是否存在、分區修剪是否生效 |
| COST | CBO 預估資源消耗 (CPU+I/O 加權) | 同一 SQL 不同計劃比較 Cost，絕對值無跨 SQL 可比性 |
| CARDINALITY | 預估回傳列數 | 與實際差距大 → 統計資訊過期/直方圖缺失 |
| BYTES | 預估資料量 | 評估記憶體/Temp 空間需求 |
原則：**最右、最上** 的操作最先執行。去除 INDEX 縮進後，第一個 TABLE ACCESS 對應驅動表 (Outer Table)。
| Id | Operation | Name | Rows |
|----|--------------------|------|------|
| 0 | SELECT STATEMENT | | |
| 1 | MERGE JOIN | | |
| 2 | SORT JOIN | | |
| 3 | NESTED LOOPS | | |
| 4 | TABLE ACCESS FULL| B | 10 | <-- 最右最上：驅動表 B
| 5 | TABLE ACCESS BY INDEX ROWID| A | 1 |
| 6 | INDEX RANGE SCAN| IDX_A | 1 |
| 7 | SORT JOIN | | |
| 8 | TABLE ACCESS FULL| C | 5 |連接順序：B → A → C。若 CBO 選錯，可用 /*+ ORDERED */ + FROM 子句順序強制指定。
系統化掌握優化器行為、存取路徑選擇邏輯、共享游標匹配規則，配合執行計劃深度解讀能力，是 SQL 調優從「試誤」走向「科學決策」的關鍵轉折點。
