Oracle 9i+ 綁定變量窺探 控制參數 _optim_peek_user_binds 詳細解析

什麼是 Bind Variable Peeking？

自 Oracle 9i 起，優化器在解析 SQL 時會「窺探」首次執行時的綁定變量實際值，以生成最優執行計劃。此功能對於數據傾斜場景極其有用，能根據實際值選擇索引或全表掃描。

潛在副作用

• 首次執行值具代表性 → 計劃最優。
• 首次執行值非典型 (如極小/極大值) → 產生不佳計劃，後續執行沿用該計劃，導致效能災難。
• 計劃在共享池中被複用，無法適應不同綁定值的數據分布差異。

控制參數：_optim_peek_user_binds

查詢參數當前值：

SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc DESCRIPTION
FROM sys.x$ksppi x, sys.x$ksppcv y
WHERE x.inst_id = USERENV('Instance')
  AND y.inst_id = USERENV('Instance')
  AND x.indx = y.indx
  AND x.ksppinm LIKE '%peek%';

輸出範例：

NAME                      VALUE      DESCRIPTION
------------------------- ---------- ------------------------------
_optim_peek_user_binds    TRUE       enable peeking of user binds

設定建議

修改方式 (需重啟實例生效)：

ALTER SYSTEM SET "_optim_peek_user_binds"=FALSE SCOPE=SPFILE;
-- 或在 init.ora / spfile 中添加
_optim_peek_user_binds=FALSE

替代方案 (不關閉窺探)

• 使用 OPT_PARAM('_optim_peek_user_binds','FALSE') Hint 單獨控制該 SQL。
• 收集直方圖統計資訊，讓優化器在不窺探情況下也能識別傾斜。
• 使用 DBMS_STATS.SET_TABLE_PREFS 設定 METHOD_OPT 針對傾斜欄位收集直方圖。
• Oracle 11g+ 引入 Automatic Cursor Sharing (ACS) 與 Adaptive Cursor Sharing，可自動為不同綁定值生成多個子計劃，大幅緩解此問題。

總結

Bind Peeking 是把雙面刃。預設開啟利於大多數場景，但在嚴重傾斜且首次值不具代表性時，可考慮關閉或升級至 11g+ 利用 ACS 機制自動處理。