共享池之九：繫結變數與session_cached_cursors

解析分為硬解析和軟解析和軟軟解析，SQL語句第一次解析時必須進行硬解析

還有一種是結果集快取—無解析，詳見：結果集快取

一句話說明硬解析與軟解析的區別是：

硬解析=需要生成執行計劃   軟解析=不需要生成執行計劃

在Oracle中存在兩種型別的SQL語句，一類為DDL語句，不共享使用，也就是每次執行都需要進行硬解析。還有一類就是DML語句，會進行硬解析或軟解析。

硬解析變成軟解析：繫結變數

軟解析變成軟軟解析：設定session_cached_cursors，詳見本文最後。

 

軟軟解析引數session_cached_cursors的參考值

select owner,name from v$db_object_cache where child_latch=1;顯示library cache中那些物件被快取，以及物件的尺寸

session_cached_cursors佔用的記憶體：會話退出會釋放，或者達到達到最大值後，最早、早少使用的會被釋放。

session_cached_cursors引數的設定： --來自周亮--ORACLE DBA實戰攻略。

SYS@ bys3>show parameter session_cached  11G中預設值是50

NAME                                TYPE        VALUE

-----------------------------------------------------------------------------

session_cached_cursors              integer    50

SYS@ bys3>select a.name,b.value from v$statname a,v$sesstat  b where a.statistic#=b.statistic# and a.name in('session cursor cache hits','session cursor cache count','parse count (total)') and  b.sid=(select c.sid from v$mystat c where rownum=1);

NAME                                                                 VALUE

--------------------------------------------------------------------------

session cursor cachehits                                               32

session cursor cachecount                                               4

parse count(total)                                                     43

session cursor cache count表示指定會話快取的遊標數，session_cached_cursors引數是系統當前每個會話最多能快取的遊標數。

session cursor cache count小於session_cached_cursors，不用增加session_cached_cursors大小。如相等，則有可能需要增加。

session cursor cache hits 表示從UGA中命中的次數--軟軟解析次數。

parse count (total)指定會話的總解析次數。

如果session cursor cache hits接近parsecount (total)，無需調整session_cached_cursors。

如果session cursor cache hits遠小於parsecount(total)，則可能需要調整session_cached_cursors。

session_cached_cursors對所有會話生效，如果需要調優的會話佔所有會話比例很小，調整意義不大。

--注意session_cached_cursors是靜態引數，調整後要重啟庫才生效。

 SYS@ bys3>alter system set session_cached_cursors=11;
alter system set session_cached_cursors=11
                 *
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this option

 繫結變數

oracle 能夠重複利用執行計劃的方法就是採用繫結變數。

繫結變數的實質就是用於替代sql語句中的常量的替代變數。繫結變數能夠使得每次提交的sql語句都完全一樣。

繫結變數只是起到佔位的作用，同名的繫結變數並不意味著在它們是同樣的，在傳遞時要考慮的是傳遞的值與繫結變數出現順序的對位，而不是繫結變數的名稱。

繫結變數是在通常情況下能提升效率，非正常的情況如下：

在欄位(包括欄位集)建有索引，且欄位(集)的集的勢非常大（也就是有個值在欄位中出現的比例特別的大）的情況下，使用繫結變數可能會導致查詢計劃錯誤，因而會使查詢效率非常低。這種情況最好不要使用繫結變數。

但是並不是任何情況下都需要使用繫結變數，下面是兩種例外情況：

1．對於隔相當一段時間才執行一次的SQL語句，這是利用繫結變數的好處會被不能有效利用優化器而抵消

2．資料倉儲的情況下。

繫結變數不能當作嵌入的字串來使用，只能當作語句中的變數來用。不能用繫結變數來代替表名、過程名、欄位名等.

從效率來看，由於oracle10G全面引入CBO，因此，在10G中使用繫結變數效率的提升比9i中更為明顯。

繫結變數窺視：Bind Peeking--欄位分佈均勻時有利

Bind Peeking 就是當在WHERE條件中使用繫結變數的時候，CBO會根據第一次使用的真實變數值來生成一個執行計劃。在這個cursor的整個生命週期中，CBO不會再對相同的SQL進行hardparse。

優點：如果索引欄位的值是均勻分佈的，hardparse就降低了，效能提高。

缺點：如果欄位分佈不均勻，並且第一次使用值不具有普遍性，那麼執行計劃就將非常糟糕。

 

Oracle11g 提供了一個新特性，AdpativeCursorSharing，或者叫 Extended Cursor Sharing，來解決這個問題。他的核心思想是，當某個欄位的histogram提供了資料不均勻的資訊，CBO會在實際使用不同值的時候，嘗試重新生成更合適的執行計劃。

通過隱含的引數來調整資料庫預設的bind peeking行為：_OPTIM_PEEK_USER_BINDS。

關閉Bind Variable Peeking，可以設定該引數為False ----要重啟資料庫。

SQL>alter sessionset"_optim_peek_user_binds"=false

 

繫結變數分級--bind graduation及bind-mismatch導致高版本問題

bind_mismatch一般是由於bind value的長度不同導致bindbuffer無法重用，最終導致cursor無法重用。
根本的原因在於：Oracle資料庫引擎應用了繫結變數分級，即根據繫結變數的長短劃分為4級，比如varchar2(32)和varchar2(33)屬於不同級別。
四個級別的劃分是：
0~32
32~128
129~2000
2001~
當表上有數十上百個varchar2型別的列時候，會比較常見因為bind graduation導致的bind-mismatch,即產生N多無法共享的子游標。
子游標過多會對SQL parse有影響。
針對一些特別的表或者查詢列特別多的SQL,可以通過給字串變數繫結固定的長度，如to_char(4000),來避免因為bind graduation導致child cursor過多的問題．

例 如:對於字元型別的欄位，進行繫結變數的時候，第一次會使用32位元組的BUFFER，如果該值小於32位元組的話，第二次執行這個SQL的時候，如果小於 32位元組，那麼可以共享這個CURSOR,如果大於，就無法共享，原因就是BIND_MISMATCH，此時會產生一個子CURSOR，同時分配128位元組的BIND BUFFER，以此類推。
select count(*) from v$sql_shared_cursor where sql_id='9rwd4wkwm4bsy' andBIND_MISMATCH='Y' ;
 COUNT(*)
----------
  120
 
可以通過v$sql_bind_capture檢視檢視一下每次繫結變數的值：
select position,LAST_CAPTURED,datatype_string,value_string fromv$sql_bind_capture where sql_id='9rwd4wkwm4bsy' and rownum<50;

正常情況不會產生這麼大量的子CURSOR。但是由於一些BUG，會導致問題。
如果沒有補丁，一個臨時性的解決方案，設定一個較大的BUFFER
SQL>ALTER SESSION SET EVENTS '10503 trace name context level<bufferlength>, forever';
 
而這些具體的內容，可以通過檢視來檢視。檢視上述的共享父遊標對於的bind_metadata
selects.child_number,m.position,m.max_length,
decode(m.datatype,1,'VARCHAR2',2,'NUMBER',m.datatype)as datatype
from v$sql s,v$sql_bind_metadata m
where s.sql_id='94ka9mv232yxb'
and s.child_address=m.address;
CHILD_NUMBER   POSITION MAX_LENGTH DATATYPE
------------ ---------- ----------------------------------------------
         0         3         32 VARCHAR2
         0         2         32 VARCHAR2
         0         1        22 NUMBER
         1         3         32 VARCHAR2
         1         2        128 VARCHAR2
         1         1         22 NUMBER
從繫結變數四個級別來看，最多生成4個子遊標。但是為什麼AWR報表中SQL ordered by Version Count的version count能高達好幾十，甚至幾百呢？而且不能共享的原因都是因為bind_mismatch.

關於:cursor_sharing

根據oracle官方建議在11g中不推薦使用cursor_sharing=SIMILAR,其實在所有版本中都不推薦,設定為該值很容易導致高版本問 題.而且該值會出現莫名其妙的,無法解釋的高版本問題.而且根據oracle相關文件,在即將釋出的12c版本中,將除掉SIMILAR值.對於客戶庫的 該問題,因為很多sql未繫結引數,為了減少硬解析,建議在業務低谷時設定cursor_sharing=FORCE,並重新整理sharedpool.

如果cursor_sharing 引數是設定為similar的，這樣會將SQL 中的謂詞值自動用變數來代替。這樣會增加cursor的數量。
為了減少cursor對library cache的佔用，先將cursor_shring 引數改成了預設的exact模式。
這樣version_count 會減少很多，但是硬解析的次數也會增加，可能會增加Library Cache Latch等待。
cursor_sharing的實驗可以參考DAVE的部落格：http://blog.csdn.net/tianlesoftware/article/details/6551723