在上篇中，我們分析了在cursor_sharing=SIMILAR的情況下，Oracle不會考慮資料字典和列描述資訊。這個過程中，SIMILAR的效果和FORCE效果相同。本篇我們繼續研究SIMILAR的工作原理。

4、有直方圖情況下的SIMILAR現象

直方圖是描述資料偏移的重要指標。Oracle會重視直方圖所體現出的資料描述內容。那麼，直方圖在促使SIMILAR其作用的過程中，是否起作用呢？

首先我們強制命令收集直方圖。

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true,method_opt => 'for columns id3 size 5');

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 53196 1 NONE

ID2 53196 1 NONE

ID3 53196 5 HEIGHT BALANCED

我們收集了id3列的高度直方圖。下面觀察執行效果。

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

COUNT(*)

----------

1

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

COUNT(*)

----------

1

SQL> select /*+ demo_id3_1 */ 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_1 */%';

SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS

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

select /*+ demo_id3_ 1xydsanchug00 3 3

1 */ count(*) from t

where id3=:"SYS_B_0 "

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

SQL_ID CHILD_NUMBER EXECUTIONS

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

1xydsanchug00 0 1

1xydsanchug00 1 1

1xydsanchug00 2 1

注意：情況有了不同，在對應不同的id3取值情況下，由於存在直方圖的不同。SIMILAR為每一個id3取值生成一個單獨的執行計劃。當下次再次出現這個取值的時候，會共用相同取值的執行計劃。

Id3列是一個普通列，那麼對於有直方圖（強令生成）的主鍵列和唯一索引列，效果是如何呢？

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true,method_opt => 'for columns id2 size 5');

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 53196 1 NONE

ID2 53196 5 HEIGHT BALANCED

ID3 53196 5 HEIGHT BALANCED

"

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

COUNT(*)

----------

1

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

COUNT(*)

----------

1

SQL> select /*+ demo_id3_2 */ 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_id3_2 */%';

SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS

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

select /*+ demo_id3_ 5gam31v1vf5a9 3 3

2 */ count(*) from t

where id2=:"SYS_B_0

"

唯一索引列的效果相同，主鍵列呢？

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true,method_opt => 'for columns id1 size 5');

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 53196 5 HEIGHT BALANCED

ID2 53196 5 HEIGHT BALANCED

ID3 53196 5 HEIGHT BALANCED

此時使用的SQL進行檢視。

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

COUNT(*)

----------

1

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

COUNT(*)

----------

1

SQL> select /*+ demo_id1_3 */ 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_3 */%';

SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS

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

select /*+ demo_id1_ 3vys9sd50v2sw 3 3

3 */ count(*) from t

where id1=:"SYS_B_0

"

連主鍵列都被判定為“Unsafe SQL”了。

上面的實驗，也就證明了SIMILAR工作的原理：只看列統計量是否存在直方圖。當SQL條件列存在直方圖的時候，並且cursor_sharing取值為SIMILAR，這樣就認為該SQL是unsafe的。就為每一個值確定一個新的執行計劃。

那麼，SIMILAR效果取決於是否存在直方圖。我們的問題就變成如何才能讓列產生直方圖。

5、列與直方圖

我們使用dbms_stats命令收集統計量的時候，透過method_opts引數指定生成直方圖的方式。在9i版本中，這個引數設定為NONE，表示預設不生成直方圖。所以在那個時代，我們經常需要手工設定method_opts引數生成偏移列的直方圖。

到了Oracle 10g，method_opts引數的預設值變成為“for all columns size auto”。簡單的說，由Oracle自己去決定是不是生成直方圖。

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

Table created

SQL> select object_id, data_object_id from dba_objects where wner='SYS' and object_name='T';

OBJECT_ID DATA_OBJECT_ID

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

56256 56256

SQL> select * from col_usage$ where obj#=56256;

OBJ# INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP

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

我們重新構建了資料表T，內部表col_usage$記錄了資料表使用情況，主要是列出現的情況。如果我們在SQL中使用了列，就會對應資訊出現在該內部表中。

此時，必然沒有直方圖資訊。

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,HISTOGRAM from dba_tab_cols where wner='SYS' and table_name='T';

COLUMN_NAME HISTOGRAM

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

ID3 NONE

ID2 NONE

ID1 NONE

無直方圖被收集。Oracle首先會去看一看該列是否使用過，如果從沒有使用過，就不會生成直方圖。因為直方圖畢竟是要消耗額外的CPU和儲存資源的。

--使用一次資料列

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

COUNT(*)

----------

1

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 * from col_usage$ where obj#=56256;

OBJ# INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP

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

56256 1 1 0 0 0 0 0 2011-10-18

--存在使用資訊之後，收集統計量。

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,HISTOGRAM from dba_tab_cols where wner='SYS' and table_name='T';

COLUMN_NAME HISTOGRAM

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

ID3 NONE

ID2 NONE

ID1 NONE

我們使用了資料列id1，並且在col_usage$中包含了相應記錄。但是依然沒有生成直方圖。是否潛在意味著描述列取值的其他統計量，如密度等因素，也在起作用？我們進行額外處理id3。

SQL> update t set id3=mod(id3,10);

50682 rows updated

SQL> commit;

Commit complete

對id3取餘數處理，增加資料列重複值出現的機率。

--使用一次id3

SQL> select count(*) from t where id3=0;

COUNT(*)

----------

5100

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true,method_opt => 'for all columns size auto');

PL/SQL procedure successfully completed

--出現對id3列使用記錄；

SQL> select * from col_usage$ where obj#=56256;

OBJ# INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP

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

56256 1 1 0 0 0 0 0 2011-10-18

56256 3 1 0 0 0 0 0 2011-10-18

SQL> select column_name,HISTOGRAM from dba_tab_cols where wner='SYS' and table_name='T';

COLUMN_NAME HISTOGRAM

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

ID3 FREQUENCY

ID2 NONE

ID1 NONE

直方圖出現！

說明：Oracle 10g的method_opts引數預設取值“for all columns size auto”的是相當複雜的判定過程。首先，Oracle會看這個資料列是否使用過，其次會檢查該列的一些統計量細節，如密度和重複值等，之後就會判斷是否生成直方圖。

6、結論

經過上下兩篇的研究，我們已經對cursor_sharing=SIMILAR取值的工作原理很清楚了。

首先，當一個資料列不存在直方圖統計資訊時候，cursor_sharing=SIMILAR的效果與cursor_sharing=FORCE相同。都會發生條件列自動繫結變數替換，都會為所有的SQL使用相同的執行計劃。

但是，當一個資料列存在直方圖的時候，無論該直方圖是何種方式生成，cursor_sharing=SIMILAR會認為這個SQL是unsafe的，需要對每個列取值生成單獨的執行計劃。所以，在cursor_sharing=SIMILAR的時候，一個顯著現象就是一些SQL帶有非常大量的version_count取值。

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

相關文章