共计 17930 个字符，预计需要花费 45 分钟才能阅读完成。
随着具体输入值的不同，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 及索引并收集统计信息
zx@MYDB>create table t1 as select * from dba_objects;Table created.zx@MYDB>create index idx_t1 on t1(object_id);Index created.zx@MYDB>select count(*) from t1; COUNT(*)---------- 72005zx@MYDB>select count(distinct(object_id)) from t1;COUNT(DISTINCT(OBJECT_ID))-------------------------- 72005zx@MYDB>exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>'T1',estimate_percent=>100,cascade=>true,method_opt=>'for all columns size 1',no_invalidate=>false);PL/SQL procedure successfully completed.执行如下两个 sql 并查看 Oracle 对 SQL 的解析情况
zx@MYDB>select count(*) from t1 where object_id between 999 and 1000; COUNT(*)---------- 2zx@MYDB>select count(*) from t1 where object_id between 999 and 60000; COUNT(*)---------- 58180zx@MYDB>col sql_text for a80zx@MYDB>select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select count(*) from t1 %';SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS-------------------------------------------------------------------------------- --------------------------------------- ------------- ----------select count(*) from t1 where object_id between 999 and 1000 5gu397922cuqd 1 1select count(*) from t1 where object_id between 999 and 60000 b8xxw70vja3tn 1 1从查询结果可以看出，Oracle 在执行上述 SQL 时都使用了硬解析。Oracle 分别为上述两个 SQL 各自生成了一个 Parent Cursor 和一个 Child Cursor。
再查看执行计划：
从执行计划可以看出 between 999 and 1000 条件的 SQL 走的是索引范围扫描，而 between 999 and 60000 走的执行计划是索引快速全扫描。
现在我们将全面的两个 SQL 改造成使用绑定变量的等价形式。定义两个绑定变量 x 和 y，并分别给它们赋值 999 和 1000。
zx@MYDB>var x number;zx@MYDB>var y number;zx@MYDB>exec :x := 999;PL/SQL procedure successfully completed.zx@MYDB>exec :y := 1000;PL/SQL procedure successfully completed.显然，此时用绑定变量 x 和 y 的改写形式“between :x and :y”与原来的“between 999 and 1000”是等价的。而且只要将 y 重新赋值为 60000，则又和“between 999 and 60000”等价了。
现在 x 和 y 的值分别为 999 和 100，执行改写后的 sql
zx@MYDB>select count(*) from t1 where object_id between :x and :y; COUNT(*)---------- 2zx@MYDB>select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select count(*) from t1 %';SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS-------------------------------------------------------------------------------- --------------------------------------- ------------- ----------select count(*) from t1 where object_id between 999 and 1000 5gu397922cuqd 1 1select count(*) from t1 where object_id between 999 and 60000 b8xxw70vja3tn 1 1select count(*) from t1 where object_id between :x and :y 9dhu3xk2zu531 1 1从上述查询结果可以看到，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：
zx@MYDB>exec :y := 60000;PL/SQL procedure successfully completed.zx@MYDB>select count(*) from t1 where object_id between :x and :y; COUNT(*)---------- 58180
从上述查询结果可以看出上述 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) 所产生的解析树和执行计划，而不再重复执行“窥探”的动作。
更多详情见请继续阅读下一页的精彩内容：http://www.linuxidc.com/Linux/2017-03/141597p2.htm
如果想让上述等价 SQL 再次走索引快速全扫描，只需要让 Oracle 再次执行 SQL 时使用硬解析就行。因为一旦使用硬解析，Oracle 就会再执行一次“窥探”的动作。让 Oracle 再次执行目标 SQL 时使用硬解析的方法有很多，其中很常见的一种方法是对目标 SQL 中所涉及的表执行 DDL 操作。因为一旦对某个表执行了 DDL 操作，库缓存 中所有在 SQL 文本中包含了这个表的 Shared Cursor 都会被 Oracle 标记为失效(invalid)，这意味着这些 Shared Cursor 中存储的解析树和执行计划将不再能被重用，所以当 Oracle 再次执行与这个表相关的 SQL 时就会使用硬解析。这里选择对表添加注释(COMMENT)，它也是 DDL 操作。
对表 T1 执行 COMMENT 语句并执行等价 SQL
zx@MYDB>comment on table t1 is 'Test table for Bind Peeking';Comment created.zx@MYDB>select count(*) from t1 where object_id between :x and :y; COUNT(*)---------- 58180zx@MYDB>select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select count(*) from t1 %';SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS-------------------------------------------------------------------------------- --------------------------------------- ------------- ----------select count(*) from t1 where object_id between 999 and 1000 5gu397922cuqd 1 1select count(*) from t1 where object_id between 999 and 60000 b8xxw70vja3tn 1 1select count(*) from t1 where object_id between :x and :y 9dhu3xk2zu531 1 1从上面的查询结果可以看到等价 SQL 对应的列 VERSION_COUNT 的值为 1，列 EXECUTIONS 的值由之前的 2 变为了现在的 1，说明 Oracle 在第三次执行该 SQL 时用的是硬解析(EXECUTIONS 的值为 1，是因为 Oracle 在这里重新生成了一对 Parent Cursor 和 Child Cursor，原先 EXECUTIONS 的值为 2 所对应的 Shared Cursor 已经被 Oracle 标记为 invalid，相当于被废弃了)。
从执行计划可以看出，现在执行计划走的是索引快速全扫描，而 Oracle 评估出来执行这个索引快速全扫描所返回结果集的 Cardinality 的值为 57646。并且“Peeked Binds”部分的内容依然为“1 – :X (NUMBER): 999 2 – :Y (NUMBER): 60000”。说明 Oracle 在执行上述 SQL 的过程中确实又一次使用了绑定变量窥探，且做“窥探”这个动作时看到的绑定变量 x 和 y 的具体输入值分别为 999 和 60000。
现在把隐含参数_OPTIM_PEEK_USER_BINDS 的值设为 FALSE 以关闭绑定变量窥探：
1 2 3 | zx@MYDB>alter session set "_optim_peek_user_binds"=false;Session altered. |
然后保持 x 的值不变，将 y 值修改为 1000
zx@MYDB>select count(*) from t1 where object_id between :x and :y; COUNT(*)---------- 2zx@MYDB>select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select count(*) from t1 %';SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS-------------------------------------------------------------------------------- --------------------------------------- ------------- ----------select count(*) from t1 where object_id between 999 and 1000 5gu397922cuqd 1 1select count(*) from t1 where object_id between 999 and 60000 b8xxw70vja3tn 1 1select count(*) from t1 where object_id between :x and :y 9dhu3xk2zu531 2 2从上面的查询结果可以看到等价 SQL 对应的列 VERSION_COUNT 和列 EXECUTIONS 的值均由 1 变为了现在的 2，说明 Oracle 在第四次执行该 SQL 时使用硬解析。VERSION_COUNT 的值为 2，意味着该 SQL 所在的 Parent Cursor 下挂了两个 Child Cursor。从如下查询结果可以看出该 SQL 确实有两个 Child Cursor：
zx@MYDB>select plan_hash_value,child_number from v$sql where sql_id='9dhu3xk2zu531';PLAN_HASH_VALUE CHILD_NUMBER--------------- ------------ 1410530761 0 2351893609 1显然，我们把绑定变量窥探关闭后再次执行 SQL 时所对应的解析权和执行计划应该存储在 CHILD_NUMBER 为 1 的 Child Cursor 中。查看执行计划
从执行计划可以看出 Oracle 此时的执行计划已经从之前的索引快速全扫描变为 现在的索引范围扫描。而且 Oracle 评估出来执行这个索引范围扫描所返回结果集的 Cardinality 的值为 180。注意 Outline Data 部分有“OPT_PARAM(‘_optim_peek_user_binds’ ‘false’)”，而且执行计划中没有“Peeking Binds”部分内容，说明此时 Oracle 已经禁用了绑定变量窥探。
前面已经介绍了使用 DDL 操作可以让 Oracle 再次执行 SQL 时使用硬解析，但这种方法的弊端在于其影响范围还是太广，因为一旦对某个表执行了 DDL 操作，再次执行与这个表相关的所有 SQL 时就会全部使用硬解析。这是很不好的，特别是对于 OLTP 类型的应用系统而言，因为这可能会导致短时间内的硬解析数量剧增，进而影响系统的性能。
下面再来介绍一种就去让 Oracle 再次执行目标 SQL 时使用硬解析，但其影响范围公限于目标 SQL 所对应的 Shared Cursor，也就是说它可以做到让 Oracle 在执行目标 SQL 时使用硬解析，在执行其他所有 SQL 时都和原来一样保持不变。
这种方法就是使用 DBMS_SHARED_POOL.PURGE。它是从 Oracle 10.2.0.4 开始引入的一种方法，它可以用来删除指定的缓存在库缓存中的 Shared Cursor。DBMS_SHARED_POOL.PURGE 可以让 Oracle 在执行目标 SQL 时使用硬解析的原理是显而易见的——如果某个 SQL 对应的 Shared Cursor 被删除了，Oracle 再次执行该 SQL 时自然就会使用硬解析了。
查看目标 SQL 对应的 ADDRESS 和 HASH_VALUE 值：
zx@MYDB>select sql_text,sql_id,version_count,executions,address,hash_value from v$sqlarea where sql_text like 'select count(*) from t1 %';SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS ADDRESS HASH_VALUE-------------------------------------------------------------------------------- --------------------------------------- ------------- ---------- ---------------- ----------select count(*) from t1 where object_id between 999 and 1000 5gu397922cuqd 1 1 00000000B4D1B130 1143368397select count(*) from t1 where object_id between 999 and 60000 b8xxw70vja3tn 1 1 00000000B4D1AA90 924127028select count(*) from t1 where object_id between :x and :y 9dhu3xk2zu531 2 2 00000000B4CC4840 2247955553使用 dbms_shared_pool.purge 删除目标 SQL 的 Shared Cursor：
zx@MYDB>exec sys.dbms_shared_pool.purge('00000000B4CC4840,2247955553','c');PL/SQL procedure successfully completed.zx@MYDB>select sql_text,sql_id,version_count,executions,address,hash_value from v$sqlarea where sql_text like 'select count(*) from t1 %';SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS ADDRESS HASH_VALUE-------------------------------------------------------------------------------- --------------------------------------- ------------- ---------- ---------------- ----------select count(*) from t1 where object_id between 999 and 1000 5gu397922cuqd 1 1 00000000B4D1B130 1143368397select count(*) from t1 where object_id between 999 and 60000 b8xxw70vja3tn 1 1 00000000B4D1AA90 924127028从上述查询结果可以看出，dbms_shared_pool.purge 确实已经删除了目标 sql 对应的 Shared Cursor。
需要注意的是，如果在 10.2.0.4 中使用 dbms_shared_pool.purge，则在使用之前必须特工设置 event 5614566(alter session set events ‘5614566 trace name context forever’)，否则 dbms_shared_pool.purge 将不起作用，这个限制在 10.2.0.4 以上的版本中已经不存在了。
现在保持 x 值不变，将 y 修改为 60000，并执行目标 SQL：
zx@MYDB>exec :y := 60000;PL/SQL procedure successfully completed.zx@MYDB>select count(*) from t1 where object_id between :x and :y; COUNT(*)---------- 58180 zx@MYDB>select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select count(*) from t1 %';SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS-------------------------------------------------------------------------------- --------------------------------------- ------------- ----------select count(*) from t1 where object_id between 999 and 1000 5gu397922cuqd 1 1select count(*) from t1 where object_id between 999 and 60000 b8xxw70vja3tn 1 1select count(*) from t1 where object_id between :x and :y 9dhu3xk2zu531 2 1zx@MYDB>select plan_hash_value,child_number from v$sql where sql_id='9dhu3xk2zu531';PLAN_HASH_VALUE CHILD_NUMBER--------------- ------------ 2351893609 0从上面的查询结果可以看到该 SQL 对应的 VERSION_COUNT 的值为 2，EXECUTIONS 的值为 1。EXECUTIONS 的值为 1 说明 Oracle 在执行些 SQL 时确实是在用硬解析，但 VERSION_COUNT 的值应该为 1 才对，从查询中也看到目标 SQL 的 Parent Cursor 下确实只挂了一个 Child Cursor，所以 VERSION_COUNT 的值应该是 1 而不是 2(也许是 Oracle 的 BUG，不再深究)。
从执行计划中可以看出，Oracle 此时仍然选择索引范围扫描，而且 Oracle 评估出来执行这个索引范围扫描返回的结果集的 Cardinality 的值依然是 180。
这意味着当我们把绑定变量窥探关闭后，无论对绑定变量 x 和 y 传入的值是多少，都不会影响 Oracle 对于目标 SQL 执行计划的选择。这也契合了之前提到的：如果不使用绑定变量窥探，则对那些可选择率可能会随着具体输入值的变化的谓词条件而言，Oracle 会使用默认的可选择率(例如 5%)。
那 180 是如何计算出来的呢？
对于上述 SQL 其 where 条件的 Selectivity 和 Cardinality 的计算公式如下所示：
Cardinality = NUM_ROWS * Selectivity
Selectivity = 0.05*0.05
上述公式适用于禁用了绑定变量窥探且 where 条件为“目标列 between x and y”的 Selectivity 和 Cardinality 的计算
NUM_ROWS 表示目标列所在列的记录数
where 条件为“目标列 between x and y”，相当于“目标列 >=x and 目标列 <=y”, 对于“目标列 >=x and 目标列 <=y”而言，Oracle 均会使用 5% 的可选择率，所以可选择率就是 0.05*0.05。
代入公式进行计算，计算结果为 180。
zx@MYDB>select table_name,num_rows from dba_tables where owner=user and table_name='T1';TABLE_NAME NUM_ROWS------------------------------------------------------------------------------------------ ----------T1 72005zx@MYDB>select round(72005*0.05*0.05) from dual;ROUND(72005*0.05*0.05)---------------------- 180参考：《基于 Oracle 的 SQL 优化》PDF 下载见 http://www.linuxidc.com/Linux/2017-02/140521.htm
更多 Oracle 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12
本文永久更新链接地址：http://www.linuxidc.com/Linux/2017-03/141597.htm
随着具体输入值的不同，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 及索引并收集统计信息
zx@MYDB>create table t1 as select * from dba_objects;Table created.zx@MYDB>create index idx_t1 on t1(object_id);Index created.zx@MYDB>select count(*) from t1; COUNT(*)---------- 72005zx@MYDB>select count(distinct(object_id)) from t1;COUNT(DISTINCT(OBJECT_ID))-------------------------- 72005zx@MYDB>exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>'T1',estimate_percent=>100,cascade=>true,method_opt=>'for all columns size 1',no_invalidate=>false);PL/SQL procedure successfully completed.执行如下两个 sql 并查看 Oracle 对 SQL 的解析情况
zx@MYDB>select count(*) from t1 where object_id between 999 and 1000; COUNT(*)---------- 2zx@MYDB>select count(*) from t1 where object_id between 999 and 60000; COUNT(*)---------- 58180zx@MYDB>col sql_text for a80zx@MYDB>select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select count(*) from t1 %';SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS-------------------------------------------------------------------------------- --------------------------------------- ------------- ----------select count(*) from t1 where object_id between 999 and 1000 5gu397922cuqd 1 1select count(*) from t1 where object_id between 999 and 60000 b8xxw70vja3tn 1 1从查询结果可以看出，Oracle 在执行上述 SQL 时都使用了硬解析。Oracle 分别为上述两个 SQL 各自生成了一个 Parent Cursor 和一个 Child Cursor。
再查看执行计划：
从执行计划可以看出 between 999 and 1000 条件的 SQL 走的是索引范围扫描，而 between 999 and 60000 走的执行计划是索引快速全扫描。
现在我们将全面的两个 SQL 改造成使用绑定变量的等价形式。定义两个绑定变量 x 和 y，并分别给它们赋值 999 和 1000。
zx@MYDB>var x number;zx@MYDB>var y number;zx@MYDB>exec :x := 999;PL/SQL procedure successfully completed.zx@MYDB>exec :y := 1000;PL/SQL procedure successfully completed.显然，此时用绑定变量 x 和 y 的改写形式“between :x and :y”与原来的“between 999 and 1000”是等价的。而且只要将 y 重新赋值为 60000，则又和“between 999 and 60000”等价了。
现在 x 和 y 的值分别为 999 和 100，执行改写后的 sql
zx@MYDB>select count(*) from t1 where object_id between :x and :y; COUNT(*)---------- 2zx@MYDB>select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select count(*) from t1 %';SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS-------------------------------------------------------------------------------- --------------------------------------- ------------- ----------select count(*) from t1 where object_id between 999 and 1000 5gu397922cuqd 1 1select count(*) from t1 where object_id between 999 and 60000 b8xxw70vja3tn 1 1select count(*) from t1 where object_id between :x and :y 9dhu3xk2zu531 1 1从上述查询结果可以看到，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：
zx@MYDB>exec :y := 60000;PL/SQL procedure successfully completed.zx@MYDB>select count(*) from t1 where object_id between :x and :y; COUNT(*)---------- 58180
从上述查询结果可以看出上述 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) 所产生的解析树和执行计划，而不再重复执行“窥探”的动作。
更多详情见请继续阅读下一页的精彩内容：http://www.linuxidc.com/Linux/2017-03/141597p2.htm
