ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 在绑定变量下使用outline

在绑定变量下使用outline

原创 Linux操作系统 作者:space6212 时间:2018-12-09 17:57:03 0 删除 编辑

前段时间写了一篇文章,简单介绍如何使用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

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

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

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

PL/SQL 过程已成功完成。

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




--分析表,查询走全表扫描
suk@oracle9i> analyze table t_outline compute statistics;

表已分析。

suk@oracle9i> 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的执行计划 suk@oracle9i> CREATE OUTLINE test_t_outline FOR CATEGORY test_outline ON select * from t_outline where owner=:o;

大纲已创建。

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

表已分析。

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

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

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

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

PL/SQL 过程已成功完成。

suk@oracle9i> 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在分别去空格并转换成大写后一样。也就是说,即使绑定使用变量,包括绑定的变量名也必须一样。

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

上一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2005-01-25

  • 博文量
    10
  • 访问量
    6020