Oracle SQL 效能調優系列 (一)：優化器模式、資料存取路徑與共享游標

1. 選擇適合的優化器模式

Oracle 提供三種優化器模式，由初始化參數 `OPTIMIZER_MODE` 或 Session/Statement 層級覆蓋：

最佳實踐：明確設定 `OPTIMIZER_MODE=ALL_ROWS` (全庫最佳吞吐) 或 `FIRST_ROWS_n` (優先回應前 N 列)，並定期執行 `DBMS_STATS.GATHER_SCHEMA_STATS` 維護統計資訊。

2. 資料表存取方式

• Full Table Scan (FTS)：依序讀取所有資料塊，多塊讀取 (`DB_FILE_MULTIBLOCK_READ_COUNT`) 優化。適合小表、大比例資料存取、無可用索引。
• ROWID 存取 (Index Access)：透過 Index 取得 ROWID 再定位資料列。Index 提供快速 ROWID 查找，適合高選擇性 (Selectivity) 查詢。

關鍵指標：`Clustering Factor` (CF) 反映索引順序與表物理順序一致度；CF 接近塊數 = 好，接近列數 = 差。`Filter Factor` (FF) 為預估回傳列數佔總列數比例。CBO 成本估算約 `FF * (CF + Index Blocks)`。

3. 共享 SQL (Shared Cursor) 機制

首次解析後，SQL 文本、解析樹、執行計畫存放於 SGA Shared Pool (Library Cache)，供所有 Session 重用，避免重複硬解析。

共享條件 (嚴格完全匹配)

1. 字元級完全相同：大小寫、空白、換行、註解皆須一致。
   `SELECT * FROM EMP` ≠ `select * from emp`
2. 參考物件完全相同：同名表若屬不同 Schema (Private Synonym vs Table Owner)，不共享。Public Synonym 同義詞指向同一物件則共享。
3. 綁定變數名稱完全相同：`:b1` vs `:b2` 視為不同 SQL，即使執行時值相同。

調優建議

• 應用程式統一 SQL 寫法規範 (大寫關鍵字、固定縮排、使用綁定變數)。
• 適度增大 `SHARED_POOL_SIZE`，監控 `V$LIBRARYCACHE.RELOADS` (硬解析率)。
• 使用 `CURSOR_SHARING=FORCE` (僅作臨時緩解) 強制字面值轉綁定變數，但可能產生次優計畫。
• 多表連接查詢不支援簡單表的快取緩衝 (Cache Buffering) 優化。

本章小結

掌握優化器模式選擇、理解 FTS 與 Index Access 成本模型、遵循共享游標嚴格匹配規則，是 SQL 調優的基石。下一章將深入探討連接順序、WHERE 子句排序、避免 `SELECT *`、減少資料庫往返等實戰技巧。