萬盛學電腦網

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

如何選擇與配置 Oracle 優化器模式

Oracle 提供三種優化器模式,正確選擇與配置對 SQL 執行效能影響深遠。本文解析三種模式特性、配置方式及最佳實踐。

三種優化器模式

模式全稱特性
RULERule-Based Optimizer (RBO)基於固定規則集,不依賴統計資訊,行為可預測但無法適應資料分佈變化。
COSTCost-Based Optimizer (CBO)基於統計資訊計算執行計畫成本,選擇最低成本路徑,需定期執行 ANALYZE 更新統計資訊。
CHOOSEChoose預設模式。若表有統計資訊則用 CBO,否則退回 RBO。易導致非預期的全表掃描。

配置方式

在 init.ora 設定 OPTIMIZER_MODE 參數:RULE、COST、CHOOSE、ALL_ROWS、FIRST_ROWS。亦可在 SQL 或 Session 層級覆寫:

ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS;
SELECT /*+ FIRST_ROWS */ * FROM emp;

避免 CHOOSE 陷阱

預設 CHOOSE 模式下,若統計資訊缺失或過舊,優化器會退回 RBO,導致全表掃描。建議:

  • 明確設定為 COST 或 RULE,避免隱性切換;
  • 建立定期 ANALYZE / DBMS_STATS 排程,確保統計資訊準確;
  • 關鍵 SQL 使用 Hint 強制指定優化目標(ALL_ROWS 全表最優、FIRST_ROWS 快速回應首筆)。

資料存取路徑

全表掃描

循序讀取所有區塊,Oracle 以多區塊讀取優化。適用於小表或需讀取大部分資料的查詢。

ROWID 存取

透過 ROWID 直接定位資料物理位置。索引提供快速取得 ROWID 的路徑,基於索引欄位的查詢可大幅提升效能。

共享 SQL 與 Cursor 重用

Oracle 將解析後的 SQL 存放於 SGA 共用池,相同語句可重用執行計畫,減少解析開銷與記憶體佔用。

共享條件(三個必須)

  1. 字元級完全相同: 包含大小寫、空白、換行。SELECT * FROM EMP 與 select * from emp 視為不同。
  2. 參照物件完全相同: 同義詞、擁有者解析結果須一致。不同使用者透過私有/公共同義詞存取同一物件可能無法共享。
  3. 繫結變數名稱相同: :blk1.pin 與 :blk1.ot_ind 雖執行時值相同,但名稱不同,無法共享。

調整共用池大小

在 init.ora 設定 SHARED_POOL_SIZE,池越大可快取越多語句,命中率越高。

copyright © 萬盛學電腦網 all rights reserved