Oracle 绑定变量窥探
转自 http://blog.csdn.net/leshami/article/details/6923627
Bind Peeking是Oracle 9i中引入的新特性，一直持续到Oracle 10g R2。它的作用就是在SQL语句硬分析的时候，查看一下当前SQL谓词的值
，以便生成最佳的执行计划。而在oracle 9i之前的版本中，Oracle
只根据统计信息来做出执行计划。
一、绑定变量窥探
使用SQL首次运行时的值来生成执行计划。后续再次运行该SQL语句则使用首次执行计划来执行。
影响的版本：Oracle
9i, Oracle 10g
对于绑定变量列中的特殊值或非均匀分布列上的绑定变量会造成非高效的执行计划被选择并执行。
要注意的是，Bind
Peeking只发生在硬分析的时候，即SQL被第一次执行的时候，之后的变量将不会在做peeking。我们可以看出，Bind
peeking并不能最终解决不同谓词导致选择不同执行计划的问题，它只能让SQL第一次执行的时候，执行计划选择更加准确，并不能帮助OLAP
系统解决绑定变量导致执行计划选择错误的问题。这也是OLAP不应该使用绑定变量的一个原因。
更确切地说，绑定变量窥探是在SQL解析的物理阶段，查询优化器将会窥探绑定变量的值并将其作为字面量来使用。即ORACLE首次解析
SQL时会将变量的真实值代入产生执行计划，后续对所有使用该绑定变量SQL语句都采用首次生存的执行计划。如此这般？那性能究竟如何？
结果是并非最佳的执行计划的使用。此问题在Oracle 11g中得以解决。
请参考：Oracle自适应共享游标
二、示例绑定变量窥探
1、创建演示环境
- SQL> select * from v$version where rownum<2; -->查看当前数据库版本
- BANNER
- ----------------------------------------------------------------
- Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
- SQL> create table t(id,owner,object_id) as -->创建测试表t
- 2 select rownum,owner,object_id from all_objects where rownum<=1000;
- SQL> alter table t add constraint t_pk primary key(id); -->为表t添加主键
- SQL> begin -->收集统计信息,此处未生成直方图信息
- 2 dbms_stats.gather_table_stats(
- 3 ownname=>'SCOTT',
- 4 tabname=>'T',
- 5 estimate_percent=>100,
- 6 method_opt=>'for all columns size 1');
- 7 end;
- 8 /
- PL/SQL procedure successfully completed.
- SQL> select count(id),count(distinct id),min(id),max(id) from t; -->查看值的分布情况
- COUNT(ID) COUNT(DISTINCTID) MIN(ID) MAX(ID)
- ---------- ----------------- ---------- ----------
- 1000 1000 1 1000
SQL> select * from v$version where rownum<2; -->查看当前数据库版本
BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
SQL> create table t(id,owner,object_id) as -->创建测试表t
2 select rownum,owner,object_id from all_objects where rownum<=1000;
SQL> alter table t add constraint t_pk primary key(id); -->为表t添加主键
SQL> begin -->收集统计信息,此处未生成直方图信息
2 dbms_stats.gather_table_stats(
3 ownname=>'SCOTT',
4 tabname=>'T',
5 estimate_percent=>100,
6 method_opt=>'for all columns size 1');
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> select count(id),count(distinct id),min(id),max(id) from t; -->查看值的分布情况
COUNT(ID) COUNT(DISTINCTID) MIN(ID) MAX(ID)
---------- ----------------- ---------- ----------
1000 1000 1 1000
2、未使用绑定变量情形下SQL语句的执行计划
- SQL> select sum(object_id) from t where id<900; -->发布SQL 查询语句
- SUM(OBJECT_ID)
- --------------
- 446549
- SQL> select * from table(dbms_xplan.display_cursor()); -->由其执行计划可知，当前的SQL语句使用了全表扫描
- /**************************************************/
- /* Author: Robinson Cheng */
- /* Blog: http://blog.csdn.net/robinson_0612 */
- /* MSN: robinson_0612@hotmail.com */
- /* QQ: 645746311 */
- /**************************************************/
- PLAN_TABLE_OUTPUT
- ---------------------------------------------------------------------------
- SQL_ID bz6h6fdsxgjka, child number 0
- -------------------------------------
- select sum(object_id) from t where id<900
- Plan hash value: 2966233522
- ---------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ---------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | | | 3 (100)| |
- | 1 | SORT AGGREGATE | | 1 | 8 | | |
- |* 2 | TABLE ACCESS FULL| T | 900 | 7200 | 3 (0)| 00:00:01 |
- ---------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 2 - filter("ID"<900)
- SQL> select sum(object_id) from t where id<10; -->发布另一条SQL 查询语句
- SQL> select * from table(dbms_xplan.display_cursor()); -->此时的查询生成的执行计划走索引范围扫描
- -->由于字面量不同，因此两条SQL语句生成了不同的SQL_ID与执行计划
- PLAN_TABLE_OUTPUT
- --------------------------------------------------------------------------
- SQL_ID 6y2280pyvacfq, child number 0
- -------------------------------------
- select sum(object_id) from t where id<10
- Plan hash value: 4270555908
- -------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- -------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | | | 3 (100)| |
- | 1 | SORT AGGREGATE | | 1 | 8 | | |
- | 2 | TABLE ACCESS BY INDEX ROWID| T | 9 | 72 | 3 (0)| 00:00:01 |
- |* 3 | INDEX RANGE SCAN | T_PK | 9 | | 2 (0)| 00:00:01 |
- -------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 3 - access("ID"<10)
SQL> select sum(object_id) from t where id<900; -->发布SQL 查询语句
SUM(OBJECT_ID)
--------------
446549
SQL> select * from table(dbms_xplan.display_cursor()); -->由其执行计划可知，当前的SQL语句使用了全表扫描
/**************************************************/
/* Author: Robinson Cheng */
/* Blog: http://blog.csdn.net/robinson_0612 */
/* MSN: robinson_0612@hotmail.com */
/* QQ: 645746311 */
/**************************************************/
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
SQL_ID bz6h6fdsxgjka, child number 0
-------------------------------------
select sum(object_id) from t where id<900
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | TABLE ACCESS FULL| T | 900 | 7200 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"<900)
SQL> select sum(object_id) from t where id<10; -->发布另一条SQL 查询语句
SQL> select * from table(dbms_xplan.display_cursor()); -->此时的查询生成的执行计划走索引范围扫描
-->由于字面量不同，因此两条SQL语句生成了不同的SQL_ID与执行计划
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
SQL_ID 6y2280pyvacfq, child number 0
-------------------------------------
select sum(object_id) from t where id<10
Plan hash value: 4270555908
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 9 | 72 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T_PK | 9 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID"<10)
3、使用绑定变量情形下的执行计划
- SQL> variable v_id number; -->定义绑定变量
- SQL> exec :v_id:=900; -->给绑定变量赋值
- PL/SQL procedure successfully completed.
- SQL> select sum(object_id) from t where id<:v_id;
- SUM(OBJECT_ID)
- --------------
- 446549
- SQL> select * from table(dbms_xplan.display_cursor()); -->此时上一条SQL语句走了全表扫描，其SQL_ID 为7qcp6urqh7d2j
- PLAN_TABLE_OUTPUT
- ------------------------------------------------------------------------------
- SQL_ID 7qcp6urqh7d2j, child number 0
- -------------------------------------
- select sum(object_id) from t where id<:v_id
- Plan hash value: 2966233522
- ---------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ---------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | | | 3 (100)| |
- | 1 | SORT AGGREGATE | | 1 | 8 | | |
- |* 2 | TABLE ACCESS FULL| T | 900 | 7200 | 3 (0)| 00:00:01 |
- ---------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 2 - filter("ID"<:V_ID) -->谓词信息表明此时使用了绑定变量
- SQL> exec :v_id:=10; -->对绑定变量重新赋值
- PL/SQL procedure successfully completed.
- SQL> select sum(object_id) from t where id<:v_id; -->再次执行SQL语句
- SUM(OBJECT_ID)
- --------------
- 254
- SQL> select * from table(dbms_xplan.display_cursor()); -->此时执行计划中依然选择的是全表扫描
- -->其SQL_ID同上一次执行的SQL语句相同，即实现了完全共享
- PLAN_TABLE_OUTPUT -->对于未使用绑定变量时id<10的情形则为走索引范围扫描
- ----------------------------------------------- -->由此可知，并非最佳的执行计划被执行
- SQL_ID 7qcp6urqh7d2j, child number 0
- -------------------------------------
- select sum(object_id) from t where id<:v_id
- Plan hash value: 2966233522
- ---------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ---------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | | | 3 (100)| |
- | 1 | SORT AGGREGATE | | 1 | 8 | | |
- |* 2 | TABLE ACCESS FULL| T | 900 | 7200 | 3 (0)| 00:00:01 |
- ---------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 2 - filter("ID"<:V_ID)
- SQL> alter system flush shared_pool; -->清空共享池，此时共享的父游标与子游标全部释放
- SQL> print v_id;
- V_ID
- ----------
- 10
- SQL> select round(avg(object_id)) from t where id<:v_id; -->使用id<10来执行SQL语句
- ROUND(AVG(OBJECT_ID))
- ---------------------
- 28
- SQL> select * from table(dbms_xplan.display_cursor()); -->此时该SQL语句使用了最佳的执行计划，即走索引范围扫描
- PLAN_TABLE_OUTPUT
- ---------------------------------------------------------------------------------------
- SQL_ID 0bx53mgt4qqnt, child number 0
- -------------------------------------
- select round(avg(object_id)) from t where id<:v_id
- Plan hash value: 4270555908
- -------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- -------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | | | 3 (100)| |
- | 1 | SORT AGGREGATE | | 1 | 8 | | |
- | 2 | TABLE ACCESS BY INDEX ROWID| T | 9 | 72 | 3 (0)| 00:00:01 |
- |* 3 | INDEX RANGE SCAN | T_PK | 9 | | 2 (0)| 00:00:01 |
- -------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 3 - access("ID"<:V_ID)
- SQL> exec :v_id:=900; -->为变量赋新值
- PL/SQL procedure successfully completed.
- SQL> select round(avg(object_id)) from t where id<:v_id;
- ROUND(AVG(OBJECT_ID))
- ---------------------
- 497
- SQL> select * from table(dbms_xplan.display_cursor()); -->此次运行的SQL语句本该使用全表扫描，而此时选择了索引范围扫描
- PLAN_TABLE_OUTPUT
- --------------------------------------------------------------------------------------
- SQL_ID 0bx53mgt4qqnt, child number 0
- -------------------------------------
- select round(avg(object_id)) from t where id<:v_id
- Plan hash value: 4270555908
- -------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- -------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | | | 3 (100)| |
- | 1 | SORT AGGREGATE | | 1 | 8 | | |
- | 2 | TABLE ACCESS BY INDEX ROWID| T | 9 | 72 | 3 (0)| 00:00:01 |
- |* 3 | INDEX RANGE SCAN | T_PK | 9 | | 2 (0)| 00:00:01 |
- -------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 3 - access("ID"<:V_ID)
- SQL> drop table t;
SQL> variable v_id number; -->定义绑定变量
SQL> exec :v_id:=900; -->给绑定变量赋值
PL/SQL procedure successfully completed.
SQL> select sum(object_id) from t where id<:v_id;
SUM(OBJECT_ID)
--------------
446549
SQL> select * from table(dbms_xplan.display_cursor()); -->此时上一条SQL语句走了全表扫描，其SQL_ID 为7qcp6urqh7d2j
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
SQL_ID 7qcp6urqh7d2j, child number 0
-------------------------------------
select sum(object_id) from t where id<:v_id
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | TABLE ACCESS FULL| T | 900 | 7200 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"<:V_ID) -->谓词信息表明此时使用了绑定变量
SQL> exec :v_id:=10; -->对绑定变量重新赋值
PL/SQL procedure successfully completed.
SQL> select sum(object_id) from t where id<:v_id; -->再次执行SQL语句
SUM(OBJECT_ID)
--------------
254
SQL> select * from table(dbms_xplan.display_cursor()); -->此时执行计划中依然选择的是全表扫描
-->其SQL_ID同上一次执行的SQL语句相同，即实现了完全共享
PLAN_TABLE_OUTPUT -->对于未使用绑定变量时id<10的情形则为走索引范围扫描
----------------------------------------------- -->由此可知，并非最佳的执行计划被执行
SQL_ID 7qcp6urqh7d2j, child number 0
-------------------------------------
select sum(object_id) from t where id<:v_id
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | TABLE ACCESS FULL| T | 900 | 7200 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"<:V_ID)
SQL> alter system flush shared_pool; -->清空共享池，此时共享的父游标与子游标全部释放
SQL> print v_id;
V_ID
----------
10
SQL> select round(avg(object_id)) from t where id<:v_id; -->使用id<10来执行SQL语句
ROUND(AVG(OBJECT_ID))
---------------------
28
SQL> select * from table(dbms_xplan.display_cursor()); -->此时该SQL语句使用了最佳的执行计划，即走索引范围扫描
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID 0bx53mgt4qqnt, child number 0
-------------------------------------
select round(avg(object_id)) from t where id<:v_id
Plan hash value: 4270555908
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 9 | 72 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T_PK | 9 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID"<:V_ID)
SQL> exec :v_id:=900; -->为变量赋新值
PL/SQL procedure successfully completed.
SQL> select round(avg(object_id)) from t where id<:v_id;
ROUND(AVG(OBJECT_ID))
---------------------
497
SQL> select * from table(dbms_xplan.display_cursor()); -->此次运行的SQL语句本该使用全表扫描，而此时选择了索引范围扫描
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
SQL_ID 0bx53mgt4qqnt, child number 0
-------------------------------------
select round(avg(object_id)) from t where id<:v_id
Plan hash value: 4270555908
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 9 | 72 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T_PK | 9 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID"<:V_ID)
SQL> drop table t;
三、总结
从上面的演示可以，由于绑定变量窥探特性，对于后续生成的执行计划，不仅套用了首次生成的执行计划，而且执行计划中的Row,Bytes,
Cost(%CPU)等都与首次生存执行计划得值相同。由此可知，尽管可以使用绑定变量解决OLTP系统中大量重复SQL的反复解析的问题。但绑定变量
可能会导致SQL语句选择非最佳的执行计划。尤其是对于存在数据倾斜的列，且生成了直方图更不宜于使用绑定变量。在Oracle
11g 中，自适
应特性从一定程度解决了绑定变量窥探所导致的问题。
四、延伸参考
Oracle自适应共享游标
绑定变量及其优缺点
父游标、子游标及共享游标
dbms_xplan之display_cursor函数的使用
dbms_xplan之display函数的使用
执行计划中各字段各模块描述
小小菜鸟一枚
