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、创建演示环境

  1. SQL> select * from v$version where rownum<2;    -->查看当前数据库版本                                            
  2.                                                                                                                 
  3. BANNER                                                                                                          
  4. ----------------------------------------------------------------                                                 
  5. Oracle Database 10g Release 10.2.0.3.0 - 64bit Production                                                       
  6.                                                                                                                 
  7. SQL> create table t(id,owner,object_id) as       -->创建测试表t                                                  
  8.   2  select rownum,owner,object_id from all_objects where rownum<=1000;                                         
  9.                                                                                                                 
  10. SQL> alter table t add constraint t_pk primary key(id);  -->为表t添加主键                                        
  11.                                                                                                                 
  12. SQL> begin                                               -->收集统计信息,此处未生成直方图信息                    
  13.   2  dbms_stats.gather_table_stats(                                                                             
  14.   3  ownname=>'SCOTT',                                                                                          
  15.   4  tabname=>'T',                                                                                              
  16.   5  estimate_percent=>100,                                                                                     
  17.   6  method_opt=>'for all columns size 1');                                                                     
  18.   7  end;                                                                                                       
  19.   8  /                                                                                                          
  20.                                                                                                                 
  21. PL/SQL procedure successfully completed.                                                                        
  22.                                                                                                                 
  23. SQL> select count(id),count(distinct id),min(id),max(id) from t;  -->查看值的分布情况                            
  24.                                                                                                                 
  25.  COUNT(ID) COUNT(DISTINCTID)    MIN(ID)    MAX(ID)                                                              
  26. ---------- ----------------- ---------- ----------                                                               
  27.       1000              1000          1       1000                                                              

    2、未使用绑定变量情形下SQL语句的执行计划       

view plaincopy to clipboardprint?
  1. SQL> select sum(object_id) from t where id<900;     -->发布SQL 查询语句                                                 
  2.                                                                                                                        
  3. SUM(OBJECT_ID)                                                                                                         
  4. --------------                                                                                                          
  5.         446549                                                                                                         
  6.                                                                                                                        
  7. SQL> select * from table(dbms_xplan.display_cursor()); -->由其执行计划可知，当前的SQL语句使用了全表扫描                 
  8. /**************************************************/                                                                   
  9. /* Author: Robinson Cheng                         */                                                                   
  10. /* Blog:   http://blog.csdn.net/robinson_0612     */                                                                   
  11. /* MSN:    robinson_0612@hotmail.com              */                                                                   
  12. /* QQ:     645746311                              */                                                                   
  13. /**************************************************/                                                                   
  14.                                                                                                                                
  15. PLAN_TABLE_OUTPUT                                                                                                      
  16. ---------------------------------------------------------------------------                                             
  17. SQL_ID  bz6h6fdsxgjka, child number 0                                                                                  
  18. -------------------------------------                                                                                   
  19. select sum(object_id) from t where id<900                                                                              
  20.                                                                                                                        
  21. Plan hash value: 2966233522                                                                                            
  22.                                                                                                                        
  23. ---------------------------------------------------------------------------                                             
  24. | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                            
  25. ---------------------------------------------------------------------------                                             
  26. |   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |                                            
  27. |   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |                                            
  28. |*  2 |   TABLE ACCESS FULL| T    |   900 |  7200 |     3   (0)| 00:00:01 |                                            
  29. ---------------------------------------------------------------------------                                             
  30.                                                                                                                        
  31. Predicate Information (identified by operation id):                                                                    
  32. ---------------------------------------------------                                                                     
  33.                                                                                                                        
  34.    2 - filter("ID"<900)                                                                                                
  35.                                                                                                                        
  36. SQL> select sum(object_id) from t where id<10;   -->发布另一条SQL 查询语句                                              
  37.                                                                                                                        
  38. SQL> select * from table(dbms_xplan.display_cursor()); -->此时的查询生成的执行计划走索引范围扫描                        
  39.                                                        -->由于字面量不同，因此两条SQL语句生成了不同的SQL_ID与执行计划   
  40. PLAN_TABLE_OUTPUT                                                                                                      
  41. --------------------------------------------------------------------------                                              
  42. SQL_ID  6y2280pyvacfq, child number 0                                                                                  
  43. -------------------------------------                                                                                   
  44. select sum(object_id) from t where id<10                                                                               
  45.                                                                                                                        
  46. Plan hash value: 4270555908                                                                                            
  47.                                                                                                                        
  48. -------------------------------------------------------------------------------------                                   
  49. | Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                  
  50. -------------------------------------------------------------------------------------                                   
  51. |   0 | SELECT STATEMENT             |      |       |       |     3 (100)|          |                                  
  52. |   1 |  SORT AGGREGATE              |      |     1 |     8 |            |          |                                  
  53. |   2 |   TABLE ACCESS BY INDEX ROWID| T    |     9 |    72 |     3   (0)| 00:00:01 |                                  
  54. |*  3 |    INDEX RANGE SCAN          | T_PK |     9 |       |     2   (0)| 00:00:01 |                                  
  55. -------------------------------------------------------------------------------------                                   
  56.                                                                                                                        
  57. Predicate Information (identified by operation id):                                                                    
  58. ---------------------------------------------------                                                                     
  59.                                                                                                                        
  60.    3 - access("ID"<10)                                                                                                 
  • 1
  • 2
  • 下一页

相关内容