上篇我們介紹了Oracle SQL共享遊標的機制，以及cursor_sharing引數EXACT取值作用。本篇我們繼續介紹cursor_sharing引數的其他兩個取值：FORCE和SIMILAR的實際含義。

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> select name, value from v$parameter where name='cursor_sharing';

NAME VALUE

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

cursor_sharing FORCE

將當前會話的cursor_sharing設定為force，同時清空library cache。之後使用三條SQL語句進行試驗。

SQL> select /*+ cursor_sharing_force_demo */ count(*) from t where id1='D';

COUNT(*)

----------

10000

SQL> select /*+ cursor_sharing_force_demo */ count(*) from t where id1='P';

COUNT(*)

----------

8000

SQL> select /*+ cursor_sharing_force_demo */ count(*) from t where id1='G';

COUNT(*)

----------

5

如果在EXACT取值的時候，三個執行語句一定會生成三個父遊標和三個子游標的。每一個遊標對應一個單獨的執行計劃。第一和第二條SQL對應全表掃描FTS方案較好，而第三條SQL顯然索引路徑較優。我們看看在FORCE取值的時候，生成計劃情況如何呢？

SQL> select sql_text, sql_id, version_count, executions from v$sqlarea where sql_text like '%/*+ cursor_sharing_force_demo */%';

SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS

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

select /*+ cursor_sharing_force_demo */ count(*) from t where id1=:"SYS_B_0" 24vkux5z1rsjy 1 3

SQL> select sql_id, child_number, sql_text from v$sql where sql_id='24vkux5z1rsjy';

SQL_ID CHILD_NUMBER SQL_TEXT

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

24vkux5z1rsjy 0 select /*+ cursor_sharing_force_demo */ count(*) from t where id1=:"SYS_B_0"

此時，我們觀察到三次執行之後SQL遊標共享情況。首先，三次的SQL語句從字面值上完全不同，差異只是存在在條件id1取值上。如果在cursor_sharing為EXACT模式下，是不能實現遊標共享的。設定為FORCE之後，我們發現Oracle自動將id1=後面的條件替換為繫結變數。三次SQL呼叫均使用相同的父遊標，而子游標只存在一個，意味著三次呼叫均是使用這個唯一的子游標。一個子遊標對應一個執行計劃，三個SQL使用相同的執行計劃。

我們使用抽取手段抽取出執行計劃，如下：

SQL> select * from table(dbms_xplan.display_cursor('24vkux5z1rsjy',0,'advanced'));

PLAN_TABLE_OUTPUT

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

SQL_ID 24vkux5z1rsjy, child number 0

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

select /*+ cursor_sharing_force_demo */ count(*) from t where

id1=:"SYS_B_0"

Plan hash value: 2966233522

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

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

| 0 | SELECT STATEMENT | | | | 9 (100)| |

| 1 | SORT AGGREGATE | | 1 | 2 | | |

|* 2 | TABLE ACCESS FULL| T | 10000 | 20000 | 9 (12)| 00:00:01 |

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

Query Block Name / Object Alias (identified by operation id):

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

1 - SEL$1

2 - SEL$1 / T@SEL$1

Peeked Binds (identified by position):

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

1 - :SYS_B_0 (VARCHAR2(30), CSID=852): 'D'

Predicate Information (identified by operation id):

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

2 - filter("ID1"=:SYS_B_0)

49 rows selected

注意，這個執行計劃進行的是資料表的全表掃描。三次呼叫中，id1取定D\P因為資料量大，都可以使用FTS。但是G取值量少，應該使用索引路徑較為合適。所以，在第一次生成執行計劃之後，Oracle都會使用該執行計劃作為相同SQL結構的語句計劃。

這類問題很類似於大名鼎鼎的bind peeking。在執行計劃中，我們也的確看到了bind peeking資訊，說明其中是使用‘D’值peeking出的執行計劃。

此處，我們已經可以知道FORCE取值的效果，當cursor_sharing引數選擇FORCE的時候：

ü Oracle對輸入的SQL值，會將where條件取值自動替換為繫結變數。以後在輸入相同的結構SQL語句時，會進行cursor sharing共享遊標；

ü 在第一次進行自動替換繫結變數的時候，Oracle會依據bind peeking取值，獲取到一個執行計劃，對應成子游標；

ü 在以後的SQL語句中，只要出現父遊標可共享的情況，Oracle都會強制使用生成的唯一子游標進行sharing。不去在乎是不是對SQL是最優的執行計劃；

FORCE取值的規則思想很簡單，對SQL語句進行強制的繫結變數替換。使用第一次的bind peeking值生成執行計劃，之後全部使用這個執行計劃。這種方式實現了遊標共享，避免出現大量的library cache硬解析，限制一個父遊標的version_count數量。

如果這種SQL語句本身是“Good SQL”，也就是條件列分佈比較平均，沒有出現過大的偏移分佈。我們認為這種FORCE是很有益的。但是如果資料列分佈不平均，這樣借用第一次輸入的bind peeking生成並且共享執行計劃就很成問題。我們說，在cursor_sharing取定FORCE遇到的潛在問題，和我們使用繫結變數時候使用的bind peeking值問題是相同的。

5、SIMILAR——另一個極端

剛剛我們討論了FORCE。在FORCE下，問題是很簡單的：進行繫結變數替換，共享全部遊標。但是這樣對於資料分佈不均衡的條件列來說，是存在很多問題的。實際環境中會出現SQL效能時好時壞的情況。作為另一個極端，我們設定SIMILAR取值。

SQL> alter session set cursor_sharing='SIMILAR';

Session altered

SQL> show parameter cursor_sharing;

NAME TYPE VALUE

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

cursor_sharing string EXACT

SQL> select name, value from v$parameter where name='cursor_sharing';

NAME VALUE

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

cursor_sharing SIMILAR

為了更容易看清現象，我們使用逐步試驗的方法：

--Invoke SQL 1

SQL> select /*+ cursor_sharing_similar_demo */ count(*) from t where id1='D';

COUNT(*)

----------

10000

SQL> select sql_text, sql_id, version_count, executions from v$sqlarea where sql_text like '%/*+ cursor_sharing_similar_demo */%';

SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS

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

select /*+ cursor_sharing_similar_demo */ count(*) from t where id1=:"SYS_B_0" 0s63s6sjytz4y 1 1

第一次呼叫SQL語句，使用條件值D。在library cache中生成了父子游標，而且同FORCE一樣，進行了繫結變數替換。我們抽出執行計劃進行檢視。

SQL> select * from table(dbms_xplan.display_cursor('0s63s6sjytz4y',0,'advanced'));

PLAN_TABLE_OUTPUT

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

SQL_ID 0s63s6sjytz4y, child number 0

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

select /*+ cursor_sharing_similar_demo */ count(*) from t where

id1=:"SYS_B_0"

Plan hash value: 2966233522

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

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

| 0 | SELECT STATEMENT | | | | 9 (100)| |

| 1 | SORT AGGREGATE | | 1 | 2 | | |

|* 2 | TABLE ACCESS FULL| T | 10000 | 20000 | 9 (12)| 00:00:01 |

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

Peeked Binds (identified by position):

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

1 - :SYS_B_0 (VARCHAR2(30), CSID=852): 'D'

Predicate Information (identified by operation id):

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

49 rows selected

對子游標（child_number=0）而言，使用D進行bind peeking後，生成全表掃描的執行計劃。下面進行第二次呼叫：

SQL> select /*+ cursor_sharing_similar_demo */ count(*) from t where id1='G';

COUNT(*)

----------

5

SQL> select sql_text, sql_id, version_count, executions from v$sqlarea where sql_text like '%/*+ cursor_sharing_similar_demo */%';

SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS

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

select /*+ cursor_sharing_similar_demo */ count(*) from t where id1=:"SYS_B_0" 0s63s6sjytz4y 2 2

在使用一個新值G的情況下，生成了一個新的子游標（version_count=2）。我們抽取出該執行計劃進行檢視。

SQL> select * from table(dbms_xplan.display_cursor('0s63s6sjytz4y',1,'advanced'));

PLAN_TABLE_OUTPUT

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

SQL_ID 0s63s6sjytz4y, child number 1

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

select /*+ cursor_sharing_similar_demo */ count(*) from t where

id1=:"SYS_B_0"

Plan hash value: 555228874

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

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

| 0 | SELECT STATEMENT | | | | 1 (100)| |

| 1 | SORT AGGREGATE | | 1 | 2 | | |

|* 2 | INDEX RANGE SCAN| IDX_T_ID1 | 5 | 10 | 1 (0)| 00:00:01 |

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

Peeked Binds (identified by position):

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

1 - :SYS_B_0 (VARCHAR2(30), CSID=852): 'G'

Predicate Information (identified by operation id):

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

49 rows selected

在使用一個新的變數值G的情況下，Oracle生成了一個新的遊標執行計劃作為對應。新生成的執行計劃是使用索引路徑。下面進行第三次呼叫。

SQL> select /*+ cursor_sharing_similar_demo */ count(*) from t where id1='D';

COUNT(*)

----------

10000

SQL> select sql_text, sql_id, version_count, executions from v$sqlarea where sql_text like '%/*+ cursor_sharing_similar_demo */%';

SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS

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

select /*+ cursor_sharing_similar_demo */ count(*) from t where id1=:"SYS_B_0" 0s63s6sjytz4y 2 3

第三次執行，我們選擇了第一次執行使用過的D值。此時，我們發現executions次數增加一次，但是version_count版本數量沒有增加。可以知道使用了第一次生成的child_number=0的子游標執行計劃。也就是說，當使用相同的繫結變數值的時候，Oracle會共享子游標。

第四次執行，使用一個新的對應值。

SQL> select /*+ cursor_sharing_similar_demo */ count(*) from t where id1='P';

COUNT(*)

----------

8000

SQL> select sql_text, sql_id, version_count, executions from v$sqlarea where sql_text like '%/*+ cursor_sharing_similar_demo */%';

SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS

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

select /*+ cursor_sharing_similar_demo */ count(*) from t where id1=:"SYS_B_0" 0s63s6sjytz4y 3 4

SQL> select sql_text,sql_id, child_number, LAST_LOAD_TIME from v$sql where sql_id='0s63s6sjytz4y';

SQL_TEXT SQL_ID CHILD_NUMBER LAST_LOAD_TIME

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

select /*+ cursor_sharing_similar_demo */ count(*) from t where id1=:"SYS_B_0" 0s63s6sjytz4y 0 2011-07-30/19:25:31

select /*+ cursor_sharing_similar_demo */ count(*) from t where id1=:"SYS_B_0" 0s63s6sjytz4y 1 2011-07-30/19:26:40

select /*+ cursor_sharing_similar_demo */ count(*) from t where id1=:"SYS_B_0" 0s63s6sjytz4y 2 2011-07-30/19:29:42

對於一個新的繫結變數取值P，Oracle進行bind peeking之後生成了一個新的子游標（child_number=2）與之對應。也就是生成了一個新的執行計劃：

SQL> select * from table(dbms_xplan.display_cursor('0s63s6sjytz4y',2,'advanced'));

PLAN_TABLE_OUTPUT

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

SQL_ID 0s63s6sjytz4y, child number 2

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

select /*+ cursor_sharing_similar_demo */ count(*) from t where

id1=:"SYS_B_0"

Plan hash value: 2966233522

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

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

| 0 | SELECT STATEMENT | | | | 9 (100)| |

| 1 | SORT AGGREGATE | | 1 | 2 | | |

|* 2 | TABLE ACCESS FULL| T | 8000 | 16000 | 9 (12)| 00:00:01 |

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

Peeked Binds (identified by position):

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

1 - :SYS_B_0 (VARCHAR2(30), CSID=852): 'P'

49 rows selected

雖然child_number=2的執行計劃也是使用全表掃描的執行計劃，本質上同child_number=0的性質相同。但是Oracle沒有進行共享，而是重新為這個bind peeking生成了一個新的子游標。

下面繼續執行SQL：

SQL> select /*+ cursor_sharing_similar_demo */ count(*) from t where id1='A';

COUNT(*)

----------

10

SQL> select sql_text, sql_id, version_count, executions from v$sqlarea where sql_text like '%/*+ cursor_sharing_similar_demo */%';

SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS

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

select /*+ cursor_sharing_similar_demo */ count(*) from t where id1=:"SYS_B_0" 0s63s6sjytz4y 4 5

SQL> select sql_text,sql_id, child_number, LAST_LOAD_TIME,executions from v$sql where sql_id='0s63s6sjytz4y';

SQL_TEXT SQL_ID CHILD_NUMBER LAST_LOAD_TIME EXECUTIONS

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

select /*+ cursor_sharing_similar_demo */ count(*) from t where id1=:"SYS_B_0" 0s63s6sjytz4y 0 2011-07-30/19:25:31 2

select /*+ cursor_sharing_similar_demo */ count(*) from t where id1=:"SYS_B_0" 0s63s6sjytz4y 1 2011-07-30/19:26:40 1

select /*+ cursor_sharing_similar_demo */ count(*) from t where id1=:"SYS_B_0" 0s63s6sjytz4y 2 2011-07-30/19:29:42 1

select /*+ cursor_sharing_similar_demo */ count(*) from t where id1=:"SYS_B_0" 0s63s6sjytz4y 3 2011-07-30/19:32:12 1

根據一個新的繫結變數值A，Oracle又為其生成了一個新的執行計劃。

至此，我們可以初步猜出SIMILAR的特徵，相對於FORCE取值帶來的問題，SIMILAR往靈活的方向前進了一步：

ü 當cursor_sharing設定為SIMILAR的時候，Oracle對沒有使用繫結變數的SQL字面語句都會進行處理，將where條件後自動替換為繫結變數；

ü 在執行語句是，對每一個條件設定值，都會生成一個新的child cursor子游標，與父遊標相對應。也就意味著對每一個語句，都會發生一次隱式的bind peeking動作；

ü 當一個語句輸入的時候，如果之前存在過相同條件值的SQL子游標，就共享該子游標。否則生成一個新的child cursor，生成一個匹配的執行計劃；

SIMILAR是FORCE的進化版。在SIMILAR模式下，Oracle對遊標共享的條件變得比較敏感。如果繫結變數值發生變化，就意味著執行計劃可能存在不匹配的情況。所以索性Oracle對每一個新的值都bind peeking一下，生成執行計劃。而執行計劃遊標的共享只在相同繫結變數的時候才發生。

這個與FORCE相比，cursor_sharing=SIMILAR的確緩解了由於bind peeking單次帶來的執行計劃不匹配問題。但是會引入兩個新問題：

ü 如果對應條件列的取值相對較少，這樣生成執行計劃的個數起碼是可以控制的。如果是一種連續取值情況或者對應取值很多，必然引起parent cursor對應的child cursor數目增多，每次從child cursor列中遍歷的時間增加，latch和pin發生的時間增多。這也是similar取值是一個常見的問題；

ü 生成child cursor的標準不是是否執行計劃相同，而是繫結變數值相同。這樣如果資料分佈較為平均，所有值對應的執行計劃都是相同的。那麼生成很多的子游標執行計劃必然是相同的。這樣又會帶來效能和其他一些問題。

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最終會確定適合的共享方案和執行計劃。

關於ACS的相關內容，請參考筆者的《Oracle自適應共享遊標——Adaptive Cursor Sharing》（http://space.itpub.net/17203031/viewspace-703280）系列文章。

淺談cursor_sharing取值對SQL共享的影響（下）

相關文章