Oracle 绑定变量窥探

最新推荐文章于 2024-09-10 16:46:08 发布
转载 最新推荐文章于 2024-09-10 16:46:08 发布 · 960 阅读 · 0
文章标签：

#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.youkuaiyun.com/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.youkuaiyun.com/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 中，自适
应特性从一定程度解决了绑定变量窥探所导致的问题。

绑定变量窥探和直方图
zhaoyangjian724的专栏
01-18 1837
绑定变量窥视cbo的一个附属功能,关闭他不会影响是用cbo还是rbo,就算关闭了绑定变量窥视,cbo还是会利用别的统计信息(num_distinct,density等)来评估cost和cardinality,只 是无法使用直方图信息而已: SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ FROM
开启和关闭绑定变量窥探
zhaoyangjian724的专栏
05-14 711
绑定变量窥视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 377
随着具体输入值的不同，SQL的where条件的可选择率(Selectivity)和结果集的行数(Cardinality)可能会随之发生变化，而Selectivity和Cardinality的值会直接影响CBO对于相关执行步骤成本值的估算，进而影响CBO对SQL执行计划的选择。这就意味着随着具体输入值的不同，目标SQL执行计划可能会发生变化。对于不使用绑定变量的SQL而言，具体...
Oracle 绑定变量窥视
weixin_30329623的博客
08-09 737
绑定变量窥视功能是数据库的一个特性，自ORACLE9i版本开始引入，默认是开启的。 “绑定变量窥视”表示，查询优化器在第一次调用游标时，会观察用户定义的绑定变量的值，允许优化器来确认过滤条件的选择性，以及是否使用绑定变量代替了常量。之后调用游标时不会出现窥视，且会根据指针共享标准来共享游标，即使随后的调用使用不同的绑定值。 使用绑定变量窥视，第一次解析包含绑定谓词的SQL...
Oracle_绑定变量窥探测试
xiaoxu oracle专栏
08-14 2055
绑定变量窥探受隐含参数:_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
PostgreSQL 绑定变量窥探
lyu1026的博客
05-20 1265
今天我们要探讨的是 custom执行计划和通用执行计划。这一技术在 Oracle中被称为绑定变量窥视。但 Kingbase中并没有这样的定义，更严格地说，Kingbase叫做custom执行计划和通用执行计划。 什么是custom执行计划，什么是通用执行计划，我们先来看一个例子，我创建了一个100011行的表，其中有两列分别为 id、 name。在name列就2种类型的值，一种值为“aaa”，有...
直方图；绑定变量窥探；自适应游标
wbaug20th的专栏
11-19 1031
搭建环境 构建一个表 CREATE TABLE TEST2 AS SELECT * FROM DBA_OBJECTS; UPDATE SET OBJECT_ID = 11111 WHERE ROWNUM <=50000; 建立索引： CREATE INDEX IND_TEST2_OBJECT_ID ON TEST2(OBJECT_ID); 收集统计信息，不收集直方图 EXEC DBMS_STAT
Oracle数据倾斜问题,Oracle数据倾斜导致的问题-有绑定变量
weixin_34957608的博客
04-03 1457
Oracle数据倾斜导致的问题-有绑定变量参考整理---<<恩墨年货-SQL与性能优化>>场景二：在上一节实验可以知道，没有绑定变量时，数据倾斜问题在特定场景下可以用直方图解决，那么在有绑定变量情况下，数据倾斜问题单凭直方图可以解决吗？显然是不能的，Oracle绑定变量技术解决了SQL语句硬解析过多的问题，降低了资源的争用。但是绑定变量在引入cursor sharing，增...
绑定变量窥探
weixin_42575078的博客
03-29 708
Cost(%CPU)等都与首次生存执行计划得值相同。由此可知，尽管可以使用绑定变量解决OLTP系统中大量重复SQL的反复解析的问题。可能会导致SQL语句选择非最佳的执行计划。尤其是对于存在数据倾斜的列，且生成了直方图更不宜于使用绑定变量。在Oracle 11g 中，自适。从上面的演示可以，由于绑定变量窥探特性，对于后续生成的执行计划，不仅套用了首次生成的执行计划，而且执行计划中的Row,Bytes,应特性从一定程度解决了绑定变量窥探所导致的问题。
oracle早期绑定和晚期绑定,Oracle 绑定变量窥探
weixin_39880895的博客
04-04 123
Bind Peeking是Oracle 9i中引入的新特性，一直持续到Oracle 10g R2。它的作用就是在SQL语句硬分析的时候，查看一下当前SQL谓词的值，以便生成最佳的执行计划。而在oracle 9i之前的版本中，Oracle 只根据统计信息来做出执行计划。一、绑定变量窥探 使用SQL首次运行时的值来生成执行计划。后续再次运行该SQL语句则使用首次执行计划来执行。影响的版本：Ora...
绑定变量和游标 ，及绑定变量窥探
文档搬运工
03-18 336
原文地址：https://docs.oracle.com/en/database/oracle/oracle-database/12.2/tgsql/improving-rwp-cursor-sharing.html#GUID-971F4652-3950-4662-82DE-713DDEED317C -- 进行3个查询，不使用绑定变量 SYS@test&gt;SELECT SUM(salar...
Oralce性能优化-绑定变量窥视
oradbm的博客
09-11 394
查看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
oracle绑定变量窥探,动态SQL的dbms_sql.bind_variable无法触发绑定变量窥探
weixin_35448535的博客
04-06 571
对表格的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...
SQL优化【基础07】 - 绑定变量窥视与自适应游标共享
N
09-01 1858
1.绑定变量窥视(bind peeking)出现的背景 出现在ORACLE9I之后，在此之前ORACLE是按一定比例来预估结果集，在日常的应用中ORACLE 发现有些时候这个比例并不是最优的，于是引入了这项技术来进一步的优化； 2.什么是窥视？ var a number; exec :a :=88; select status from t1 where object_id=:a;
Oracle绑定变量窥视与自适应游标共享
dnuiking的博客
09-10 1622
创建test表，列status存在2个值，有数据倾斜，在列st问题：如果SQL硬解析生成执行计划时“窥视”到的绑定变量刚好只适合少部分数据，不适合其他大多数数据，那么直到下一次硬解析前（一般是收集统计信息后才会触发硬解析），都会使用这个不优的执行计划。解决：1.收集直方图，当查询Active的时候，直方图观察到绝大多数都是Active，则会走全表扫描，当查询Inactive的时候，直方图观察到绝大多数都是Inactive，则会走索引扫描。
ORACLE绑定变量窥视有什么作用？
MKing's Blog
03-21 3601
ORACLE绑定变量窥视有什么作用？在OLTP系统中绑定变量性能的提高很明显，这个大家都清楚，但是绑定变量有时也会产生一些不好的执行计划，特别是需要直方图的列。从ORACLE9i开始提供了绑定变量窥视的功能，就是在ORACLE第一次解析SQL时会将变量的真实值代入产生执行计划，以后对所有的同样的绑定变量SQL都采用这个执行计划了。如果第一次的真实值恰好是比较特殊的值，那这将严重影响产生的执行计
oracle 11g 新特性之动态绑定变量窥视(一)
cpbr82909536的博客
04-09 344
以前在it168发过，现在转过来。 1. 11g之前的绑定变量窥视 我们都知道，为了能够让SQL语句共享执行计划，oracle始终都是强调在进行应用系统的设计时，必须使用绑定变量，也就是用一...
Oracle 某一天绑定变量的语句，在第一次传入不同的参数值时，会执行很慢，是怎么回事
最新发布
03-30
<think>好的，用户问的是Oracle数据库中某一天绑定变量的语句，在第一次传入不同的参数值时执行很慢，是怎么回事。我需要分步骤分析这个问题。 首先，绑定变量在Oracle中的作用是减少硬解析，通过共享执行计划来提高效率。但有时候第一次使用不同参数时变慢，可能涉及到执行计划的问题。这时候要考虑绑定变量窥探（Bind Peeking）的影响。当第一次执行带有绑定变量的语句时，Oracle会窥探传入的值，生成一个执行计划。如果这个值不具有代表性，比如极端值，可能会导致后续查询使用不合适的计划，特别是当数据分布不均匀时。例如，如果第一次传入的值返回的数据量很大，优化器可能选择全表扫描，而实际后续查询可能参数对应的数据量很小，更适合索引扫描，这时候第一次执行就会变慢。 接下来要考虑的是直方图统计信息。Oracle的直方图帮助优化器了解数据分布，但如果没有收集或者收集不准确，优化器可能无法正确估计选择率，导致生成低效的执行计划。当第一次传入的参数对应的数据分布与直方图信息不符时，执行计划可能不佳，导致性能问题。 另外，硬解析的发生也可能导致延迟。第一次执行绑定变量语句时，Oracle需要做硬解析，生成执行计划，这过程本身比较耗时。如果系统资源紧张，比如高并发或内存不足，硬解析时间会更长，从而影响第一次执行的速度。 还有自适应游标共享（ACS）的问题。在Oracle 11g之后引入的ACS功能，允许对不同绑定值使用不同的执行计划。但如果ACS没有正确触发，或者游标没有被标记为绑定敏感，可能会导致重复使用不合适的计划，影响性能。 此外，数据倾斜和索引使用不当也是可能的原因。如果某个绑定值对应的数据量极大，而执行计划没有正确使用索引，或者索引统计信息不准确，也会导致第一次执行缓慢。 针对这些可能性，解决办法可能包括检查绑定变量窥探的设置，确保统计信息准确，特别是直方图的收集。可能需要使用SQL Profile或SQL Plan Management来固定更优的执行计划。另外，升级到更高版本的Oracle，利用自适应执行计划等新特性，或者手动使用提示（hint）来指导优化器选择正确的计划也是可行的方法。 最后，用户可能需要具体分析当时的执行计划，查看统计信息是否最新，是否存在数据倾斜，以及绑定变量的值是否导致了不合适的计划选择。通过这些步骤，可以定位问题所在并采取相应的优化措施。</think>这个问题通常与Oracle的**绑定变量窥探（Bind Peeking）**和**执行计划固化**机制有关。以下是分步解释和解决方案： --- ### **1. 绑定变量窥探（Bind Peeking）** - **现象**： 当第一次执行带有绑定变量的SQL语句时，Oracle会窥探（peek）传入的绑定变量值，并根据该值生成一个执行计划。如果该值具有极端性（例如极小的值或极多的重复值），可能导致生成的执行计划不适合后续其他参数值。 - **问题场景**： 例如，表 `orders` 中 `status` 字段数据分布不均： - 值 `'COMPLETED'` 占99%的数据（适合全表扫描）， - 值 `'PENDING'` 占1%的数据（适合索引扫描）。 若第一次传入 `status = 'COMPLETED'`，Oracle可能生成全表扫描计划；后续传入 `'PENDING'` 时，仍强制使用全表扫描，导致性能下降。 --- ### **2. 执行计划固化** - **原因**： 生成的执行计划会被缓存到共享池（Shared Pool）中，后续相同SQL（即使绑定变量值不同）会复用该计划。若初始计划不适用于新参数，性能问题会持续。 --- ### **3. 直方图（Histogram）缺失** - **关键影响**： 若表的列未收集直方图统计信息，优化器无法感知数据分布不均，可能错误估计选择率（Selectivity）。例如： ```sql SELECT * FROM orders WHERE status = :status; -- 未收集直方图时，优化器默认均匀分布 ``` --- ### **4. 自适应游标共享（Adaptive Cursor Sharing, ACS）** - **11g+ 特性**： Oracle 11g引入ACS，允许为不同绑定变量值生成不同执行计划。但需满足以下条件： - 游标被标记为**绑定敏感（Bind Sensitive）**， - 执行计划需根据变量值变化动态调整。 **若ACS未生效**，可能导致计划固化问题。 --- ### **解决方案** #### **方法1：收集统计信息与直方图** ```sql -- 收集表统计信息并生成直方图 BEGIN DBMS_STATS.GATHER_TABLE_STATS( ownname => 'SCHEMA_NAME', tabname => 'ORDERS', method_opt => 'FOR COLUMNS SIZE 254 STATUS' -- 为列生成直方图 ); END; ``` #### **方法2：强制优化器重新生成计划** - 使用 `REOPT` 提示（12c+）： ```sql SELECT /*+ REOPT */ * FROM orders WHERE status = :status; ``` - 清空共享池中的缓存计划（谨慎使用）： ```sql ALTER SYSTEM FLUSH SHARED_POOL; ``` #### **方法3：使用SQL Profile或SPM固定计划** - 通过SQL Tuning Advisor生成更优的SQL Profile： ```sql DECLARE ret VARCHAR2(100); BEGIN ret := DBMS_SQLTUNE.CREATE_TUNING_TASK( sql_text => 'SELECT * FROM orders WHERE status = :status', scope => 'COMPREHENSIVE', time_limit => 60 ); DBMS_SQLTUNE.EXECUTE_TUNING_TASK(ret); END; ``` - 使用SQL Plan Management（SPM）固定最佳计划： ```sql -- 从AWR或游标缓存加载计划 DECLARE plans PLS_INTEGER; BEGIN plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id => 'g4w4hj5m6k7uj' -- 替换为实际SQL_ID ); END; ``` #### **方法4：升级或使用自适应特性** - 若使用Oracle 12c+，开启**自适应执行计划**： ```sql ALTER SESSION SET optimizer_adaptive_plans = TRUE; ``` --- ### **验证步骤** 1. 检查执行计划变化： ```sql SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id => 'g4w4hj5m6k7uj')); ``` 2. 确认直方图收集： ```sql SELECT COLUMN_NAME, HISTOGRAM FROM DBA_TAB_COL_STATISTICS WHERE TABLE_NAME = 'ORDERS' AND COLUMN_NAME = 'STATUS'; ``` --- 通过以上方法，可以定位是绑定变量窥探、统计信息缺失还是执行计划固化导致的问题，并针对性优化。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值