绑定变量窥视

最新推荐文章于 2022-05-20 11:00:00 发布

原创最新推荐文章于 2022-05-20 11:00:00 发布 · 1.1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#session #sql #statistics #library #table #system

数据库专栏收录该内容

6 篇文章

订阅专栏

在了解了参数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)