光有统计信息是不够的,变量的值不同,可能采用的执行计划也不同,所以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系统中,不要绑定变量。