Oracle 效能調優技巧全集：優化器選擇、存取路徑與 SQL 共享機制

一、優化器模式抉棄：拒絕 CHOOSE 預設陷阱

Oracle 提供三種優化器：RULE（規則）、COST（成本）、CHOOSE（選擇性）。預設 CHOOSE 模式會根據表是否被 ANALYZE 自動切換 CBO/RBO，極易導致非預期的全表掃描。

• 建議：在 init.ora 明確設定 OPTIMIZER_MODE=RULE 或 COST，並在 Session/SQL 層級按需覆蓋
• 使用 CBO 必須定期執行 ANALYZE 更新統計資訊，否則退回 RBO

二、表存取雙路徑：全表掃描 vs ROWID 索引存取

索引建立在高選擇性欄位上，可將邏輯 I/O 降低數量級。

三、共享 SQL 機制：減少硬解析的關鍵

運作原理

首次解析後，SQL 文本、執行計劃存入 SGA Shared Pool。後續相同語句直接復用，省去解析與優化開銷。

共享三鐵律（嚴格字元級比對）

1. 字元完全相同：大小寫、空白、換行皆須一致
   SELECT * FROM EMP; ≠ SELECT * FROM emp;
2. 引用物件完全相同：同義詞、擁有者解析結果一致
   例：User A 用 Private Synonym、User B 是 Table Owner → 無法共享
3. 繫結變數名稱相同：:blk1.pin vs :blk1.ot_ind 視為不同語句

DBA 調優建議

• 在 init.ora 適度調大 SHARED_POOL_SIZE，提升命中率
• 應用端強制使用繫結變數，避免字面值 SQL 洪水
• 統一 SQL 格式規範（大寫關鍵字、固定縮排），納入 Code Review

四、實戰檢核清單

1. 確認優化器模式非 CHOOSE
2. 高頻 SQL 確認使用索引存取（EXPLAIN PLAN 驗證）
3. Shared Pool 命中率 > 90%（V$LIBRARYCACHE 監控）
4. 消除字面值 SQL，全面採用 Bind Variable

結語

優化器模式鎖定、索引存取驗證、共享池管理構成 Oracle 效能基石三角。將這些原則內化為開發規範，勝過事後百倍調優成本。