Oracle绑定变量窥探

随着具体输入值的不同，SQL的where条件的可选择率(Selectivity)和结果集的行数(Cardinality)可能会随之发生变化，而Selectivity和Cardinality的值会直接影响CBO对于相关执行步骤成本值的估算，进而影响CBO对SQL执行计划的选择。这就意味着随着具体输入值的不同，目标SQL执行计划可能会发生变化。

对于不使用绑定变量的SQL而言，具体输入值一量发生了变化，目标SQL的SQL文本就会随之发生变化，这样Oracle就能很容易地计算出对应Selectivity和Cardinality的值，进而据此来选择执行计划。但对于使用绑定变量的SQL而言，情况就完全不一样了，因为现在无论对应绑定变量的具体输入值是什么，目标SQL的SQL文本都是一模一样的，这种情况下Oracle应该如何来决定目标SQL的执行计划呢？

对于使用了绑定变量的SQL而言，Oracle可以选择如下两种方法来决定其执行计划：

• 使用绑定变量窥探
• 如果不使用绑定变量窥探，则对于那些可选择率可能会随着具体输入值的不同而不同的谓词条件使用默认的可选择率(例如5%)。

绑定变量窥探(Bind Peeking)是在Oracle 9i中引入的，是否启用绑定变量窥探受隐含参数_OPTIM_PEEK_USER_BINDS的控制，_OPTIM_PEEK_USER_BINDS的默认值是TRUE，表示在Oracle 9i及其后续的版本中，绑定变量窥探在默认情况下就已经被启用了。

当绑定变量窥探被启用后，每当Oracle以硬解析的方式解析使用了绑定变量的目标SQL时，Oracle都会实际窥探(Peeking)一下对应绑定变量的具体输入值，并以这些具体输入值为标准，来决定这些使用了绑定变量的目标SQL的where条件的Selectivity和Cardinality的值，并据此来选择该SQL的执行计划。这个“窥探(Peeking)”的动作只有在硬解析的时候才会执行，当使用了绑定变量的目标SQL再次执行时(此时对应的是软解析/软软解析)，即便此时对应绑定变量的具体输入值和之前硬解析时对应的值不同，Oracle也会沿用之前硬解析时所产生的解析树和执行计划，而不再重复执行上述“窥探”的动作。

绑定变量窥探的好处是显而易见的，因为有了绑定变量窥探，Oracle在计算目标SQL的where条件的Selectivity和Cardinality的值时，就可以避免使用默认的可选择率，这样就有更大的可能性得到该SQL准确的执行计划。同样，绑定变量窥探的坏处也是显而易见的，对于那些执行计划可能会随着对应绑定变量具体输入值的不同而变化的目标SQL而言，一旦启用了绑定变量窥探，其执行计划就会被固定下来，到于这个固定下来的执行计划到底是什么，则完全倚赖于该SQL在硬解析时传入的对应绑定变量的具体值。这意味着一量启用了绑定变量窥探，目标SQL在后续执行时就会沿用之前硬解析所产生的解析树和执行计划，即使这种沿用并不适合于当前的情形。

绑定变量窥探这种不管后续传入的绑定变量的具体输入值是什么而一直沿用之前硬解析时所产生的解析权和执行计划的特性一直饱受诟病(这种状况一直到Oracle 11g中引入自适应游标共享后才有所缓解)，因为它可能使CBO在某些情况下(对应绑定变量的某些具体输入值)所选择的执行计划并不是目标SQL在当前情形下是最优执行计划，而且它可能会带来目标SQL执行计划的突然改变，进而直接影响应用系统的性能。

比如某个SQL的执行计划随着绑定变量具体输入值的不同会对应两个执行计划，一个是走对索引的索引范围扫描，另一个是走对索引的索引快速全扫描。正常情况下，对绝大多数绑定变量输入值，执行计划都应该走索引范围扫描，极少数情况下会走索引快速全扫描。但假如有一开该SQL对应的Shared Cursor被age out出Shared Pool了，那么当该SQL再次执行时Oracle就得硬解析。不幸的是如果这次硬解析时传入的绑定变量输入值恰好是走索引快速全扫描所对应的极少数的情形，那么后续的SQL走会走这个执行计划，这种情况下该SQL的执行效率就很可能比之前慢一个甚至多个数量级。表现在在应用系统上就是突然有一天发现某个应用跑不动了，而之前一直是好好的。

下面看一个绑定变量窥探的实例：

创建测试表T1及索引并收集统计信息

执行如下两个sql并查看Oracle对SQL的解析情况

从查询结果可以看出，Oracle在执行上述SQL时都使用了硬解析。Oracle分别为上述两个SQL各自生成了一个Parent Cursor和一个Child Cursor。

再查看执行计划：

从执行计划可以看出between 999 and 1000条件的SQL走的是索引范围扫描，而between 999 and 60000走的执行计划是索引快速全扫描。

现在我们将全面的两个SQL改造成使用绑定变量的等价形式。定义两个绑定变量x和y，并分别给它们赋值999和1000。

显然，此时用绑定变量x和y的改写形式“between :x and :y”与原来的“between 999 and 1000”是等价的。而且只要将y重新赋值为60000，则又和“between 999 and 60000”等价了。

现在x和y的值分别为999和100，执行改写后的sql

从上述查询结果可以看到，Oracle在第一次执行上述等价SQL时也是用的硬解析

从执行计划看，此时是对索引IDX_T1走的索引范围扫描，而且Oracle评估出来执行这个索引范围扫描所返回结果集的Cardinality的值为3。并注意到“Peeked Binds”部分的内容为“1 - :X (NUMBER): 999 2 - :Y (NUMBER): 1000”，这说明Oracle在硬解析上述SQL的过程中确实使用了绑定变量窥探，且做“窥探”这个动作时看到的绑定变量x和y的具体输入值分别为999和1000。

现在保持x不变，将y修改为60000：

从上述查询结果可以看出上述SQL对应的VERSION_COUNT的值为1，列EXECUTIONS的值为2，这说明Oracle在第二次执行该SQL时用的是软解析。

从执行计划上可以看出，此时SQL的执行计划依然走的是对索引IDX_T1走的索引范围扫描，并且“Peeked Binds”部分的内容依然为“1 - :X (NUMBER): 999 2 - :Y (NUMBER): 1000”。

之前在不使用绑定变量时，我们已经知道Oracle在执行“between 999 and 60000”条件时走的是索引快速全扫描。但第二次执行使用绑定变量等价改写的SQL时，即使绑定变量x和y的具体的输入值是999和60000，但Oracle这里依然沿用该SQL之前硬解析时(对应绑定量x和y的具体的输入值是999和1000)所产生的解析树和执行计划，而不再重复执行“窥探”的动作。