在Oracle中，使用者應用輸入的SQL語句要進行所謂的Parse解析過程，用于生成執行計劃，這也就是Query Optimizer的主要工作。在Parse中，有兩種具體類型，被稱為“hard parse”（硬解析）和“Soft parse”（軟解析）
“實作執行計劃shared cursor共享，減少硬解析”是我們OLTP系統優化一個重要方向。但是，讓Oracle真正實作SQL共享不是一件容易的事情，受到很多其他因素的影響。最常用的方式是使用綁定變量，讓SQL字面值保持一緻。如果應用端沒有使用綁定變量，一種做法是設定系統參數cursor_sharing，将SQL語句中的條件進行綁定變量替換。本篇将從cursor_sharing可選值含義入手，讨論分析幾種取值的确切含義和應用場景。以及為什麼很多資料中都是對cursor_sharing設定望而卻步
1.實驗環境
我們在Oracle 11g下準備一個相對偏值的資料表。
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
在Oracle 11g裡，預設cursor_sharing取值為EXACT，表示不開啟SQL字面取值綁定變量替換功能。
SQL> show parameter cursor_sharing
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT
使用腳本生成資料表資料
SQL> create table t (id1 varchar2(10), id2 varchar2(10), id3 varchar2(10));
Table created.
SQL> create index idx_t_id1 on t(id1);
Index created.
SQL> select object_id from dba_objects where owner='ZYSCM' and object_name='T';
OBJECT_ID
----------
170516
SQL> select id1, count(*) from t group by id1;
ID1 COUNT(*)
---------- ----------
P 11
D 22
A 7
SQL>
2、 統計量收集
這裡單獨談談統計量收集的問題。在Oracle統計量中，通常選擇直方圖histogram進行偏度描述。
注意：在Oracle 9i中，直方圖預設使用dbms_stats是不會收集的，需要手工的制定method_opts參數。在Oracle 10g之後，使用“column auto”作為method_opts參數的預設取值
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true,method_opt => 'for all columns size auto');
PL/SQL procedure successfully completed.
SQL> select column_name, num_distinct, NUM_BUCKETS, HISTOGRAM from dba_tab_col_statistics where owner='ZYSCM' and table_name='T';
COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM
------------------------------ ------------ ----------- ---------------
ID1 3 1 NONE
ID2 3 1 NONE
ID3 3 1 NONE
SQL>
注意，預設是沒有生成直方圖的。主要原因在于需要使用一次id1作為條件列。
//使用一次條件列；
SQL> select count(*) from t where id1='D';
COUNT(*)
----------
22
//重新收集一下統計量；
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true,method_opt => 'for all columns size auto');
PL/SQL procedure successfully completed.
//發現統計量收集
SQL> select column_name, num_distinct, NUM_BUCKETS, HISTOGRAM from dba_tab_col_statistics where owner='ZYSCM' and table_name='T';
COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM
------------------------------ ------------ ----------- ---------------
ID1 3 3 FREQUENCY
ID2 3 1 NONE
ID3 3 1 NONE
SQL>
當我們使用過一次id1條件之後，再次手動統計量，使用預設的auto參數，就生成id1列的頻度直方圖。
這裡也就揭示了Oracle在收集統計量直方圖auto選項的含義。當我們指定auto之後，Oracle會自動判斷是否對資料列生成直方圖、生成直方圖bullet的個數。如果這個列從來就沒有出現在SQL條件列中，也就不會被收集直方圖。
3、EXACT——不進行條件列替換
EXACT是cursor_sharing參數的預設選項，表示含義是不進行SQL條件自動綁定變量替換。
SQL> show parameter cursor_sharing
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT
SQL> alter system flush shared_pool;
System altered.
我們發出兩句SQL，分别使用資料取值差異很大的id1值。
SQL> select count(*) from t where id1='P';
COUNT(*)
----------
11
SQL> select count(*) from t where id1='A';
COUNT(*)
----------
7
此時，父子遊标library cache中情況如下：
SQL> select sql_text, sql_id, version_count, executions from v$sqlarea where sql_text like 'select %';
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
------------- ----------- ----------- ---------
select count(*) from t where id1='P' 1fu450v8ddmv9 1 1
select count(*) from t where id1='A' 16jfmsdnc6as0 1 1
在EXACT下，不會發生SQL字面值改寫的情況。如果兩個SQL的其他部分相同，隻是where條件的取值有差異，Oracle是會将這兩個語句作為兩個單獨SQL進行硬解析，分别生成執行計劃。
4、 FORCE——強制共享執行計劃
預設值EXACT的作用是不對非字面SQL綁定變量進行替換操作。而FORCE值和SIMILAR取值意味着Oracle需要對輸入的SQL語句進行處理，首先就是對條件值進行綁定變量化，其次就是針對不同的取值采用不同的執行計劃共享政策。
當選擇FORCE值的時候，意味着Oracle會對SQL字面值進行綁定變量處理。一個語句形成父遊标和僅有的一個子遊标。子遊标執行計劃通過Oracle binds peeking技術實作，以後所有類似形态的SQL都是先共享。
SQL> alter system flush shared_pool;
System altered.
SQL> alter session set cursor_sharing='FORCE';
Session altered.
SQL> show parameter cursor_sharing
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
cursor_sharing string FORCE
将目前會話的cursor_sharing設定為force，同時清空library cache。之後使用三條SQL語句進行試驗。
SQL> select count(*) from t where id1='D';
COUNT(*)
----------
22
SQL> select count(*) from t where id1='P';
COUNT(*)
----------
11
SQL> select count(*) from t where id1='A';
COUNT(*)
----------
7
如果在EXACT取值的時候，三個執行語句一定會生成三個父遊标和三個子遊标的。每一個遊标對應一個單獨的執行計劃。第一和第二條SQL對應全表掃描FTS方案較好，而第三條SQL顯然索引路徑較優。我們看看在FORCE取值的時候，生成計劃情況如何呢？
SQL> select sql_text, sql_id, version_count, executions from v$sqlarea where sql_text like '%%';
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
-------------------------------------------------------------------------------- ------------- ------------- ----------
select count(*) from t where id1=:"SYS_B_0" 2yczsvd6tcjuj 1 3
此時，我們觀察到三次執行之後SQL遊标共享情況。首先，三次的SQL語句從字面值上完全不同，差異隻是存在在條件id1取值上。如果在cursor_sharing為EXACT模式下，是不能實作遊标共享的。設定為FORCE之後，我們發現Oracle自動将id1=後面的條件替換為綁定變量。三次SQL調用均使用相同的父遊标，而子遊标隻存在一個，意味著三次調用均是使用這個唯一的子遊标。一個子遊标對應一個執行計劃，三個SQL使用相同的執行計劃。
5、SIMILAR——另一個極端
剛剛我們讨論了FORCE。在FORCE下，問題是很簡單的：進行綁定變量替換，共享全部遊标。但是這樣對于資料分布不均衡的條件列來說，是存在很多問題的。實際環境中會出現SQL性能時好時壞的情況。作為另一個極端，我們設定SIMILAR取值。
SQL> alter system set cursor_sharing='SIMILAR';
System altered.
SQL> show parameter cursor_sharing;
NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
cursor_sharing string
SIMILAR
為了更容易看清現象，我們使用逐漸試驗的方法：
--Invoke SQL 1
SQL> select count(*) from t where id1='P';
COUNT(*)
----------
11
SQL> select sql_text, sql_id, version_count, executions from v$sqlarea where sql_text like '%%';
SQL_TEXT
--------------------------------------------------------------------------------
SQL_ID VERSION_COUNT EXECUTIONS
------------- ------------- ----------
select count(*) from t where id1=:"SYS_B_0"
f0rpwa1ja706p 1 1
第一次調用SQL語句，使用條件值D。在library cache中生成了父子遊标，而且同FORCE一樣，進行了綁定變量替換。下面進行第二次調用：
SQL> select count(*) from t where id1='A';
COUNT(*)
----------
7
SQL> select sql_text, sql_id, version_count, executions from v$sqlarea where sql_text like '%%';
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
-------------------------------------------------------------------------------- ------------- ------------- ----------
select sql_text, sql_id, version_count, executions from v$sqlarea where sql_text f0rpwa1ja706p 2 2
like '%%'
在使用一個新值A的情況下，生成了一個新的子遊标（version_count=2）
6、結論
cursor_sharing的取值和參數是Oracle library cache中管理生成乃至共享執行計劃的重要參數。EXACT值是預設值，實作了直接使用字面SQL不開啟轉變綁定變量的功能。
而FORCE和SIMILAR取值卻開啟了字面轉綁定變量的功能。在這兩個模式下，Oracle會自動的将where後面的條件替換為綁定變量，以增加SQL共享的機率。具體實作sharing的方式上，FORCE和SIMILAR取值又有所差異。
FORCE的sharing原則是共享一切，隻生成一個子遊标，之後所有都去共享這個子遊标的執行計劃。随之而來的就是bind peeking問題風險。
而SIMILAR過于謹慎，對每一個SQL都進行類似bind peeking操作。對每個可能取值都生成單獨的子遊标執行計劃。相同的輸入共享相同的執行計劃。這個雖然避免了bind peeking問題，卻帶來了新的多version count問題。
從EXACT到FORCE到SIMIlAR，到Oracle 11g中推出的ACS（Adaptive Cursor Sharing），Oracle一直試圖去實作cursor sharing的自動化和高效化。過去，隻能通過手工顯示綁定變量來實作SQL共享最大化的目标。而手工書寫的大部分SQL由于字面值的原因很難共享。cursor_sharing參數的作用就是進行這方面的嘗試，雖然從目前看還是有一些問題，但是已經進行了有益的嘗試。
Oracle 11g中推出的ACS自适應遊标，将遊标共享的标準從SQL字面值相同，綁定變量Peeking值相同，拓展到執行計劃相同。在不斷的自适應嘗試過程中，Oracle ACS最終會确定适合的共享方案和執行計劃。
![oracle隐式遊标和顯示遊标[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)