Oracle9i,10g,11g 使用繫結變數的區別及與cursor_sharing的關係[final]

繫結變數的好處，大家都比較熟悉，使用繫結變數可以限制需要儲存在庫快取
中返回相似結果集的SQL語句遊標的數量。也就是為了減少硬解析。但是並不是任
何時候使用繫結變數都是最優的。有時候會出現繫結變數使效能變差的情況。

 

1. 掃描成本和OPTIMIZER_INDEX_COST_ADJ

在CBO模式下，Oracle會根據統計資訊計算各個訪問路徑的代價，採用最小代價的訪問路徑
作為語句的執行計劃。而對於索引的訪問代價的計算，需要根據一個系統引數OPTIMIZER_INDEX_COST_ADJ
來轉換為與全表掃描代價等價的一個值。

這是什麼意思呢？我們先稍微解釋一下這個引數：OPTIMIZER_INDEX_COST_ADJ。它的值是一
個百分比，預設是100，取值範圍是1～10000。當估算索引掃描代價時，會將索引的原始代
價值乘以這個百分比，將換算後的值作為與全表掃描代價比較的值。也就是說，當這個值為
100時，計算出的索引掃描代價就是它的原始代價：
COST (Index Scan)= COST_ORIGINAL (Index Scan) * OPTIMIZER_INDEX_COST_ADJ/100

在使用繫結變數時，引數OPTIMIZER_INDEX_COST_ADJ對於是否選擇索引會有重要的影響。在
Oracle9i之前使用繫結變數不會將繫結變數的值考慮進去，而是計算出平均成本。

特別是類似索引欄位的集的勢非常高的時候(比如1000筆記錄，995筆為'A',2筆為'B',3筆
為'C')，平均代價與實際掃描某個值代價相差非常遠。這種情況下，OPTIMIZER_INDEX_COST_ADJ
對不使用繫結變數查詢影響就非常小（因為索引代價不是比全表掃描成本大很多就是小
很多），不管掃描哪個值，不使用繫結變數將更加容易選擇到合理的查詢計劃。

 

 

2.  繫結變數窺視(Bind Variables Peeking)及cursor_sharing

在瞭解了引數OPTIMIZER_INDEX_COST_ADJ的作用後，再瞭解一個對查詢計劃，特別是使用
繫結變數時會產生重大影響的特性： 繫結變數窺視（Bind Variables Peeking）。

繫結變數窺視是9i以後的一個新特性。它使CBO最佳化器在計算訪問代價時，將繫結變數傳入
的值考慮進去，從而計算出更合理的成本（否則，將會計算平均成本）。看下面例子：

此時OPTIMIZER_INDEX_COST_ADJ是60，根據上面的結論，似乎查詢計劃應該選擇掃描索引。
但是，這裡給繫結變數賦了值"A"，這時，最佳化器會“窺視”到這個值，並且在計算掃描成
本時按照這個值的成本來計算。因此，得出的查詢計劃是全表掃描，而不是掃描索引.

但是，繫結變數窺視對一條語句只會使用一次。就是說，在第一次解析語句時，將繫結變
量的實體值考慮進去計算成本生成查詢計劃。以後在執行該語句時不再發生Bind Variables Peeking，
而是都採用這個第一次執行時的執行計劃，不管繫結變數中賦予是什麼值 。

因此，這種情況下使用繫結變數也會導致無法選擇最優的查詢計劃。

綜上所述，在對建有索引的欄位（包括欄位集），且欄位（集）的集的勢非常大時(資料分
布非常不均勻)，使用繫結變數可能會導致查詢計劃錯誤。

------------------------------------------------------------------------
存在較多因素導致SQL語句不能在shared_pool中共享：
1. SQL文字大小寫不一致
2. SQL語句的繫結變數的型別不一致或長度有很大區別
3. SQL語句涉及的物件名稱雖然一致但是位於不同的schema下
4. SQL語句的最佳化模式不一致(比如新增hint,修改了optimizer_mode引數等)
------------------------------------------------------------------------

 

 

-----------------------------------
以下來自網路文章(來自itpub及it168)：
-----------------------------------
http://tech.it168.com/a2009/0108/262/000000262531_2.shtml

 

1, 使用文字常量，如select * from t1 where x=1;

這種情況缺點是如果使用不同常量，如select * from t1 where x=2; sql語句無法共享，
產生大量硬解析。耗費過多CPU.   這種情況的優點是，相對於使用繫結變數，使用常量
更容易得到比較合理的執行計劃。

 

2. 使用繫結變數，如select * from t1 where x=:b1; (:b1=1)

這種的優點是可以避免大量的硬解析，節省CPU.
這種的缺點是可能得到不好的執行計劃。
當Oracle對帶有繫結變數的SQL語句作解析的時候，如果這個語句是第一次執行，解析的
時候會做一次bind variable peeking，也就是根據當時繫結變數的值決定合適的執行計
劃。這個計劃對於這個繫結變數（:b1=1）自然是合適的。

但是如果後來我們再執行select * from t1 where x=:b1; (這次:b1=2)，很多情況下，
Oracle是不會再做bind variable peeking，而是直接使用b1=1的時候的執行計劃，那麼
現在的執行計劃不一定是合理的。

 

現在我們可以來看cursor_sharing這個引數。

1) exact : 只令完全相同的 SQL 語句共享一個遊標。（預設選項）

Only allows statements with identical text to share the same cursor.

這個設定是預設的，我覺得也是最好的。
這時候如果為了實現sql的共享，從應用程式的角度就需要使用繫結變數。但是如果
我們發現使用繫結變數會導致不太合適的計劃，應用就應該採用文字常量，當然這時
候在相應的列上使用柱狀圖也是必須的了。這個設定再結合好的應用，是最好的。

2) force: 強制執行和similar基本上一樣，但force會影響執行計劃。
Forces statements that may differ in some literals, but are otherwise
identical, to share a cursor, unless the literals affect the meaning of the
statement.

簡單的說，這時候oracle會把select * from t1 where x=1; 改寫成select * from t1
where x=:b1; (:b1=1)，這在無法修改應用的情況下確實是可以大量減少hard parse的，
但是也永遠的無法解決前面提到的繫結變數所固有的執行計劃不準確的問題。

3) similar: 允許相似的SQL可以共享一個遊標。
Causes statements that may differ in some literals, but are otherwise
identical, to share a cursor, unless the literals affect either the meaning
of the statement or the degree to which the plan is optimized.

當cursor_sharing=similar，我們執行select * from x=1; 這時候語句會被改寫為
select * from t1 where x=:b1; (b1=1), 然後CBO會做bind variable peeking，
得到計劃plan A，然後我們執行select * from x=2; 這時候語句會被改寫為select *
from t1 where x=:b1; (b1=2), 然後取決於x上有沒有收集柱狀圖，如果有收集，CBO
會重新生成新的計劃plan B。

那麼這三個設定那個更好，自然是看具體情況更合適的更好。不過如果應用可以修改
的話，我始終覺得exact是最好的。

cursor_sharing=similar的時候，library cache裡的結構不合理，會產生嚴重的
latch爭用。猜想這時候所有不同version的計劃都在一個sql下，從而受到同一個
latch的保護。

 

 

3. 11g新特性 - 自適應遊標共享 

Oracle 11g提供自適應遊標共享(ACS)以克服不該共享時的遊標共享，ACS使
用了兩個新的度量機制 ： 繫結敏感度和繫結感知。

繫結敏感度：  無論何時，當包含繫結變數的SQL語句首次執行時，最佳化器在
偷窺了繫結變數的值後，會為其標記一個繫結敏感度，以確定語句的謂詞，但
偷窺結束時也類似，因為它也為後面相同語句相同繫結變數不同值時進行對比，
以確定是否要產生新的執行計劃。

自適應遊標共享後設資料 ：Oracle 11g提供了三個新的檢視，並在v$sql檢視中
新增了兩個新列以便讓Oracle DBA確定最佳化器是否已經決定SQL語句是否適合自
適應遊標共享，最佳化器使用業務規則將SQL語句的執行計劃進行分類以便共享：

自適應遊標共如果享檢視：

V$SQL  --  增加了兩列，IS_BIND_SENSITIVE, 表示SQL語句是否繫結敏感，如果
這一列值是Y,意味著最佳化器已經偷窺了繫結變數的值，以便確定每個謂詞的選擇。
IS_BING_AWARE列，表示最佳化器執行額外的語句後決定SQL語句的遊標是否有繫結感
知。

V$SQL_CS_HISTOGRAM -- Oracle11g使用分配的頻率決定SQL語句是否繫結敏感，
包括執行的次數，特別是子游標被執行的次數。

V$SQL_CS_SELECTIVITY -- 包括有關SQL語句謂詞的相對選擇性資訊，包括謂詞自身
及高值，低值範圍，這些值也稱為遊標的選擇性立方體。

V$SQL_CS_STATISTICS -- 列出自適應遊標是否被共享以及/或如何共享的統計資訊，
如果繫結設定已經用於構建自適應遊標，PEEKED列會顯示一個Y值。

繫結感知：  一旦SQL語句的遊標被標記為繫結敏感，最佳化器可能還會決定將其視為
繫結感知，最佳化器是透過檢查提供給繫結變數的值是否與相同查詢後面的執行計劃匹配
來實現的，如果最佳化器決定它可以使用現有的執行計劃，那就只需要更新遊標執行直方
圖以反應語句的執行情況，  換句話說，如果繫結變數值發了重大變化，最佳化器可能會
決定建立一個全新的子游標和執行計劃，如果是這樣的話，Oracle 11g也會儲存自適應
遊標共享後設資料中的子游標的相對選擇性。

我覺得它有助於把這些選擇性評級作為“電子雲”或影響範圍的中心點，Oracle文件
了使用的術語是“選擇性立方體”，在隨後遊標的執行過程中，最佳化器會使用遊標最
近執行的統計資訊與現有的選擇性統計資訊進行比較，如果它觀察到大多數執行都使
用系統的選擇性範圍，遊標將會被標記為繫結感知。

當繫結變數的值超出了現有繫結感知遊標影響的範圍時，執行包含這個繫結變數的查詢
會發生什麼?在語句的硬解析期間，最佳化器可能只會選擇擴大選擇範圍，以包括新的繫結
值，這是透過建立新的子游標結合這兩套繫結變數值，然後刪除舊的、範圍小的遊標來
實現的，顯然，這樣只會產生幾個的確需要的幾個子游標。

　　那麼如何啟用這一新功能呢?好訊息是在Oracle 11g中預設就已經啟動了，它完全
與CURSOR_SHARING初始化引數無關，這大大增加了在OLTP/DSS系統中SQL語句使用繫結
變數的機會。

　　對SQL計劃管理(SPM)的影響：如果你讀過我之前寫的SQL計劃管理方面的文章，你
可能會疑惑自適應遊標共享是否會影響SQL計劃管理捕獲和儲存SQL執行計劃到SQL管理
基礎庫中的功能，下面列出它們之間互動的摘要資訊：
　　如果初始化引數OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES被設定為TRUE以啟用自
動捕獲執行計劃，那麼帶有繫結變數的SQL語句也會被標記為啟用和接收執行計劃。

　　如果同一個語句構建了第二個執行計劃 – 並不是自適應遊標共享 – 那麼該計劃
只會簡單地新增到語句的計劃歷史中，但它不會立即被使用，因為SPM首先會要求校驗這
個新的執行計劃。

　　不幸的是，這意味著一個很好的執行計劃會被忽略，解決這個問題的一個好辦法是
將自動捕獲計劃設定為FALSE，然後在庫快取中將所有子游標捕獲到SMB中，這樣將會強
制所有子游標的計劃被標記為SQL計劃基線。

 結語
　　Oracle 11g的新特性自適應遊標共享為包含有繫結變數的SQL語句有效共享執行計
劃提供了一個更簡單的方法，但只有繫結變數有值時才有意義，自適應遊標共享有時也
會產生新的執行計劃，但共享的遊標會保持相對小的數量。