ITPub博客

首页 > Linux操作系统 > Linux操作系统 > outline初体验

outline初体验

原创 Linux操作系统 作者:space6212 时间:2019-07-02 12:12:05 0 删除 编辑

在实际工作中,往往先在开发库开发程序,然后再移植到产品库的,但这样也往往会出现问题,如在开发库中效率很高的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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 日志分析一例
请登录后发表评论 登录
全部评论

注册时间:2005-01-25

  • 博文量
    245
  • 访问量
    168757