绑定变量窥视

  在了解了参数OPTIMIZER_INDEX_COST_ADJ的作用后。再了解一个对查询计划，特别 是 使用绑定变量 时会产生重大影响的特性：绑定变量 窥视（Bind Variables Peeking）。

绑定变量 窥视是9i以后的一个新特性。它使CBO优化器在计算访问代价时，将绑定变量 传入的值考虑进去，从而计算出更合理的成本（否则，将会计算平均成本）。看下面例子：

SQL> conn sys/sys as sysdba
Connected.
SQL>
SQL> alter system set OPTIMIZER_INDEX_COST_ADJ=60;
 
System altered.
 
SQL> analyze table T_PEEKING compute statistics for table for all indexes for all indexed columns;
 
Table analyzed.
 
SQL>
SQL> set autot trace
SQL>
SQL> alter session set sql_trace = true;
 
Session altered.
 
SQL>
SQL> var v char(1)
SQL>
SQL> exec :v := 'A';
 
PL/SQL procedure successfully completed.
 
SQL>
SQL> select * from T_PEEKING a where b = :V;
 
1000 rows selected.
 
SQL>
SQL> alter session set sql_trace = false;
 
Session altered.
用TKPROF处理生成的trace文件。因为在存在绑定变量 窥视时，autotrace或者explain plan可能不会显示正确的查询计划，需要tkprof来处理sql trace。

tkprof fuyuncat_ora_5352.trc aaa.txt
 

此时OPTIMIZER_INDEX_COST_ADJ是60，根据上面的结论，似乎查询计划应该选择扫描索引。但是，这里给绑定变量 赋了值"A"，这时，优化器会“窥视”到这个值，并且在计算扫描成本时按照这个值的成本来计算。因此，得出的查询计划是全表扫描，而不是扫描索引，靠tkprof分析的结果：

select *
from
 T_PEEKING a where b = :V
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       68      0.01       0.07          0        406          0        1000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       70      0.01       0.08          0        406          0        1000
 
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: SYS
 
Rows     Row Source Operation
-------  ---------------------------------------------------
   1000  TABLE ACCESS FULL T_PEEKING (cr=406 pr=0 pw=0 time=5052 us)
 
*************************************************************************
 

但是 ，绑定变量 窥视对一条语句只会使用一次。就是 说，在第一次解析语句时，将绑定变量 值考虑进去计算成本生成查询计划。以后在执行该语句时都采用这个查询计划，而不再考虑以后绑定变量 的值是 什么 了。

SQL> conn sys/sys as sysdba
Connected.
SQL>
SQL>
SQL> set autot trace
SQL>
SQL> alter session set sql_trace = true;
 
Session altered.
 
SQL>
SQL> var v char(1)
SQL>
SQL> exec :v := 'B';
 
PL/SQL procedure successfully completed.
 
SQL>
SQL> select * from T_PEEKING a where b = :V;
 
1000 rows selected.
 
SQL>
SQL> alter session set sql_trace = false;
 
Session altered.
 

再用tkprof分析生成的trace文件，看到尽管这里的值是 "B"，选择索引扫描会更优，但分析结果中查询计划还是 使用全表扫描：

select *
from
 T_PEEKING a where b = :V
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0        340          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0        340          0           2
 
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS
 
Rows     Row Source Operation
-------  ---------------------------------------------------
      2  TABLE ACCESS FULL T_PEEKING (cr=340 pr=0 pw=0 time=1005 us)
 

因此，这种情况下使用绑定变量 也会导致无法选择最优的查询计划。

 

综上，我们可以得出一个结论：在对建有索引的字段（包括字段集），且字段（集）的集的势非常大时，使用绑定变量 可能会导致查询计划错误，因而会使查询效率非常低。