bind peeking

最新推荐文章于 2021-04-04 15:12:20 发布

事后诸葛亮

最新推荐文章于 2021-04-04 15:12:20 发布

阅读量1.6k

点赞数

分类专栏： execution plan 文章标签： sql oracle table access library query

本文链接：https://blog.csdn.net/zq9017197/article/details/7681991

版权

execution plan 专栏收录该内容

27 篇文章 0 订阅

订阅专栏

光有统计信息是不够的，变量的值不同，可能采用的执行计划也不同，所以Oracle引入了bind peeking这个技术。但需要强调的是，即使是bind peeking，也只是发生在硬分析的时候。

SQL> drop table t purge;

表已删除。

SQL> create table t as select 1 id,a.* from all_objects a;

表已创建。

SQL> update t set id = 99 where rownum = 1;

已更新 1 行。

SQL> commit;

提交完成。

SQL> create index t_idx on t(id);

索引已创建。

SQL> exec dbms_stats.gather_table_stats(user,'t',estimate_percent=>100,cascade=>true);

PL/SQL 过程已成功完成。

上面创建一个表，这个表里的数值只有2个值，id=99只有1条记录，剩下的全部等于1。如果有一条sql，它的谓词是id<>1，那么毫无疑问，数据库要选择索引，否则会选择全表扫描。

SQL> variable n number;
SQL> exec :n := 1;

PL/SQL 过程已成功完成。

SQL> alter session set sql_trace = true;

会话已更改。

SQL> select * from t n_was_1 where id = :n;
... ...

SQL> exec :n := 99;

PL/SQL 过程已成功完成。

SQL> select * from t n_was_99 where id = :n;
... ...

/*注意上面的语句中T表的2个别名，n_was_1表示变量等于1的sql语句，
而n_was_99表示这条sql的变量等于9，这个别名的用意是我们在之后产生的trace
文件中可以方便的确定那个sql的变量是多少。上面的2个sql分布赋予另外的变量1和99，
因为表有别名，所以这两条sql都将会做硬分析，此时Oracle将会对他们做bind peeking。*/

SQL> select * from t n_was_1 where id = :n;
... ...

/*由于这条sql在前面已经被执行，所以它将会进行一次软分析，
而且不会被bind peeking。但是我们要注意的是，此时n的值是99了。*/

SQL> exec :n := 1;

PL/SQL 过程已成功完成。

SQL> select * from t n_was_99 where id = :n;
... ...

/*将变量的值再次改为1，并且重新做执行第二条sql，也就是别名是n_was_99的sql。*/

SQL> alter session set sql_trace = false;

会话已更改。

SQL> select spid from v$process where addr = (select paddr from v$session where sid = (select sid from v$mystat where rownum = 1));

SPID
------------------------
3912

SQL> show parameter user_dump_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest                       string      /u01/app/diag/rdbms/orcl/orcl/
                                                 trace
SQL> !
[oracle@linux ~]$ cd /u01/app/diag/rdbms/orcl/orcl/trace/
[oracle@linux trace]$ ll | grep 3912
-rw-r----- 1 oracle asmadmin   748014 06-21 18:16 orcl_ora_3912.trc
-rw-r----- 1 oracle asmadmin    73908 06-21 18:16 orcl_ora_3912.trm
[oracle@linux trace]$ tkprof orcl_ora_3912.trc /u01/app/test explain=u1/u1 sys=no aggregate=no

TKPROF: Release 11.2.0.1.0 - Development on 星期四 6月 21 18:23:49 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

下面是从使用tkprof工具处理过的trace文件。

********************************************************************************

SQL ID: 14agujf6va3v2
Plan Hash: 1601196873
select * 
from
 t n_was_1 where id = :n                 --这是第一条sql，此时n=1。


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      155      0.01       0.16         36        187          0        2311
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      157      0.02       0.18         36        187          0        2311

Misses in library cache during parse: 1      --1表示只是一次硬解析。
Optimizer mode: ALL_ROWS
Parsing user id: 85  (U1)

Rows     Row Source Operation
-------  ---------------------------------------------------
   2311  TABLE ACCESS FULL T (cr=187 pr=36 pw=0 time=13860 us cost=292 size=7104000 card=71040)
/*执行计划是对的，因为n=1的值几乎占了全部数据，所以全表扫描要更优一些，
从这里可以看到Oracle在分析执行计划的时候，已经知道n=1，也就是说这个时候发生了bind peeking。*/


Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
   2311   TABLE ACCESS   MODE: ANALYZED (FULL) OF 'T' (TABLE)

********************************************************************************

SQL ID: 4nvd7075rwfs7
Plan Hash: 470836197
select * 
from
 t n_was_99 where id = :n         --这是第二条sql，此时n=99。


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.07         16          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.08         16          3          0           1

Misses in library cache during parse: 1      --同样，这是一次硬解析。
Optimizer mode: ALL_ROWS
Parsing user id: 85  (U1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY INDEX ROWID T (cr=3 pr=16 pw=0 time=0 us cost=2 size=100 card=1)
      1   INDEX RANGE SCAN T_IDX (cr=2 pr=16 pw=0 time=0 us cost=1 size=0 card=1)(object id 73927)
/*同样很完美，对于只有一条记录的n=99，没有比选择索引更正确的了，所以Oracle在这次依然使用了bind peeking。
要注意，前面两条sql都是第一次执行，所以它们都发生了硬分析，bind peeking只发生在硬分析阶段。*/


Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
      1   TABLE ACCESS   MODE: ANALYZED (FULL) OF 'T' (TABLE)

********************************************************************************

SQL ID: 14agujf6va3v2
Plan Hash: 1601196873
select * 
from
 t n_was_1 where id = :n     --这是第三条sql，此时n=99。


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.12       0.40       1006       1047          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.12       0.40       1006       1047          0           1

Misses in library cache during parse: 0    --注意！这是一次软分析。
Optimizer mode: ALL_ROWS
Parsing user id: 85  (U1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS FULL T (cr=1047 pr=1006 pw=0 time=0 us cost=292 size=7104000 card=71040)
/*我们很遗憾的看到，尽管n=99非常适合选择索引，但是Oracle却选择了全表扫描。
它仍然选择了之前的执行计划，因为它不知道此时n的值是多少，这是一次软分析，软分析的时候不会发生bind peeking。*/


Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
      1   TABLE ACCESS   MODE: ANALYZED (FULL) OF 'T' (TABLE)

********************************************************************************

SQL ID: 4nvd7075rwfs7
Plan Hash: 470836197
select * 
from
 t n_was_99 where id = :n           --这是第四条sql，此时n=1。


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      143      0.00       0.03          8        316          0        2131
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      145      0.00       0.03          8        316          0        2131

Misses in library cache during parse: 0     --同样这是一次软分析。
Optimizer mode: ALL_ROWS
Parsing user id: 85  (U1)

Rows     Row Source Operation
-------  ---------------------------------------------------
   2131  TABLE ACCESS BY INDEX ROWID T (cr=316 pr=8 pw=0 time=2973613 us cost=2 size=100 card=1)
   2131   INDEX RANGE SCAN T_IDX (cr=148 pr=8 pw=0 time=2673776 us cost=1 size=0 card=1)(object id 73927)
/*和第三条sql一样，执行这条sql的时候，Oracle仍然不知道n的值是多少，
因为没有发生硬分析，所以它使用的仍然是之前这条sql产生的执行计划。*/


Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
   2131   TABLE ACCESS   MODE: ANALYZED (FULL) OF 'T' (TABLE)

********************************************************************************

bind peeking为什么在软分析的时候不起作用，它为什么不能对每一次的绑定变量都做peeking？
因为对于OLTP系统来说，相同的sql重复频率非常高，如果优化器反复解析sql，必然极大的消耗系统的资源；另外，OLTP系统用户请求的结果集都非常小，所以基本上都会考虑使用索引，那么既然大家的执行假话都一致，为什么要对sql做重复分析呐。bind peeking在第一次获得了正确的执行计划之后，后续的所有sql都照这个执行计划来执行，可以极大的改善系统的性能，这是由OLTP系统的特性决定的。
对于OLAP系统sql的执行计划和谓词的值关系极大，谓词的值不同，很可能执行计划就不同，如果都踩去相同的执行计划，sql的执行效率必然非常低；另外一个OLAP系统数据库每天执行的sql数量远远少于OLTP系统，并且sql的重复率远远低于OLTP，这种情况下，sql解析花费的代价和sql执行花费的代价来比，完全可以忽略。
因此，对于OLAP系统，我觉得不需要做绑定变量，这样做可能发生执行计划选择错误的严重后果。另外如果事实上的确做了绑定变量，bind peeking也只能保证第一条硬分析的sql能够选择正确的执行计划，如果后面的谓词变量改变，很可能还是会出现sql选择错误的执行计划，因此在OLAP系统中，不要绑定变量。

事后诸葛亮

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
bind peeking

光有统计信息是不够的，变量的值不同，可能采用的执行计划也不同，所以Oracle引入了bind peeking这个技术。但需要强调的是，即使是bind peeking，也只是发生在硬分析的时候。SQL> drop table t purge;表已删除。SQL> create table t as select 1 id,a.* from all_objects a;表已创建。SQ
复制链接

扫一扫

专栏目录