從業界普遍接受繫結變數、硬解析和軟解析概念以及對Oracle資料庫效能的影響之後。開發和運維DBA就都在尋找一種方法，將系統中已經存在和即將存在的硬編碼改為繫結變數方式，進而減少硬解析的出現。

Cursor_sharing引數在一個時期似乎是解決一切的萬靈藥。該引數的“作用域”僅限在那些沒有使用過繫結變數的SQL語句。這一點很重要，因為如果在系統中使用了繫結變數，這個問題就不是我們需要考慮的了。

Cursor_sharing從引數語義上看，就是處理沒有使用繫結變數時候，Oracle是否將where後面取值替換為繫結變數，便於進行SQL語句shared cursor共享。

引數cursor_sharing具有三個取值：exact、similar和force。EXACT是各Oracle版本引數的預設值，表示不開啟這個功能。對沒使用繫結變數的SQL語句，完全使用字面值共享機制。FORCE是一個極端，首先會將字面值進行替換，之後所有的執行語句均是採用相同的執行計劃。詳細解析請參見筆者《淺談cursor_sharing》（http://space.itpub.net/17203031/viewspace-705196）

Similar是三個取值中較為複雜的一個。首先，同FORCE取值一樣，對傳入的SQL語句字面值進行改寫，將where條件後的條件取值替換為繫結變數。但同時，SIMILAR並不是強令使用一個子遊標滿足所有的SQL語句，而是準備多條執行計劃與之對應。

1、一條SQL需要幾個執行計劃對應？

當一個SQL的where條件取值被替換為繫結變數，如果按照CBO的原則，不同的繫結變數取值最優的執行計劃是不同的。這裡我們分為兩種情況：

ü “Safe SQL”

如果SQL條件使用的是等號“=”條件，而且資料列分佈比較平均，且Oracle存在多種執行計劃生成可能的情況下，我們說單執行計劃的確可以滿足所有SQL等號取值條件。

實際中這種資料列比如主鍵列、唯一索引列和一些統計量反映的平均化。

ü “Unsafe SQL”

Unsafe SQL的情況要複雜的多。當我們的SQL對應條件列是一個資料偏移度很高的列，或者進行範圍查詢的時候，多個最優執行計劃對應的情況是可能出現的。

對cursor_sharing引數取定SIMILAR的時候，就是可以對“Safe SQL”取定單一執行計劃，而對“Unsafe SQL”取定多執行計劃。

那麼，Oracle怎麼知道這個SQL是否是Safe呢？下面我們依據例項進行試驗。

2、環境準備

我們選擇在Oracle 10g下進行試驗。

SQL> select * from v$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 – Production

--預設情況下，cursor_sharing引數取值為EXACT；

SQL> show parameter cursor_sharing

NAME TYPE VALUE

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

cursor_sharing string EXACT

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

NAME TYPE VALUE

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

cursor_sharing 2 EXACT

此時，我們構建資料表，取值相同，但是具有不同的資料字典描述內容。從而驗證是不是資料字典資訊是SIMILAR判定Safe SQL的標準。

SQL> create table t as select object_id id1, object_id id2, object_id id3 from dba_objects;

Table created

SQL> alter table t add constraint pk_t_id1 primary key (id1);

Table altered

SQL> create unique index idx_t_id2 on t(id2);

Index created

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 wner='SYS' and table_name='T';

COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM

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

ID1 51697 1 NONE

ID2 51697 1 NONE

ID3 53196 1 NONE

三個資料列取值完全相同，差異只在於資料字典的描述地位不同。同時，三個列均沒有收集標識資料偏移度的直方圖。

3、無直方圖下的相關實驗

我們首先進行三個資料列試驗，判斷SIMILAR取值的行為方式。

--調整cursor_sharing取值

SQL> alter session set cursor_sharing='SIMILAR';

Session altered

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

NAME TYPE VALUE

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

cursor_sharing 2 SIMILAR

首先對主鍵列進行選擇使用。

--第一次呼叫SQL

SQL> select /*+ demo_id1 */ count(*) from t where id1=1000;

COUNT(*)

----------

1

--檢查父子游標情況

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

SQL_TEXT SQL_ID VERSION_COUNT

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

select /*+ demo_id1 bvdkhydtduzb6 1

*/ count(*) from t w

here id1=:"SYS_B_0"

--生成一個父遊標和一個子遊標；

SQL> select sql_id, child_number from V$sql where sql_id='bvdkhydtduzb6';

SQL_ID CHILD_NUMBER

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

bvdkhydtduzb6 0

注意，我們輸入的SQL是一個條件硬編碼的SQL。當cursor_sharing取值為SIMILAR之後，一個重要特性就是SQL被改寫，原有條件列被佔用符替換。這樣就變化為一個使用繫結變數的SQL語句。

下面分別執行兩個SQL，除了where條件中id1取值不同之外，字面值相同。

--取定2000取值；

SQL> select /*+ demo_id1 */ count(*) from t where id1=2000;

COUNT(*)

----------

1

--取定3000取值；

SQL> select /*+ demo_id1 */ count(*) from t where id1=3000;

COUNT(*)

----------

1

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

SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS

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

select /*+ demo_id1 bvdkhydtduzb6 1 3

*/ count(*) from t w

here id1=:"SYS_B_0"

SQL> select sql_id, child_number, executions from V$sql where sql_id='bvdkhydtduzb6';

SQL_ID CHILD_NUMBER EXECUTIONS

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

bvdkhydtduzb6 0 3

當我們對id1使用三個不同的取值時，使用的完全是相同的執行計劃。SIMILAR的效果和FORCE取值完全相同。Id1列為主鍵列，取值絕對均勻，是否是由於主鍵列唯一的特性，讓Oracle SIMILAR取值判定是“Safe SQL”?

我們進行下面的試驗，對id2列進行。Id2列為一個唯一索引列，取值和id1列完全相同。看看此時的效果。

--id2

SQL> select /*+ demo_id2 */ count(*) from t where id2=1000;

COUNT(*)

----------

1

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

SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS

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

select /*+ demo_id2 1wnrjz4j2dkqr 1 1

*/ count(*) from t w

here id2=:"SYS_B_0"

SQL> select sql_id, child_number, executions from V$sql where sql_id='1wnrjz4j2dkqr';

SQL_ID CHILD_NUMBER EXECUTIONS

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

1wnrjz4j2dkqr 0 1

再次嘗試2000和3000的id2取值。

SQL> select /*+ demo_id2 */ count(*) from t where id2=2000;

COUNT(*)

----------

1

SQL> select /*+ demo_id2 */ count(*) from t where id2=3000;

COUNT(*)

----------

1

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

SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS

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

select /*+ demo_id2 1wnrjz4j2dkqr 1 3

*/ count(*) from t w

here id2=:"SYS_B_0"

SQL> select sql_id, child_number, executions from V$sql where sql_id='1wnrjz4j2dkqr';

SQL_ID CHILD_NUMBER EXECUTIONS

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

1wnrjz4j2dkqr 0 3

注意，此時傳入的SQL語句發生where條件取值替換為繫結變數的現象，但是同時都是針對其對應一個執行計劃。難道說，Oracle能“看出”唯一索引的效果？

最後進行一般列id3的試驗。

SQL> select /*+ demo_id3 */ count(*) from t where id3=1000;

COUNT(*)

----------

1

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

SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS

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

select /*+ demo_id3 c0yvrw873f8gw 1 1

*/ count(*) from t w

here id3=:"SYS_B_0"

SQL> select /*+ demo_id3 */ count(*) from t where id3=2000;

COUNT(*)

----------

1

SQL> select /*+ demo_id3 */ count(*) from t where id3=3000;

COUNT(*)

----------

1

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

SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS

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

select /*+ demo_id3 c0yvrw873f8gw 1 3

*/ count(*) from t w

here id3=:"SYS_B_0"

SQL> select sql_id, child_number, executions from V$sql where sql_id='c0yvrw873f8gw';

SQL_ID CHILD_NUMBER EXECUTIONS

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

c0yvrw873f8gw 0 3

普通資料列id3也只對應一條執行計劃。

從上面對id1-id3的試驗情況看，cursor_sharing=SIMILAR下Oracle是會對SQL字面值中的條件進行繫結變數替換。但是判定“Safe SQL”的標準一定不是資料列描述資訊。

那麼，SIMILAR和FORCE在判定“Safe SQL”上的差異，是什麼標準呢？我們下篇繼續探討。

Cursor_sharing=SIMILAR取值與直方圖（上）

相關文章