Oracle 绑定变量窥探

最新推荐文章于 2024-03-29 08:52:55 发布
最新推荐文章于 2024-03-29 08:52:55 发布
阅读量882 收藏
点赞数
分类专栏： SQL优化 文章标签： Oracle PLSQL 索引
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]  view plain copy print ?
  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语句的执行计划       

[sql]  view plain copy print ?
  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)                                                                                                 

    3、使用绑定变量情形下的执行计划             

[sql]  view plain copy print ?
  1. SQL> variable v_id number;   -->定义绑定变量                                                                           
  2. SQL> exec :v_id:=900;        -->给绑定变量赋值                                                                         
  3.                                                                                                                        
  4. PL/SQL procedure successfully completed.                                                                               
  5.                                                                                                                        
  6. SQL> select sum(object_id) from t where id<:v_id;                                                                      
  7.                                                                                                                        
  8. SUM(OBJECT_ID)                                                                                                         
  9. --------------                                                                                                         
  10.         446549                                                                                                         
  11.                                                                                                                        
  12. SQL> select * from table(dbms_xplan.display_cursor());   -->此时上一条SQL语句走了全表扫描，其SQL_ID 为7qcp6urqh7d2j    
  13.                                                                                                                        
  14. PLAN_TABLE_OUTPUT                                                                                                      
  15. ------------------------------------------------------------------------------                                         
  16. SQL_ID  7qcp6urqh7d2j, child number 0                                                                                  
  17. -------------------------------------                                                                                  
  18. select sum(object_id) from t where id<:v_id                                                                            
  19.                                                                                                                        
  20. Plan hash value: 2966233522                                                                                            
  21.                                                                                                                        
  22. ---------------------------------------------------------------------------                                            
  23. | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                            
  24. ---------------------------------------------------------------------------                                            
  25. |   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |                                            
  26. |   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |                                            
  27. |*  2 |   TABLE ACCESS FULL| T    |   900 |  7200 |     3   (0)| 00:00:01 |                                            
  28. ---------------------------------------------------------------------------                                            
  29.                                                                                                                        
  30. Predicate Information (identified by operation id):                                                                    
  31. ---------------------------------------------------                                                                    
  32.                                                                                                                        
  33.    2 - filter("ID"<:V_ID)    -->谓词信息表明此时使用了绑定变量                                                         
  34.                                                                                                                        
  35. SQL> exec :v_id:=10;         -->对绑定变量重新赋值                                                                     
  36.                                                                                                                        
  37. PL/SQL procedure successfully completed.                                                                               
  38.                                                                                                                        
  39. SQL> select sum(object_id) from t where id<:v_id;   -->再次执行SQL语句                                                 
  40.                                                                                                                        
  41. SUM(OBJECT_ID)                                                                                                         
  42. --------------                                                                                                         
  43.            254                                                                                                         
  44.                                                                                                                        
  45. SQL> select * from table(dbms_xplan.display_cursor());  -->此时执行计划中依然选择的是全表扫描                          
  46.                                                         -->其SQL_ID同上一次执行的SQL语句相同，即实现了完全共享         
  47. PLAN_TABLE_OUTPUT                                       -->对于未使用绑定变量时id<10的情形则为走索引范围扫描           
  48. -----------------------------------------------         -->由此可知，并非最佳的执行计划被执行                          
  49. SQL_ID  7qcp6urqh7d2j, child number 0                                                                                  
  50. -------------------------------------                                                                                  
  51. select sum(object_id) from t where id<:v_id                                                                            
  52.                                                                                                                        
  53. Plan hash value: 2966233522                                                                                            
  54.                                                                                                                        
  55. ---------------------------------------------------------------------------                                            
  56. | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                            
  57. ---------------------------------------------------------------------------                                            
  58. |   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |                                            
  59. |   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |                                            
  60. |*  2 |   TABLE ACCESS FULL| T    |   900 |  7200 |     3   (0)| 00:00:01 |                                            
  61. ---------------------------------------------------------------------------                                            
  62.                                                                                                                        
  63. Predicate Information (identified by operation id):                                                                    
  64. ---------------------------------------------------                                                                    
  65.                                                                                                                        
  66.    2 - filter("ID"<:V_ID)                                                                                              
  67.                                                                                                                        
  68. SQL> alter system flush shared_pool;    -->清空共享池，此时共享的父游标与子游标全部释放                                
  69.                                                                                                                            
  70. SQL> print v_id;                                                                                                       
  71.                                                                                                                        
  72.       V_ID                                                                                                             
  73. ----------                                                                                                             
  74.         10                                                                                                             
  75.                                                                                                                        
  76. SQL> select round(avg(object_id)) from t where id<:v_id;   -->使用id<10来执行SQL语句                                   
  77.                                                                                                                        
  78. ROUND(AVG(OBJECT_ID))                                                                                                  
  79. ---------------------                                                                                                  
  80.                    28                                                                                                  
  81.                                                                                                                        
  82. SQL> select * from table(dbms_xplan.display_cursor());    -->此时该SQL语句使用了最佳的执行计划，即走索引范围扫描       
  83.                                                                                                                        
  84. PLAN_TABLE_OUTPUT                                                                                                      
  85. ---------------------------------------------------------------------------------------                                
  86. SQL_ID  0bx53mgt4qqnt, child number 0                                                                                  
  87. -------------------------------------                                                                                  
  88. select round(avg(object_id)) from t where id<:v_id                                                                     
  89.                                                                                                                        
  90. Plan hash value: 4270555908                                                                                            
  91.                                                                                                                        
  92. -------------------------------------------------------------------------------------                                  
  93. | Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                  
  94. -------------------------------------------------------------------------------------                                  
  95. |   0 | SELECT STATEMENT             |      |       |       |     3 (100)|          |                                  
  96. |   1 |  SORT AGGREGATE              |      |     1 |     8 |            |          |                                  
  97. |   2 |   TABLE ACCESS BY INDEX ROWID| T    |     9 |    72 |     3   (0)| 00:00:01 |                                  
  98. |*  3 |    INDEX RANGE SCAN          | T_PK |     9 |       |     2   (0)| 00:00:01 |                                  
  99. -------------------------------------------------------------------------------------                                  
  100.                                                                                                                        
  101. Predicate Information (identified by operation id):                                                                    
  102. ---------------------------------------------------                                                                    
  103.                                                                                                                        
  104.    3 - access("ID"<:V_ID)                                                                                              
  105.                                                                                                                        
  106. SQL> exec :v_id:=900;                   -->为变量赋新值                                                                
  107.                                                                                                                        
  108. PL/SQL procedure successfully completed.                                                                               
  109.                                                                                                                        
  110. SQL> select round(avg(object_id)) from t where id<:v_id;                                                               
  111.                                                                                                                        
  112. ROUND(AVG(OBJECT_ID))                                                                                                  
  113. ---------------------                                                                                                  
  114.                   497                                                                                                  
  115.                                                                                                                        
  116. SQL> select * from table(dbms_xplan.display_cursor()); -->此次运行的SQL语句本该使用全表扫描，而此时选择了索引范围扫描  
  117.                                                                                                                        
  118. PLAN_TABLE_OUTPUT                                                                                                      
  119. --------------------------------------------------------------------------------------                                 
  120. SQL_ID  0bx53mgt4qqnt, child number 0                                                                                  
  121. -------------------------------------                                                                                  
  122. select round(avg(object_id)) from t where id<:v_id                                                                     
  123.                                                                                                                        
  124. Plan hash value: 4270555908                                                                                            
  125.                                                                                                                        
  126. -------------------------------------------------------------------------------------                                  
  127. | Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                  
  128. -------------------------------------------------------------------------------------                                  
  129. |   0 | SELECT STATEMENT             |      |       |       |     3 (100)|          |                                  
  130. |   1 |  SORT AGGREGATE              |      |     1 |     8 |            |          |                                  
  131. |   2 |   TABLE ACCESS BY INDEX ROWID| T    |     9 |    72 |     3   (0)| 00:00:01 |                                  
  132. |*  3 |    INDEX RANGE SCAN          | T_PK |     9 |       |     2   (0)| 00:00:01 |                                  
  133. -------------------------------------------------------------------------------------                                  
  134.                                                                                                                        
  135. Predicate Information (identified by operation id):                                                                    
  136. ---------------------------------------------------                                                                    
  137.                                                                                                                        
  138.    3 - access("ID"<:V_ID)                                                                                              
  139.                                                                                                                        
  140. SQL> drop table t;                                                                                                     

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

[sql]  view plain copy print ?
  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)                                                                                                 

    3、使用绑定变量情形下的执行计划             

[sql]  view plain copy print ?
  1. SQL> variable v_id number;   -->定义绑定变量                                                                           
  2. SQL> exec :v_id:=900;        -->给绑定变量赋值                                                                         
  3.                                                                                                                        
  4. PL/SQL procedure successfully completed.                                                                               
  5.                                                                                                                        
  6. SQL> select sum(object_id) from t where id<:v_id;                                                                      
  7.                                                                                                                        
  8. SUM(OBJECT_ID)                                                                                                         
  9. --------------                                                                                                         
  10.         446549                                                                                                         
  11.                                                                                                                        
  12. SQL> select * from table(dbms_xplan.display_cursor());   -->此时上一条SQL语句走了全表扫描，其SQL_ID 为7qcp6urqh7d2j    
  13.                                                                                                                        
  14. PLAN_TABLE_OUTPUT                                                                                                      
  15. ------------------------------------------------------------------------------                                         
  16. SQL_ID  7qcp6urqh7d2j, child number 0                                                                                  
  17. -------------------------------------                                                                                  
  18. select sum(object_id) from t where id<:v_id                                                                            
  19.                                                                                                                        
  20. Plan hash value: 2966233522                                                                                            
  21.                                                                                                                        
  22. ---------------------------------------------------------------------------                                            
  23. | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                            
  24. ---------------------------------------------------------------------------                                            
  25. |   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |                                            
  26. |   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |                                            
  27. |*  2 |   TABLE ACCESS FULL| T    |   900 |  7200 |     3   (0)| 00:00:01 |                                            
  28. ---------------------------------------------------------------------------                                            
  29.                                                                                                                        
  30. Predicate Information (identified by operation id):                                                                    
  31. ---------------------------------------------------                                                                    
  32.                                                                                                                        
  33.    2 - filter("ID"<:V_ID)    -->谓词信息表明此时使用了绑定变量                                                         
  34.                                                                                                                        
  35. SQL> exec :v_id:=10;         -->对绑定变量重新赋值                                                                     
  36.                                                                                                                        
  37. PL/SQL procedure successfully completed.                                                                               
  38.                                                                                                                        
  39. SQL> select sum(object_id) from t where id<:v_id;   -->再次执行SQL语句                                                 
  40.                                                                                                                        
  41. SUM(OBJECT_ID)                                                                                                         
  42. --------------                                                                                                         
  43.            254                                                                                                         
  44.                                                                                                                        
  45. SQL> select * from table(dbms_xplan.display_cursor());  -->此时执行计划中依然选择的是全表扫描                          
  46.                                                         -->其SQL_ID同上一次执行的SQL语句相同，即实现了完全共享         
  47. PLAN_TABLE_OUTPUT                                       -->对于未使用绑定变量时id<10的情形则为走索引范围扫描           
  48. -----------------------------------------------         -->由此可知，并非最佳的执行计划被执行                          
  49. SQL_ID  7qcp6urqh7d2j, child number 0                                                                                  
  50. -------------------------------------                                                                                  
  51. select sum(object_id) from t where id<:v_id                                                                            
  52.                                                                                                                        
  53. Plan hash value: 2966233522                                                                                            
  54.                                                                                                                        
  55. ---------------------------------------------------------------------------                                            
  56. | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                            
  57. ---------------------------------------------------------------------------                                            
  58. |   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |                                            
  59. |   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |                                            
  60. |*  2 |   TABLE ACCESS FULL| T    |   900 |  7200 |     3   (0)| 00:00:01 |                                            
  61. ---------------------------------------------------------------------------                                            
  62.                                                                                                                        
  63. Predicate Information (identified by operation id):                                                                    
  64. ---------------------------------------------------                                                                    
  65.                                                                                                                        
  66.    2 - filter("ID"<:V_ID)                                                                                              
  67.                                                                                                                        
  68. SQL> alter system flush shared_pool;    -->清空共享池，此时共享的父游标与子游标全部释放                                
  69.                                                                                                                            
  70. SQL> print v_id;                                                                                                       
  71.                                                                                                                        
  72.       V_ID                                                                                                             
  73. ----------                                                                                                             
  74.         10                                                                                                             
  75.                                                                                                                        
  76. SQL> select round(avg(object_id)) from t where id<:v_id;   -->使用id<10来执行SQL语句                                   
  77.                                                                                                                        
  78. ROUND(AVG(OBJECT_ID))                                                                                                  
  79. ---------------------                                                                                                  
  80.                    28                                                                                                  
  81.                                                                                                                        
  82. SQL> select * from table(dbms_xplan.display_cursor());    -->此时该SQL语句使用了最佳的执行计划，即走索引范围扫描       
  83.                                                                                                                        
  84. PLAN_TABLE_OUTPUT                                                                                                      
  85. ---------------------------------------------------------------------------------------                                
  86. SQL_ID  0bx53mgt4qqnt, child number 0                                                                                  
  87. -------------------------------------                                                                                  
  88. select round(avg(object_id)) from t where id<:v_id                                                                     
  89.                                                                                                                        
  90. Plan hash value: 4270555908                                                                                            
  91.                                                                                                                        
  92. -------------------------------------------------------------------------------------                                  
  93. | Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                  
  94. -------------------------------------------------------------------------------------                                  
  95. |   0 | SELECT STATEMENT             |      |       |       |     3 (100)|          |                                  
  96. |   1 |  SORT AGGREGATE              |      |     1 |     8 |            |          |                                  
  97. |   2 |   TABLE ACCESS BY INDEX ROWID| T    |     9 |    72 |     3   (0)| 00:00:01 |                                  
  98. |*  3 |    INDEX RANGE SCAN          | T_PK |     9 |       |     2   (0)| 00:00:01 |                                  
  99. -------------------------------------------------------------------------------------                                  
  100.                                                                                                                        
  101. Predicate Information (identified by operation id):                                                                    
  102. ---------------------------------------------------                                                                    
  103.                                                                                                                        
  104.    3 - access("ID"<:V_ID)                                                                                              
  105.                                                                                                                        
  106. SQL> exec :v_id:=900;                   -->为变量赋新值                                                                
  107.                                                                                                                        
  108. PL/SQL procedure successfully completed.                                                                               
  109.                                                                                                                        
  110. SQL> select round(avg(object_id)) from t where id<:v_id;                                                               
  111.                                                                                                                        
  112. ROUND(AVG(OBJECT_ID))                                                                                                  
  113. ---------------------                                                                                                  
  114.                   497                                                                                                  
  115.                                                                                                                        
  116. SQL> select * from table(dbms_xplan.display_cursor()); -->此次运行的SQL语句本该使用全表扫描，而此时选择了索引范围扫描  
  117.                                                                                                                        
  118. PLAN_TABLE_OUTPUT                                                                                                      
  119. --------------------------------------------------------------------------------------                                 
  120. SQL_ID  0bx53mgt4qqnt, child number 0                                                                                  
  121. -------------------------------------                                                                                  
  122. select round(avg(object_id)) from t where id<:v_id                                                                     
  123.                                                                                                                        
  124. Plan hash value: 4270555908                                                                                            
  125.                                                                                                                        
  126. -------------------------------------------------------------------------------------                                  
  127. | Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                  
  128. -------------------------------------------------------------------------------------                                  
  129. |   0 | SELECT STATEMENT             |      |       |       |     3 (100)|          |                                  
  130. |   1 |  SORT AGGREGATE              |      |     1 |     8 |            |          |                                  
  131. |   2 |   TABLE ACCESS BY INDEX ROWID| T    |     9 |    72 |     3   (0)| 00:00:01 |                                  
  132. |*  3 |    INDEX RANGE SCAN          | T_PK |     9 |       |     2   (0)| 00:00:01 |                                  
  133. -------------------------------------------------------------------------------------                                  
  134.                                                                                                                        
  135. Predicate Information (identified by operation id):                                                                    
  136. ---------------------------------------------------                                                                    
  137.                                                                                                                        
  138.    3 - access("ID"<:V_ID)                                                                                              
  139.                                                                                                                        
  140. SQL> drop table t;                                                                                                     

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

Flexes
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
绑定变量窥探和直方图
zhaoyangjian724的专栏
01-18 1767
绑定变量窥视cbo的一个附属功能,关闭他不会影响是用cbo还是rbo,就算关闭了绑定变量窥视,cbo还是会利用别的统计信息(num_distinct,density等)来评估cost和cardinality,只 是无法使用直方图信息而已: SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ FROM
开启和关闭绑定变量窥探
zhaoyangjian724的专栏
05-14 643
绑定变量窥视cbo的一个附属功能,关闭他不会影响是用cbo还是rbo,就算关闭了绑定变量窥视,cbo还是会利用别的统计信息(num_distinct,density等)来评估cost和cardinality,只 是无法使用直方图信息而已: SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ FROM...
Oracle绑定变量窥探
weixin_33736832的博客
03-07 307
随着具体输入值的不同，SQL的where条件的可选择率(Selectivity)和结果集的行数(Cardinality)可能会随之发生变化，而Selectivity和Cardinality的值会直接影响CBO对于相关执行步骤成本值的估算，进而影响CBO对SQL执行计划的选择。这就意味着随着具体输入值的不同，目标SQL执行计划可能会发生变化。对于不使用绑定变量的SQL而言，具体...
Oracle_绑定变量窥探测试
xiaoxu oracle专栏
08-14 1960
绑定变量窥探受隐含参数:_optim_peek_user_binds的限制，自9i之后改参数默认为True,为自动开启状态： 查询sql SQL> col name for a50 SQL> col value for a10 SQL> col description for a100 SQL> select a.ksppinm name, b.ksppstvl value, a.ksp
Oracle 绑定变量窥视
weixin_30329623的博客
08-09 549
绑定变量窥视功能是数据库的一个特性，自ORACLE9i版本开始引入，默认是开启的。 “绑定变量窥视”表示，查询优化器在第一次调用游标时，会观察用户定义的绑定变量的值，允许优化器来确认过滤条件的选择性，以及是否使用绑定变量代替了常量。之后调用游标时不会出现窥视，且会根据指针共享标准来共享游标，即使随后的调用使用不同的绑定值。 使用绑定变量窥视，第一次解析包含绑定谓词的SQL...
KingbaseES 绑定变量窥探机制
lyu1026的博客
04-26 1324
概述： 对于数据严重倾斜的，极端如以下例子，不同的传入值，可能执行计划不同，制定执行计划时，就要求知道变量的值。对于绑定变量的情况，我们知道Oracle 有_optim_peek_user_binds 参数，控制是否启用变量窥探。KingbaseES 也有类似参数，控制是否启用变量窥探。 窥探机制 KingbaseES 采用以下判断机制，决定是否固定执行计划： 前5次执行时，每次都会根据实际传入的实际绑定变量新生成执行计划进行执行，即每次都是硬解析，同时会记录这5次的执行计划； 当第.
什么是oracle 谓词越界,oracle谓词越界、绑定变量窥探
weixin_39811166的博客
04-07 420
最近有一客户晚上新导入了一批数据到数据库中，第二天发现业务变慢，主要是其中有一条核心业务SQL执行计划走错导致。结果排查发现客户在导入数据后并未重新收集统计信息，SQL使用绑定变量，窥探的变量刚好是越界，导致SQL第一次硬解析生成的执行计划走错，再加上10G的库导致接下来的执行计划直接沿用内存中的执行计划(默认情况下直到SQL对应的share cursor被age out出了share pool才...
绑定变量及其优缺点
fsfsdfsdw的博客
01-21 3153
绑定变量及其优缺点
oracle早期绑定和晚期绑定,Oracle 绑定变量窥探
weixin_39880895的博客
04-04 89
Bind Peeking是Oracle 9i中引入的新特性，一直持续到Oracle 10g R2。它的作用就是在SQL语句硬分析的时候，查看一下当前SQL谓词的值，以便生成最佳的执行计划。而在oracle 9i之前的版本中，Oracle 只根据统计信息来做出执行计划。一、绑定变量窥探 使用SQL首次运行时的值来生成执行计划。后续再次运行该SQL语句则使用首次执行计划来执行。影响的版本：Ora...
Oralce性能优化-绑定变量窥视
oradbm的博客
09-11 314
查看Oracle SQL绑定变量方bai法如下： alter session set nls_date_format = ‘yyyy-mm-dd,hh24:mi:ss’; set linesize 400 col sql_Id format a20 col name format a20 col datatype_string format a14 col value_string format a20 –这个dusql从内存中读zhi取绑定变量值信息，dao若是不在内存中，则使用下一个sql select
SQL优化【基础07】 - 绑定变量窥视与自适应游标共享
N
09-01 1791
1.绑定变量窥视(bind peeking)出现的背景 出现在ORACLE9I之后，在此之前ORACLE是按一定比例来预估结果集，在日常的应用中ORACLE 发现有些时候这个比例并不是最优的，于是引入了这项技术来进一步的优化； 2.什么是窥视？ var a number; exec :a :=88; select status from t1 where object_id=:a;
绑定变量窥探
weixin_42575078的博客
03-29 592
Cost(%CPU)等都与首次生存执行计划得值相同。由此可知，尽管可以使用绑定变量解决OLTP系统中大量重复SQL的反复解析的问题。可能会导致SQL语句选择非最佳的执行计划。尤其是对于存在数据倾斜的列，且生成了直方图更不宜于使用绑定变量。在Oracle 11g 中，自适。从上面的演示可以，由于绑定变量窥探特性，对于后续生成的执行计划，不仅套用了首次生成的执行计划，而且执行计划中的Row,Bytes,应特性从一定程度解决了绑定变量窥探所导致的问题。
ORACLE绑定变量窥视有什么作用？
MKing's Blog
03-21 3543
ORACLE绑定变量窥视有什么作用？在OLTP系统中绑定变量性能的提高很明显，这个大家都清楚，但是绑定变量有时也会产生一些不好的执行计划，特别是需要直方图的列。从ORACLE9i开始提供了绑定变量窥视的功能，就是在ORACLE第一次解析SQL时会将变量的真实值代入产生执行计划，以后对所有的同样的绑定变量SQL都采用这个执行计划了。如果第一次的真实值恰好是比较特殊的值，那这将严重影响产生的执行计
oracle绑定变量窥探,动态SQL的dbms_sql.bind_variable无法触发绑定变量窥探
weixin_35448535的博客
04-06 480
对表格的2个数据分布不均衡的列都做了直方图的统计，今天跟踪系统，发现居然没有触发绑定变量窥探。程序是用动态SQL开发的一个共用的功能，其中更就用到了：L_PROCESS_PHASE := 2;IF P_COL_VALUE_TAB.COUNT > 0 THENFOR I IN 1..P_COL_VALUE_TAB.LAST LOOPIF P_COL_VALUE_TAB.exists(I) TH...
oracle 11g 新特性之动态绑定变量窥视(一)
cpbr82909536的博客
04-09 269
以前在it168发过，现在转过来。 1. 11g之前的绑定变量窥视 我们都知道，为了能够让SQL语句共享执行计划，oracle始终都是强调在进行应用系统的设计时，必须使用绑定变量，也就是用一...
全新转转交易猫自带客服多模板全开源完整定制版源码
08-27
定制版全开源完整无授权 商品发布； 请在后台商品添加成功后， 再点击该商品管理，可重新编辑当前商品的所有信息及配图以及支付等等相关信息 可点击分享或者跳转，将链接地址进行发布分享 请在手机端打开访问 访问商品主要模板文件路径目录 咸鱼; http://你的域名地址/Xianyu_goods/ 代练妈妈;http://你的域名地址/dlmm/ 大麦商品;http://你的域名地址/dm/dzpj.php 大麦售票;http://你的域名地址/dm/dzp.php 交易猫; http://你的域名地址/jym/ QQ卡框工具;http://你的域名地址/kk/ 京东; http://你的域名地址/www.jd.com/index1.php 转转;http:/ 安装教程； 环境； PHP版本5.6，数据库版本MySQL 5.6 第一步源码上传解压 第二步找到config文件夹找到Conn.php修改数据库 第三步导入数据库sql.sql 第四步http://域名/wuyun.php登录后台 默认登录后台账号及密码； 默认账号；admin 默认密码；123456 源码内附详细搭建教程TXT文档
项目模板-硬件详细设计说明书.doc
最新发布
08-27
项目模板-硬件详细设计说明书.doc
oracle绑定变量
02-29
Oracle绑定变量是一种在SQL语句中使用的特殊语法，用于将变量与SQL语句中的参数进行绑定。通过使用绑定变量，可以提高SQL语句的执行效率和安全性。 在Oracle中，绑定变量使用冒号（:）作为前缀，并且在SQL语句中使用该变量的地方都需要加上冒号前缀。绑定变量可以在SQL语句执行之前进行赋值，并且可以多次重复使用。 绑定变量的主要优点有： 1. 提高性能：使用绑定变量可以减少SQL语句的解析时间，因为Oracle数据库可以缓存已解析的SQL语句和执行计划，重复使用绑定变量可以直接使用缓存中的执行计划，避免了每次都重新解析SQL语句。 2. 防止SQL注入攻击：通过使用绑定变量，可以将用户输入的数据作为参数传递给SQL语句，而不是将其直接拼接到SQL语句中。这样可以有效防止SQL注入攻击。 3. 简化代码：使用绑定变量可以减少代码量，避免了手动拼接SQL语句的麻烦。 下面是一个使用绑定变量的示例： ```sql DECLARE v_employee_id NUMBER := 100; v_employee_name VARCHAR2(100); BEGIN SELECT employee_name INTO v_employee_name FROM employees WHERE employee_id = :v_employee_id; DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name); END; ``` 在上面的示例中，使用了绑定变量:v_employee_id来代替SQL语句中的参数。在执行SQL语句之前，可以将v_employee_id赋值为具体的值，然后执行SQL语句，获取结果。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值