关于绑定变量的一点心得

 

关于绑定变量的一点心得 (2008-12-03 13:32:31)

<script></script> 标签：杂谈

分类：经典引用博文

作者：白鳝

我们一直在告诉开发人员一定要使用绑定变量，而你是否真正了解绑定变量的有缺点呢？绑定变量可以减少SQL分析，节约共享池的空间。但是在某些情况下，使用绑定变量也是有缺点的。比如说，如果使用绑定变量，那么优化器就会忽略直方图的信息，在生成执行计划的时候可能不够优化。 另外一个有趣的问题是，如果一张表有几十万条记录，而某个字段有2个值，那么如果在这个字段上建一个索引，那么这个索引可能起作用吗？不能？你能确定吗？如果说某个字段的取值有2个，VALID和INVALID，其中有10条记录是INVALID，其他都是VALID，那么这个索引是否有用呢？从这上面看，这个索引应该是有用的，如果访问的是INVALID的行，这个索引是十分高效的。而这种情况在我们的应用环境中大量存在。比如有一条记录，刚刚插入的时候状态为1，处理后为2，归档后为3。那么可能只有少量的为1的行，其次是为2的，最多的是3的。在这种字段上建立索引是有效的。我们来做个实验： 首先从DBA_OBJECT中生成一张TEST表，最好多搞点数据，然后把其中几行记录的STATUS字段修改为INVALID: update test set status='INVALID'  WHERE OWNER='SCOTT'; 这样，在一张有10万多条记录的表里面有了4条INVALID的记录，其他都是VALID。然后创建索引： create index test_idx on test(status); 然后对表进行分析 EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','TEST',CASCADE=>'TRUE'); 下面我们看看索引是否被使用了： SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','TEST'); PL/SQL 过程已成功完成。 SQL> select owner from scott.test where status='INVALID'; Execution Plan ----------------------------------------------------------    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=148 Card=54334 Bytes           =706342)    1    0   TABLE ACCESS (FULL) OF 'TEST' (Cost=148 Card=54334 Bytes=7           06342)   SQL> select owner from scott.test where status='VALID'; 已选择108664行。 Execution Plan ----------------------------------------------------------    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=148 Card=54334 Bytes           =706342)    1    0   TABLE ACCESS (FULL) OF 'TEST' (Cost=148 Card=54334 Bytes=7           06342) 看来索引没有起作用，我们忘记分析直方图了，下面分析直方图： SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','TEST',method_opt => 'FOR ALL INDEXED COLUMNS  SIZE 2'); PL/SQL 过程已成功完成。 SQL> select owner from scott.test where status='INVALID'; Execution Plan ----------------------------------------------------------    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=4 Bytes=52)    1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=4 Byt           es=52)    2    1     INDEX (RANGE SCAN) OF 'TEST_IDX' (NON-UNIQUE) (Cost=1 Ca           rd=4) SQL> select owner from scott.test where status='VALID'; 已选择108664行。 Execution Plan ----------------------------------------------------------    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=148 Card=108668 Byte           s=1412684)    1    0   TABLE ACCESS (FULL) OF 'TEST' (Cost=148 Card=108668 Bytes=           1412684)   很好，一切都是完美的。如果使用绑定变量，是不是更加完美呢？ SQL> begin :a:='INVALID';END;   2  / SQL> select owner from test where status=:a; Execution Plan ----------------------------------------------------------    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=148 Card=54334 Bytes           =706342)    1    0   TABLE ACCESS (FULL) OF 'TEST' (Cost=148 Card=54334 Bytes=7           06342) 好像不对劲了？为什么？ 查一下直方图的使用限制，下列场合直方图是无法使用的：   o all predicates on the column use bind variables    o the column data is uniformly distributed    o the column is not used in WHERE clauses of queries    o the column is unique and is used only with equality predicates  由于在使用绑定变量的时候，9i开始使用bind peeking技术，通过这个技术，在SQL进行硬分析的时候，如果存在直方图，会探测绑定变量，根据绑定变量产生执行计划。8i不具备bind peeking技术，如果柱状图存在，会使用缺省的选择性参数来计算COST。但是上述实验第一次执行的时候代入了INVALID，为什么执行计划不走索引呢，通过分析，原来是autotrace的一个BUG，在这个情况下，只能通过v$sql_plan或者使用sql_trace(10046)。通过分析发现： 如果第一次执行使用了INVLAID，今后所有的执行，都走索引 如果第一次使用了VALID，今后所有的执行都不走索引     执行计划出现了不好的倾向。使用了绑定变量后，优化器不是每次都能够准确的判断执行计划。我们遇到了麻烦  从上面的例子可以学到点什么？ 1、对于倾斜性的列，可以通过使用直方图来优化索引 2、对于倾斜性的列，从查询性能考虑，不要使用绑定变量（如果列上有可用索引）

如果知道其中的原理，就不难决定了。实际上很简单，除非访问的索引字段倾斜性很严重，类似上面的例子，需要使用直方图的，其他情况，都可以使用绑定变量。如果需要使用直方图，建议还是别用绑定变量了。

直方图是Oracle CBO优化器使用的一种统计数据，比如有一个字段a，取值范围是1－10000，整个表有100万条记录，那么如果你要查询a>10 and a<100的记录，如果这样的记录有100条，那么走索引是最好的，如果这样的记录有90万条，那么走索引肯定不如全表扫描。直方图里面可以看出记录的分布情况，比如1－100有多少条，101－200有多少条记录，等等。优化器通过使用直方图，可以更准确的判断使用什么执行计划最优

 

转自：http://blog.sina.com.cn/s/blog_4d9ece9a0100caw8.html