outline初體驗

在實際工作中，往往先在開發庫開發程式，然後再移植到產品庫的，但這樣也往往會出現問題，如在開發庫中效率很高的SQL在產品庫確執行得很慢。

由於SQL的執行效率受很多因數影響，所以在除錯作用不大的情況下，可以用outline來穩定個別SQL的執行計劃。

--建立測試資料
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

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

已選擇40448行。

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)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5932 consistent gets
0 physical reads
0 redo size
2420404 bytes sent via SQL*Net to client
30159 bytes received via SQL*Net from client
2698 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
40448 rows processed

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

Table analyzed

> select * from t_outline where owner='SUK';

已選擇40448行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=48 Card=40448 Bytes=
2871808)

1 0 TABLE ACCESS (FULL) OF 'T_OUTLINE' (Cost=48 Card=40448 Byt
es=2871808)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3168 consistent gets
0 physical reads
0 redo size
2420404 bytes sent via SQL*Net to client
30159 bytes received via SQL*Net from client
2698 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
40448 rows processed

--可以看到，從全表掃描效率明顯比索引掃描高，故可以用outline穩定該查詢的執行計劃，使之在RBO下也可以走全表掃描
--以CBO下的執行計劃作為outline的執行計劃
SQL> CREATE OUTLINE test_t_outline FOR CATEGORY test_outline ON
select * from t_outline where owner='SUK';

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

表已分析。

> select * from t_outline where owner='SUK';

已選擇40448行。

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)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5932 consistent gets
0 physical reads
0 redo size
2420404 bytes sent via SQL*Net to client
30159 bytes received via SQL*Net from client
2698 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
40448 rows processed

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

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

> select * from t_outline where owner='SUK';

已選擇40448行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=48 Card=395 Bytes=50
560)

1 0 TABLE ACCESS (FULL) OF 'T_OUTLINE' (Cost=48 Card=395 Bytes
=50560)

Statistics
----------------------------------------------------------
331 recursive calls
4 db block gets
3191 consistent gets
0 physical reads
604 redo size
2420404 bytes sent via SQL*Net to client
30159 bytes received via SQL*Net from client
2698 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
40448 rows processed
--可以看到，啟用outline可以讓查詢在RBO下也走全表掃描，提高了效率
--稍微修改SQL，看SQL不完全一致的情況下outline是否起作用> SELECT * FROM T_OUTLINE where owner='SUK';

已選擇40448行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=48 Card=395 Bytes=50
560)

1 0 TABLE ACCESS (FULL) OF 'T_OUTLINE' (Cost=48 Card=395 Bytes
=50560)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3168 consistent gets
0 physical reads
0 redo size
2420404 bytes sent via SQL*Net to client
30159 bytes received via SQL*Net from client
2698 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
40448 rows processed

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