在绑定变量下使用outline

最新推荐文章于 2023-05-21 11:33:04 发布

cuilin2183

最新推荐文章于 2023-05-21 11:33:04 发布

阅读量117

点赞数

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

--没有用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行。

--从上面的测试可知，我们在编写程序时，在不同的地方使用相同的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/，如需转载，请注明出处，否则将追究法律责任。

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