【DB笔试面试389】在Oracle中，什么是绑定变量窥探？

AiDBA宝典

发布于 2022-02-22 15:07:42

8510

发布于 2022-02-22 15:07:42

文章被收录于专栏：小麦苗的DB宝专栏

题目

在Oracle中，什么是绑定变量窥探？

答案

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

l 使用绑定变量窥探（Bind Peeking）。

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

绑定变量窥探（Bind Peeking）是在Oracle 9i中引入的，是否启用绑定变量窥探受隐含参数“_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在后续执行时就只会沿用之前硬解析所产生的解析树和执行计划，即使当时的执行计划和解析树并不适合于新传入的值。

关于绑定变量窥探需要注意以下几点：

（1）在Oracle llg中引入自适应游标共享后，绑定变量窥探这种不管后续传入的绑定变量的具体输入值是什么而一直沿用之前硬解析时所产生的解析树和执行计划的缺点才有所缓解。

（2）在不改变SQL语句文本的情况下，让SQL语句重新进行硬解析的方法有：①对SQL语句涉及到的对象执行DDL操作（例如COMMENT语句）。②执行DBMS_SHARED_POOL.PURGE来删除共享池中的游标。③在重新收集统计信息时指定NO_INVALIDATE=>FALSE选项。

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

绑定变量窥探的副作用在于，一旦启用（默认情况下绑定变量窥探就己经被启用），使用了绑定变量的目标SQL就只会沿用之前硬解析时所产生的解析树和执行计划，即使这种沿用完全不适合当前的情形，即根据第一次传入的值然后固化执行计划。

DB笔试面试历史连接