Oracle 绑定变量 详解

帝王

　　Oracle绑定变量详解
　　一．绑定变量
　　绑定变量:为了成功地执行语句,一个变量必须在一个SQL语句中替换为一个有效的值或者一个值的地址,。
　　变量绑定是OLTP系统中一个非常值得关注的技术。良好的变量绑定会使OLTP系统数据库中的SQL执行速度飞快，内存效率极高；不使用绑定变量可能会使OLTP数据库不堪重负，资源被SQL解析严重耗尽，系统运行缓慢。
　　当一个用户与数据库建立连接后，会向数据库发出操作请求，即向数据库送过去SQL语句。Oracle在接收到这些SQL后，会先对这个SQL做一个hash函数运算，得到一个Hash值，然后到共享池中寻找是否有和这个hash值匹配的SQL存在。如果找到了，Oracle将直接使用已经存在的SQL的执行计划去执行当前的SQL，然后将结果返回给用户。如果在共享池中没有找到相同Hash值的SQL，oracle会认为这是一条新的SQL。会进行解析。
　　Oracle解析的步骤如下：
　　（1）语法解析
　　（2）语义解析
　　（3）生成执行计划，这里分软解析和硬解析。硬解析是非常耗资源的。
　　（4）SQL的执行
　　关于SQL的解析，详见Blog：
　　OracleSQL的硬解析和软解析
　　http://blog.csdn.net/tianlesoftware/archive/2010/04/08/5458896.aspx
　　了解了SQL的执行过程，在来看一些绑定变量，绑定变量的本质就是本来需要做Oracle硬解析的SQL变成软解析，以减少ORACLE花费在SQL解析上的时间和资源。
　　假如有两条SQL:
　　Selectsalaryfromuserwherename=’A’;
　　Selectsalaryfromuserwherename=’B’;
　　如果没有用绑定变量，那么这2条SQL会被解析2次，因为他们的谓词部分不一样。如果我们用了绑定变量，如：
　　Selectsalaryfromuserwherename=:X;
　　这时，之前的2条SQL就变成了一种SQL，Oracle只需要对每一种SQL做一次硬解析，之后类似的SQL都使用这条SQL产生的执行计划，这样就可以大大降低数据库花费在SQL解析上的资源开销。这种效果当SQL执行的越多，就越明显。
　　简单的说，绑定变量就是拿一个变量来代替谓词常量，让Oracle每次对用户发来的SQL做hash运算时，运算出的结果都是同样的Hash值，于是将所有的用户发来的SQL看作是同一个SQL来对象。
　　二．OLAP和OLTP系统中的绑定变量
　　OLAP和OLTP系统是有很大差异的。他们之间的区别，详细参考Blog：
　　OracleOLAP（联机事务处理）与OLTP（联机分析处理）介绍
　　http://blog.csdn.net/tianlesoftware/archive/2010/08/08/5794844.aspx
　　在OLTP系统中，我们可以使用绑定变量是因为在OLTP中，SQL语句大多是比较简单或者操作的结果集都很小。当一个表上创建了索引，那么这种极小结果集的操作使用索引最合适，并且几乎所有的SQL的执行计划的索引都会被选择，因为这种情况下，索引可能只需要扫描几个数据块就可以定位到数据，而全表扫描将会相当耗资源。因此，这种情况下，即使每个用户的谓词条件不一样，执行计划也是一样的，就是都用索引来访问数据，基本不会出现全表扫描的情况。在这种执行计划几乎唯一的情况下，使用绑定变量来代替谓词常量，是合适的。
　　在OLAP系统中，SQL的操作就复杂很多，OLAP数据库上大多数时候运行的一些报表SQL，这些SQL经常会用到聚合查询（如：groupby），而且结果集也是非常庞大，在这种情况下，索引并不是必然的选择，甚至有时候全表扫描的性能会更优于索引，即使相同的SQL，如果谓词不同，执行计划都可能不同。
　　对于OLAP系统中的绑定变量，有以下原则：
　　（1）OLAP系统完全没有必要绑定变量，那样只会带来负面的影响，比如导致SQL选择错误的执行，这个代价有时是灾难性的；让Oracle对每条SQL做硬分析，确切的知道谓词条件的值，这对执行计划的选择至关重要，这样做的原因是，在OLAP系统中，SQL硬分析的代价是可以忽略的，系统的资源基本上是用于做大的SQL查询，和查询比起来，SQL解析消耗的资源显得微不足道。所以得到一个最优的执行计划就非常重要。
　　（2）在OLAP系统中，让Oracle确切地知道谓词的数值至关重要，它直接决定了SQL执行计划的选择，这样做的方式就是不要绑定变量。
　　（3）在OLAP系统中，表，索引的分析显得直观重要，因为它是Oracle为SQL做出正确的执行计划的信息的来源和依据，所以需要建立一套能够满足系统需求的对象分析的执行Job。
　　三．Bindpeaking
　　先看一段官网的说明：
　　Thequeryoptimizerpeeksatthevaluesofuser-definedbindvariablesonthefirstinvocationofacursor.ThisfeatureenablestheoptimizertodeterminetheselectivityofanyWHEREclauseconditionasifliteralshavebeenusedinsteadofbindvariables.
　　Toensuretheoptimalchoiceofcursorforagivenbindvalue,OracleDatabaseusesbind-awarecursormatching.Thesystemmonitorsthedataaccessperformedbythequeryovertime,dependingonthebindvalues.Ifbindpeekingtakesplace,andifthedatabaseusesahistogramtocomputeselectivityofthepredicatecontainingthebindvariable,thenthedatabasemarksthecursorasbind-sensitive.
　　Wheneverthedatabasedeterminesthatacursorproducessignificantlydifferentdataaccesspatternsdependingonthebindvalues,thedatabasemarksthiscursorasbind-aware.OracleDatabaseswitchestobind-awarecursormatchingtoselectthecursorforthisstatement.Whenbind-awarecursormatchingisenabled,thedatabaseselectsplansbasedonthebindvalueandtheoptimizerestimateofitsselectivity.Withbind-awarecursormatching,aSQLstatementwithuser-definedbindvariablecanhavemultipleexecutionplans,dependingonthebindvalues.
　　WhenbindvariablesappearinaSQLstatement,thedatabaseassumesthatcursorsharingisintendedandthatdifferentinvocationsusethesameexecutionplan.Ifdifferentinvocationsofthecursorsignificantlybenefitfromdifferentexecutionplans,thenbind-awarecursormatchingisrequired.Bindpeekingdoesnotworkforallclients,butaspecificsetofclients.
　　From：http://download.oracle.com/docs/cd/E11882_01/server.112/e10821/optimops.htm#PFGRF94588
　　BindPeeking是Oracle9i中引入的新特性，它的作用就是在SQL语句硬分析的时候，查看一下当前SQL谓词的值，以便生成最佳的执行计划。而在oracle9i之前的版本中，Oracle只根据统计信息来做出执行计划。
　　要注意的是，BindPeeking只发生在硬分析的时候，即SQL被第一次执行的时候，之后的变量将不会在做peeking。我们可以看出，Bindpeeking并不能最终解决不同谓词导致选择不同执行计划的问题，它只能让SQL第一次执行的时候，执行计划选择更加准确，并不能帮助OLAP系统解决绑定变量导致执行计划选择错误的问题。这也是OLAP不应该使用绑定变量的一个原因。
　　总结：
　　对于OLTP系统，相同的SQL重复频率非常高，如果优化器反复解析SQL，必然会极大的消耗系统资源，另外，OLTP系统用户请求的结果集都非常小，所以基本上都考虑使用索引。BindPeeking在第一次获得了一个正确的执行计划之后，后续的所有SQL都按照这个执行计划来执行，这样就极大的改善了系统的性能。
　　对于OLAP系统，SQL执行计划和谓词关系极大，谓词值不同，可能执行计划就不同，如果采用相同的执行计划，SQL的执行效率必然很低。另外，一个OLAP系统数据库每天执行的SQL数量远远比OLTP少，并且SQL重复频率也远远低于OLTP系统，在这种条件下，SQL解析花费的代价和SQL执行花费的代价相比，解析的代价可以完全忽略。
　　所以，对于OLAP系统，不需要绑定变量，如果使用可能导致执行计划选择错误。并且，如果用了绑定变量，BindPeeking也只能保证第一条硬分析SQL能正确的选择执行计划，如果后面的谓词改变，很可能还是会选择错误的执行计划。因此在OLAP系统中，不建议使用绑定变量。