Oracle 優化器的三種模式
Oracle 資料庫提供三種優化器模式：RULE（基於規則）、COST（基於成本，CBO）以及CHOOSE（選擇性）。管理員可透過 init.ora 參數 OPTIMIZER_MODE 設定預設模式（RULE、COST、CHOOSE、ALL_ROWS、FIRST_ROWS），亦可在 SQL 或 Session 層級覆寫。
CBO 的前提條件
若要啟用 CBO，必須定期執行 ANALYZE 指令收集物件統計資訊。若模式設為 CHOOSE，Oracle 會依據資料表是否已分析自動切換：已分析則用 CBO，否則退回 RULE。預設採用 CHOOSE，但為避免不必要的全表掃描，建議明確指定 RULE 或 COST。
資料存取路徑：全表掃描 vs ROWID 存取
- 全表掃描：循序讀取每一筆記錄，Oracle 以多區塊讀取優化效能。
- ROWID 存取：透過索引快速定位 ROWID，再直接讀取資料區塊，大幅提升查詢速度。
共享 SQL 與 Shared Pool
Oracle 將解析後的 SQL 存放於 SGA 的 Shared Pool，供所有使用者重用。共用需滿足三條件：字元級完全相同（含空白、換行）、參考物件完全相同（同義詞、擁有者一致）、綁定變數名稱相同。適當調大 SHARED_POOL_SIZE 可增加命中率。
RBO 專屬：FROM 子句表順序與 WHERE 子句順序
RBO 以從右至左處理 FROM 子句，最後一張表為驅動表，宜選記錄最少者。WHERE 子句則自下而上解析，連接條件先寫，過濾性最強的條件放最後。
常見調優技巧彙總
- 避免 SELECT *：解析時需查資料字典轉換欄位名，增加開銷。
- 減少資料庫往返：合併多筆查詢、使用陣列繫結（建議 ARRAYSIZE=200）。
- 善用 DECODE/CASE：單次掃描完成多條件聚合，避免重複連接。
- 整合無關查詢：利用 OUTER JOIN + DUAL 將多個單表查詢合為一條。
- 刪除重複記錄：
DELETE WHERE ROWID > (SELECT MIN(ROWID) ...) 最有效率。
- TRUNCATE 替代 DELETE：不產生 Rollback，速度極快，但屬 DDL 無法回滾。
- 適度 COMMIT：釋放 Rollback Segment、鎖、Redo Log Buffer 等資源。
- COUNT(*) vs COUNT(1)：實測差異極小，若有索引欄位則
COUNT(index_col) 最快。
- WHERE 替代 HAVING：先過濾再分組，減少排序與聚合成本。
- 減少子查詢掃表次數：合併子查詢、使用多欄位 IN 比較。
- 函數封裝查詢：將查閱邏輯包裝為 PL/SQL 函數，減少連接開銷。
- 使用表別名：縮短解析時間，避免欄位歧義。
- EXISTS 替代 IN/NOT IN：半連接機制可提早終止掃描；NOT EXISTS 優於 NOT IN。
- JOIN 替代 EXISTS：RBO 下 NESTED LOOPS 效率優於 FILTER。
- EXISTS 替代 DISTINCT：一對多查詢時避免排序去重。
效能監控與分析工具
- V$SQLAREA：找出 Hit Ratio 低、Disk Reads 高的問題 SQL。
- SQL TRACE + TKPROF：收集執行統計，需先設
TIMED_STATISTICS=TRUE。
- EXPLAIN PLAN / AUTOTRACE：查看執行計畫，由內而外、由上而下閱讀；注意 NESTED LOOPS 的特殊順序。
索引策略與操作模式
索引採 B-tree 結構，適合大表查詢，但會增加 DML 開銷（每筆約 4-5 次額外 I/O）。定期 ALTER INDEX REBUILD 可維持效能。兩種掃描模式：INDEX UNIQUE SCAN（等值唯一索引）與 INDEX RANGE SCAN（範圍或非唯一索引）。前導萬用字元（如 LIKE '%ABC'）會導致索引失效，改為全表掃描。
驅動表選擇與多索引合併
- CBO 依統計資訊自動選擇最低成本路徑。
- RBO 下：若所有連接欄皆有索引，FROM 最後一張表為驅動表；否則選無索引者。
- 多索引時：唯一索引優先；同等級則依 FROM 順序（最後優先）或 WHERE 順序（最前優先）。
等值 vs 範圍比較的索引選擇
混合等值與範圍條件時，Oracle 僅使用等值索引，範圍條件改為事後過濾。若皆為範圍比較且無法判斷優劣，則採用 WHERE 最前的索引。
