绑定变量

1.绑定变量的窥探（bind peeking）：

　　定义：bind peeking 是在oracle9i中引入的，是否启用bind peeking 由隐含参数_optim_peek_user_binds的控制，其默认值为True，表示在9i及其之后的版本中，默认都是开启bind peeking的。

　　作用：在oracle对采用的绑定变量的目标SQL进行硬解析时，oracle都会窥探以下其对应的绑定变量的输入值，并以其作为标准来计算目标SQL中的where条件 的Selectivity和Cardinality的值，以此来决定该SQL的执行计划，当使用的绑定变量的目标SQL再次执行时（此时对应的是软解析/软软解析），即便此时绑定变量的输入值与之前的不一样，oracle也不会进行“窥探”动作，而是会沿用之前硬解析时产生的解析树和执行计划。　

　　存在的问题：由于开启bind peeking会导致oracle采用原有的执行计划，所以当绑定变量的输入值改变之后，很可能导致CBO采用的执行计划不是最优的，而且它也有可能导致执行计划的突变，进而直接影响系统的性能。

　　案例：假设有一条SQL采用了绑定变量，输入值不同会对应两个不同的执行计划，一个是对某索引的范围扫描，另一个是对该索引的快速全扫描。其中只有极少数情况才会走索引快速扫描，这意味着多数情况下库缓存中缓存的执行计划都是走索引范围扫描，这种情况就会导致原本该走索引快速全扫描的情况走了索引范围扫描，这没什么大问题，最多就是不能像索引快速全扫描那样使用并行和多块读，此时目标SQL的执行效率不会有数量级的差异。但是假如有一天该SQL对应的Shared Cursor被age out出Shared Pool了，该SQL再次执行时oracle会对其进行硬解析（包括“窥探”动作），刚好此时传入的绑定变量的值是上述极少数走索引快速全扫描的情况，那么此时oracle就会沿用快速索引全扫描的执行计划。意味着只要其对应的Shared Cursor还在Shared Pool中，该SQL就会采用索引快速全扫描的执行计划，这时很恐怖的事情，因为索引快速全扫描会扫描所有的索引叶子快，而该SQL大多数时间是采用索引范围扫描很可能只需要扫描很少的索引叶子快就可以了，这种情况就会导致执行效率比之前走索引范围扫描时慢了一个甚至多个数量级，具体表现在应用系统上突然有一天发现某个应用跑不动了，在此之前都是好的。　

　　解决方法：在传入不同的绑定变量值时让目标SQL走硬解析过程。让oracle再次执行目标SQL时使用硬解析的方法很多：

　　其中很常见的一种方法就是对目标SQL中涉及的表执行DDL操作，因为一旦对表执行DDL操作，库缓存中所有在SQL文本中包含了该表的Shared Cursor都会被oracle标记为invalid，意味着这些Shared Cursor中存储的解析树和执行计划将不再能被重用，所以oracle再次执行与这个表相关的SQL时都会使用硬解析。DDL操作中一般选择添加注释的comment语句，因为使用comment语句也是DDL操作，而且其对生产环境的影响是微乎其微的。

　　以上方法虽然可行，但影响范围还是很大，因为对某个表做DDL操作后会导致所有涉及该表的SQL在重新执行时都会采用硬解析，很有可能导致短时间内硬解析数量剧增，影响系统性能。所以还有一种方式为使用DBMS_SHARED_POOL.PURGE，该存储过程是在10.2.0.4开始引入的一种方法，它可以删除指定的缓存在库缓存中的Shared Cursor。当库缓存中不存在目标SQL的Shared Cursor时，oracle再次执行该SQL时就会使用硬解析。其使用方式为：

首先查出目标SQL的address和hash_value

SQL> select sql_text,sql_id,address,hash_value from v$sqlarea  where sql_text like 'select count(*) from bind_peeking_test%';

SQL_TEXT                                                                      SQL_ID                ADDRESS               HASH_VALUE
-------------------------------------------------------------------    --------------------   ----------------------------  -------------------- 
select count(*) from bind_peeking_test where objec 10d0mz2454kxh  00000000C2C04130  2287094704
t_id between 999 and 60000

select count(*) from bind_peeking_test where objec 5k7dbumjgks0a   00000000C2C04BF0  3807993866
t_id between 999 and 1000

select count(*) from bind_peeking_test                      64h74416s7ar    00000000C2C8B8E0  1300474612

SQL> exec sys.dbms_shared_pool.purge('00000000C2C8B8E0,1300474612','c'); --c为常量，表示要删除的是Shared Cursor

PL/SQL procedure successfully completed.

SQL> select sql_text,sql_id,address,hash_value from v$sqlarea  where sql_text like 'select count(*) from bind_peeking_test%';

SQL_TEXT                                                                      SQL_ID                ADDRESS               HASH_VALUE
-------------------------------------------------------------------    --------------------   ----------------------------  -------------------- 
select count(*) from bind_peeking_test where objec 10d0mz2454kxh  00000000C2C04130  2287094704
t_id between 999 and 60000

select count(*) from bind_peeking_test where objec 5k7dbumjgks0a   00000000C2C04BF0  3807993866
t_id between 999 and 1000

以上演示可以看出目标SQL 对应的Shared Cursor确实被删除了。需要注意的是，如果要在oracle10.2.0.4中使用DBMS_SHARED_POOL.PURGE，则在使用前必须手工设置event 5614566（akter sessuib set events '5614566 trace bane context forever'），否则DBMS_SHARED_POOL.PURGE将不起作用，该限制在10.2.0.4之后的版本已经不存在。