Oracle 绑定变量窥探 - 军军小站|张军博客

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

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自适应共享游标
    绑定变量及其优缺点
    父游标、子游标及共享游标
    dbms_xplan之display_cursor函数的使用
    dbms_xplan之display函数的使用
    执行计划中各字段各模块描述

Oracle 绑定变量窥探

更多文章、技术交流、商务合作、联系博主

微信扫码或搜索：z360901061

微信扫一扫加我为好友

QQ号联系： 360901061

您的支持是博主写作最大的动力，如果您喜欢我的文章，感觉我的文章对您有帮助，请用微信扫描下面二维码支持博主2元、5元、10元、20元等您想捐的金额吧，狠狠点击下面给点支持吧，站长非常感激您！手机微信长按不能支付解决办法：请将微信支付二维码保存到相册，切换到微信，然后点击微信右上角扫一扫功能，选择支付二维码完成支付。

【本文对您有帮助就好】元

2元

5元

10元

20元

自定义