在繫結變數下使用outline

前段時間寫了一篇文章，簡單介紹如何使用outline，原文地址：

http://space6212.itpub.net/post/12157/145831

原來的文章主要針對非繫結變數的情況的，但在實際中我們會使用到繫結變數，今天我們就接著討論下使用繫結變數時如何使用outline

用outline穩定執行計劃

--建立測試資料
SQL> create table t_outline as select * from all_objects where owner='SUK';

Table created

SQL> create index idx_t_outline on t_outline(owner);

Index created

> select owner,count(1) from t_outline group by owner;

OWNER COUNT(1)
------------------------------ ----------
SUK 145920

> var o varchar2(20)> exec :o:='SUK'

PL/SQL 過程已成功完成。

--RBO下用索引掃描，根據資料分佈可知，索引掃描效率是很低的> select * from t_outline where owner=:o;

已選擇145920行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T_OUTLINE'
2 1 INDEX (RANGE SCAN) OF 'IDX_T_OUTLINE' (NON-UNIQUE)

--分析表，查詢走全表掃描> analyze table t_outline compute statistics;

表已分析。

> select * from t_outline where owner=:o;

已選擇145920行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=174 Card=145920 Byte
s=11089920)

1 0 TABLE ACCESS (FULL) OF 'T_OUTLINE' (Cost=174 Card=145920 B
ytes=11089920)

--可以看到，從全表掃描效率明顯比索引掃描高，故可以用outline穩定該查詢的執行計劃，使之在RBO下也可以走全表掃描

--以CBO下的執行計劃作為outline的執行計劃 > CREATE OUTLINE test_t_outline FOR CATEGORY test_outline ON select * from t_outline where owner=:o;

大綱已建立。

--刪除統計資料，使查詢走RBO> analyze table t_outline delete statistics;

表已分析。

> select * from t_outline where owner=:o;

已選擇145920行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T_OUTLINE'
2 1 INDEX (RANGE SCAN) OF 'IDX_T_OUTLINE' (NON-UNIQUE)

--沒有用outline的情況下走的是索引掃描

> alter session set use_stored_outlines =test_outline;
--啟用outline
會話已更改。

> select * from t_outline where owner=:o;

已選擇145920行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=174 Card=1460 Bytes=
186880)

1 0 TABLE ACCESS (FULL) OF 'T_OUTLINE' (Cost=174 Card=1460 Byt
es=186880)

--可以看到，啟用outline可以讓查詢在RBO下也走全表掃描，提高了效率
--稍微修改SQL，看SQL不完全一致的情況下outline是否起作用> SELECT * FROM T_OUTLINE where owner=:o;

已選擇145920行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=174 Card=1460 Bytes=
186880)

1 0 TABLE ACCESS (FULL) OF 'T_OUTLINE' (Cost=174 Card=1460 Byt
es=186880)

--從上面的執行計劃可知，SQL不完全一致的情況下，outline也起作用。測試可知，即使原來的sql分成多行仍然可以用outline。
--實際上，outline在分析某條sql是否可用儲存大綱的時候會把這條sql的空格全部去掉，並且會把sql全部轉換成大寫再進行比較，所以，只要sql的去掉所有空格後的字元一致就可以用到儲存在庫中與之對應的outline。但像這種語句是不能用到outline的
SQL> SELECT * FROM T_OUTLINE T where owner=:o;
--上面的SQL因為多了一個T，所以不能用到outline。

--我們使用outline時需要注意使用繫結變數，否則outline能應用的範圍就很小了（當然針對特殊的查詢需要特殊對待的除外），但是，如果繫結變數的變數名不一樣，oracle是不會用outline的。如

> var a varchar2(20)> exec :a:='SUK'

PL/SQL 過程已成功完成。

> SELECT * FROM T_OUTLINE where owner=:a;

已選擇145920行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T_OUTLINE'
2 1 INDEX (RANGE SCAN) OF 'IDX_T_OUTLINE' (NON-UNIQUE)

--從上面的測試可知，我們在編寫程式時，在不同的地方使用相同的sql應儘量使用相同的變數名

--如果沒有使用繫結變數，且cursor_sharing不為force，我們很容易就可以推斷出這種情況下能用到outline的機率很小，只有去空格並轉成大寫後的SQL完全一樣時才會用到outline。但我們知道，如果cursor_sharing=force，oracle會強制把具體值轉換成變數，使得sql以繫結變數的方式執行，那麼，是不是說cursor_sharing=force就可以使不繫結變數、且條件值不一樣的sql用outline呢？

透過試驗可以得知
當cursor_sharing=force時，
普通查詢：select * from t_outline where owner='SUK' 會轉化為：select * from t_outline where owner=:"SYS_B_0"

但在建立outline時：
CREATE OUTLINE test_t_outline FOR CATEGORY test_outline ON select * from t_outline where owner='SUK';
在OL$中儲存的仍然是：select * from t_outline where owner='SUK' ，
而不是：select * from t_outline where owner=:"SYS_B_0"

--所以如果資料庫沒有使用繫結變數，無論cursor_sharing為什麼值，如果想要用到outline，去空格並轉成大寫後的SQL必須一樣（包括條件值）。

結論：
無論cursor_sharing設定成什麼、是否使用繫結變數，如果要用到outline，則唯一的條件是：執行查詢的SQL必須與CREATE OUTLINE後接的SQL在分別去空格並轉換成大寫後一樣。也就是說，即使繫結使用變數，包括繫結的變數名也必須一樣。