萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

深度解析 Oracle 優化器為何選擇(或不選擇)索引:RBO vs CBO 決策邏輯全圖譜

SQL 執行計劃的誕生:九步曲

從語法分析到運行執行計劃,第 8 步「選擇數據搜索路徑」決定是全表掃描還是索引訪問。這一步的核心仲裁者是優化器。

兩代優化器:RBO 與 CBO

優化器模式參數:optimizer_mode

SHOW PARAMETER optimizer_mode;
-- 預設 CHOOSE:有統計信息用 CBO,無則退回 RBO
-- RULE:強制 RBO
-- FIRST_ROWS_n / ALL_ROWS:CBO 目標模式

索引失效的六大「元兇」排查清單

1. 優化器模式錯配

表已分析但 `optimizer_mode=RULE`,或表未分析但模式為 `CHOOSE`/`ALL_ROWS` 導致退回 RBO。對策:統一模式為 `ALL_ROWS`,定期收集統計信息。

2. WHERE 條件未包含索引前導列

組合索引 `(a,b,c)`,查詢 `WHERE b=1` 無法使用索引範圍掃描(可做 Index Full Scan,效率低)。鐵律:前導列必須出現在等值/範圍條件中。

3. 連接方式不匹配

  • Nested Loops (NL):內表有索引時極其高效。
  • Sort Merge Join (SMJ):索引僅能省去排序,不能加速訪問。
  • Hash Join (HJ):索引幾乎無用,依賴哈希表。

對策:檢查執行計劃連接方式,必要時用 `/*+ USE_NL(t2) */` Hint 強制 NL。

4. 連接順序導致外表全掃

NL 連接中,外表(驅動表)採用全表掃描。若大表作外表、小表有索引作內表,索引無法發揮作用。對策:收集準確統計信息讓 CBO 選擇小表驅動大表,或用 `/*+ LEADING(small_table) */` 指定順序。

5. 涉及系統字典表/視圖

字典表未分析,CBO 可能生成極差計劃。禁忌:切勿手動分析字典表(`SYS`/`SYSTEM` 模式),會導致死鎖或性能崩塌。

6. 索引列參與函數運算

`WHERE UPPER(name) = 'ABC'` 導致普通 B*Tree 索引失效。對策:建立函數索引 `CREATE INDEX idx_upper_name ON emp(UPPER(name));` 或改寫 SQL。

現代最佳實踐

  1. 統計信息自動化:11g+ 啟用 `DBMS_STATS.AUTO_STATS_ADVISOR`,維護窗口自動收集。
  2. SQL Plan Management (SPM):固化優秀計劃,防止統計信息波動導致計劃退化。
  3. 實時 SQL 監控:`V$SQL_MONITOR` 觀察長時間運行 SQL 的實時執行計劃與等待事件。

結語

索引不被使用,90% 以上源於統計信息缺失/過期、SQL 寫法不當、或優化器模式配置錯誤。建立「收集統計 → 捕獲基線 → 監控偏離」的閉環機制,比單純加 Hint 更根本、更持久。

copyright © 萬盛學電腦網 all rights reserved
維度RBO (Rule-Based Optimizer)CBO (Cost-Based Optimizer)
引入版本Oracle 6Oracle 7
決策依據固定規則集(如:有索引必走索引)統計信息計算 Cost,選最低成本
數據敏感度不敏感(不管數據分布)高度敏感(依賴統計信息)
現狀已棄用,不再發展唯一主流,持續演進