Oracle SQL 效能優化系列講座（一）：優化器、存取路徑與共享游標

1. 選用適合的優化器

Oracle 提供三種優化器模式：

• RULE (RBO)：基於規則，依固定優先順序選擇存取路徑。
• COST (CBO)：基於成本，依統計資訊估算各路徑代價選擇最優。
• CHOOSE：若表有統計資訊用 CBO，否則用 RBO。

透過 init.ora 的 OPTIMIZER_MODE 設定預設值（RULE、COST、CHOOSE、ALL_ROWS、FIRST_ROWS），亦可在 SQL 或 Session 層級覆蓋（/*+ RULE */、ALTER SESSION SET OPTIMIZER_MODE=...）。

建議：預設 CHOOSE 易導致非預期全表掃描，生產環境宜明確指定 ALL_ROWS（吞吐量優先）或 FIRST_ROWS_n（回應時間優先），並定期執行 DBMS_STATS.GATHER_SCHEMA_STATS 維護統計資訊。

2. 表存取方式

索引建立在高選擇性欄位上，可將 ROWID 快速定位轉化為效能優勢。避免在低選擇性欄位（如性別、狀態碼）建立單欄索引。

3. 共享 SQL（Shared Pool / Library Cache）

Oracle 將解析後的 SQL 存放於 SGA 共享池，所有 Session 可重用。若新 SQL 與現有完全相同（含大小寫、空白、換行），即可共享執行計畫，減少解析開銷。

共享的三大必要條件

1. 字元級完全相同：SELECT * FROM EMP; 與 select * from emp; 視為不同。
2. 參考物件完全相同：同義詞、權限路徑必須一致。例：User A 用私有同義詞 sal_limit，User B 用公有同義詞同名物件，雖名稱相同但物件不同，無法共享。
3. 繫結變數名稱相同：:blk1.pin 與 :blk1.ot_ind 即使執行時值相同，也不共享。

最佳實務建議

• 統一 SQL 大小寫、縮排風格，建立編碼規範。
• 善用繫結變數，避免硬解析風暴。
• 監控 V$SQLAREA、V$SQL_SHARED_CURSOR 找出不共享原因。
• 適當調大 SHARED_POOL_SIZE，但避免過大導致管理開銷。