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下用索引扫描，根据数据分布可知，索引扫描效率是很低的suk@oracle9i> 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

suk@oracle9i> 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';

--删除统计数据，使查询走RBOsuk@oracle9i> analyze table t_outline delete statistics;

表已分析。

suk@oracle9i> 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的情况下走的是索引扫描

suk@oracle9i> alter session set use_stored_outlines =test_outline;
--启用outline
会话已更改。

suk@oracle9i> 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是否起作用suk@oracle9i> 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。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/231499/viewspace-63715/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/231499/viewspace-63715/