Oracle 绑定变量窥探

电玩女神 2022-05-15 13:09 152阅读 0赞

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自适应共享游标][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

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)

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;

三、总结  
 从上面的演示可以，由于绑定变量窥探特性，对于后续生成的执行计划，不仅套用了首次生成的执行计划，而且执行计划中的Row,Bytes,  
Cost(%CPU)等都与首次生存执行计划得值相同。由此可知，尽管可以使用绑定变量解决OLTP系统中大量重复SQL的反复解析的问题。但绑定变量  
可能会导致SQL语句选择非最佳的执行计划。尤其是对于存在数据倾斜的列，且生成了直方图更不宜于使用绑定变量。在Oracle 11g 中，自适  
应特性从一定程度解决了绑定变量窥探所导致的问题。

四、延伸参考  
 [Oracle自适应共享游标  
][Oracle] [绑定变量及其优缺点][Link 1]  
[ 父游标、子游标及共享游标][Link 2]  
[ dbms\_xplan之display\_cursor函数的使用][dbms_xplan_display_cursor]  
[ dbms\_xplan之display函数的使用][dbms_xplan_display]  
[ 执行计划中各字段各模块描述][Link 3]

[Oracle]: http://blog.csdn.net/robinson_0612/article/details/6923670
[Link 1]: http://blog.csdn.net/robinson_0612/article/details/6904229
[Link 2]: http://blog.csdn.net/robinson_0612/article/details/6893477
[dbms_xplan_display_cursor]: http://blog.csdn.net/robinson_0612/article/details/6866925
[dbms_xplan_display]: http://blog.csdn.net/robinson_0612/article/details/6866870
[Link 3]: http://blog.csdn.net/robinson_0612/article/details/6860007