《转载》oracle绑定变量详解

原文转载自linuxg公社 http://www.linuxidc.com/Linux/2011-04/34781.htm

Oracle 绑定变量

         http://www.linuxidc.com/%22Linux/2011-04/34781.htm



一．绑定变量

         bind variable： A variable in a SQL statement that must be replaced with a valid value, or the address of a value, in order for the statement to successfully execute.



变量绑定是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：

         Oracle SQL的硬解析和软解析

         http://www.linuxidc.com/Linux/2011-04/34783.htm



了解了SQL 的执行过程，在来看一些绑定变量，绑定变量的本质就是本来需要做Oracle 硬解析的SQL 变成软解析，以减少ORACLE 花费在SQL解析上的时间和资源。



加入有两条SQL:    

         Select salary from user where name=’A’;

         Select salary from user where name=’B’;



如果没有用绑定变量，那么这2条SQL 会被解析2次，因为他们的谓词部分不一样。 如果我们用了绑定变量，如：

         Select salary from user where name=:X;



这时，之前的2条SQL就变成了一种SQL， Oracle 只需要对每一种SQL做一次硬解析，之后类似的SQL 都使用这条SQL产生的执行计划，这样就可以大大降低数据库花费在SQL解析上的资源开销。 这种效果当SQL执行的越多，就越明显。



         简单的说，绑定变量就是拿一个变量来代替谓词常量，让Oracle每次对用户发来的SQL做hash 运算时，运算出的结果都是同样的Hash值，于是将所有的用户发来的SQL看作是同一个SQL来对象。





二． OLAP 和OLTP 系统中的绑定变量

         OLAP 和 OLTP 系统是有很大差异的。 他们之间的区别，详细参考Blog：

         Oracle OLAP 与 OLTP 介绍

       http://www.linuxidc.com/Linux/2011-04/34784.htm



在OLTP系统中，我们可以使用绑定变量是因为在OLTP中，SQL语句大多是比较简单或者操作的结果集都很小。当一个表上创建了索引，那么这种极小结果集的操作使用索引最合适，并且几乎所有的SQL的执行计划的索引都会被选择，因为这种情况下，索引可能只需要扫描几个数据块就可以定位到数据，而全表扫描将会相当耗资源。 因此，这种情况下，即使每个用户的谓词条件不一样，执行计划也是一样的，就是都用索引来访问数据，基本不会出现全表扫描的情况。 在这种执行计划几乎唯一的情况下，使用绑定变量来代替谓词常量，是合适的。



在OLAP系统中，SQL的操作就复杂很多，OLAP数据库上大多数时候运行的一些报表SQL，这些SQL经常会用到聚合查询（如：group by），而且结果集也是非常庞大，在这种情况下，索引并不是必然的选择，甚至有时候全表扫描的性能会更优于索引，即使相同的SQL，如果谓词不同，执行计划都可能不同。





对于OLAP系统中的绑定变量，有以下原则：

（1）       OLAP 系统完全没有必要绑定变量，那样只会带来负面的影响，比如导致SQL选择错误的执行，这个代价有时是灾难性的；让Oracle对每条SQL做硬分析，确切的知道谓词条件的值，这对执行计划的选择至关重要，这样做的原因是，在OLAP系统中，SQL硬分析的代价是可以忽略的，系统的资源基本上是用于做大的SQL查询，和查询比起来，SQL解析消耗的资源显得微不足道。所以得到一个最优的执行计划就非常重要。

（2）       在OLAP系统中，让Oracle确切地知道谓词的数值至关重要，它直接决定了SQL执行计划的选择，这样做的方式就是不要绑定变量。

（3）       在OLAP系统中，表，索引的分析显得直观重要，因为它是Oracle 为SQL做出正确的执行计划的信息的来源和依据，所以需要建立一套能够满足系统需求的对象分析的执行Job。

先看一段官网的说明：

         The query optimizer peeks at the values of user-defined bind variables on the first invocation of a cursor. This feature enables the optimizer to determine the selectivity of any WHERE clause condition as if literals have been used instead of bind variables.

To ensure the optimal choice of cursor for a given bind value, Oracle Database uses bind-aware cursor matching. The system monitors the data access performed by the query over time, depending on the bind values. If bind peeking takes place, and if the database uses a histogram to compute selectivity of the predicate containing the bind variable, then the database marks the cursor as bind-sensitive.

Whenever the database determines that a cursor produces significantly different data access patterns depending on the bind values, the database marks this cursor as bind-aware. Oracle Database switches to bind-aware cursor matching to select the cursor for this statement. When bind-aware cursor matching is enabled, the database selects plans based on the bind value and the optimizer estimate of its selectivity. With bind-aware cursor matching, a SQL statement with user-defined bind variable can have multiple execution plans, depending on the bind values.

When bind variables appear in a SQL statement, the database assumes that cursor sharing is intended and that different invocations use the same execution plan. If different invocations of the cursor significantly benefit from different execution plans, then bind-aware cursor matching is required. Bind peeking does not work for all clients, but a specific set of clients.



From：http://download.Oracle.com/docs/cd/E11882_01/server.112/e10821/optimops.htm#PFGRF94588



Bind Peeking是Oracle 9i中引入的新特性，它的作用就是在SQL语句硬分析的时候，查看一下当前SQL谓词的值，以便生成最佳的执行计划。 而在oracle 9i之前的版本中，Oracle 只根据统计信息来做出执行计划。



要注意的是，Bind Peeking只发生在硬分析的时候，即SQL被第一次执行的时候，之后的变量将不会在做peeking。我们可以看出，Bind peeking并不能最终解决不同谓词导致选择不同执行计划的问题，它只能让SQL第一次执行的时候，执行计划选择更加准确，并不能帮助OLAP系统解决绑定变量导致执行计划选择错误的问题。这也是OLAP不应该使用绑定变量的一个原因。







总结：

         对于OLTP系统，相同的SQL重复频率非常高，如果优化器反复解析SQL，必然会极大的消耗系统资源，另外，OLTP系统用户请求的结果集都非常小，所以基本上都考虑使用索引。 Bind Peeking 在第一次获得了一个正确的执行计划之后，后续的所有SQL都按照这个执行计划来执行，这样就极大的改善了系统的性能。



         对于OLAP系统，SQL执行计划和谓词关系极大，谓词值不同，可能执行计划就不同，如果采用相同的执行计划，SQL的执行效率必然很低。另外，一个OLAP系统数据库每天执行的SQL数量远远比OLTP少，并且SQL重复频率也远远低于OLTP系统，在这种条件下，SQL解析花费的代价和SQL执行花费的代价相比，解析的代价可以完全忽略。



所以，对于OLAP系统，不需要绑定变量，如果使用可能导致执行计划选择错误。 并且，如果用了绑定变量，Bind Peeking也只能保证第一条硬分析SQL能正确的选择执行计划，如果后面的谓词改变，很可能还是会选择错误的执行计划。 因此在OLAP系统中，不建议使用绑定变量。


本篇文章来源于 Linux公社网站(www.linuxidc.com)  原文链接：http://www.linuxidc.com/Linux/2011-04/34781p2.htm