我们一直在告诉开发人员一定要使用绑定变量,而你是否真正了解绑定变量的有缺点呢?绑定变量可以减少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、对于倾斜性的列,从查询性能考虑,不要使用绑定变量(如果列上有可用索引)
分享到:
相关推荐
PLSQL绑定变量用法小结归纳.pdf
绑定变量的目的和命名用方法
动态SQL与绑定变量 动态SQL与绑定变量
绑定变量
在oltp系统中提倡使用绑定变量,使用绑定变量可以减少hard parse
Java中Oracle操作绑定变量使用用户工具模块解决方案
oracle 数据库的绑定变量特性及应用
在Java中实现Oracle变量的绑定方法 .doc 在Java中实现Oracle变量的绑定方法 .doc
查找未使用绑定变量sql.sql
也谈oracle 数据库的绑定变量特性及应用,很好的资料!
wpf 给控件绑定静态变量,静态变理改变后控件上的值随着变量的demo,这只是个demo,具体应用场景可以根据实际的情况进行调整,本demo使用 vs2019进行编写。
wpf 给控件绑定静态变量,静态变理改变后控件上的值随着变量的demo,这只是个demo,具体应用场景可以根据实际的情况进行调整,本demo使用 vs2019进行编写。
注意如何查询sql自动绑定变量,执行以上sql分析执行情况
WPF的数据绑定功能非常强大,在控件中使用数据绑定功能会为你节省大量的处理...本示例中,将一个变量绑定到一个控件上,在代码中改变变量值,并立刻引起控件值的变化。此代码经过删减,仅仅保留精简内容,一看就懂。
Oracle数据库的绑定变量特性及应用.pdfOracle数据库的绑定变量特性及应用.pdfOracle数据库的绑定变量特性及应用.pdfOracle数据库的绑定变量特性及应用.pdfOracle数据库的绑定变量特性及应用.pdfOracle数据库的绑定...
结合实例说明Oracle数据库绑定变量特性及应用。合理的使用绑定变量,可以大大提高SQL性能
绑定变量 变量绑定是OLTP系统中一个非常值得关注的技术。良好的变量绑定会使OLTP系统数据库中的SQL 执行速度飞快,内存效率极高;不使用绑定变量可能会使OLTP 数据库不堪重负,资源被SQL解析严重耗尽,系统运行...
labview创建和绑定变量共享io变量编程示例程序源码 This VI programmatically discovers all I/O variables being published by the CompactRIO controller specified below. Shared variables are created and ...
Oracle 中关于变量绑定的解释说明,还是蛮清楚的学习资料。