Oracle 绑定变量窥探

      Bind Peeking是Oracle 9i中引入的新特性,一直持续到Oracle 10g R2。它的作用就是在SQL语句硬分析的时候,查看一下当前SQL谓词的值
,以便生成最佳的执行计划。而在oracle 9i之前的版本中,Oracle 只根据统计信息来做出执行计划。

一、绑定变量窥探
    使用SQL首次运行时的值来生成执行计划。后续再次运行该SQL语句则使用首次执行计划来执行。
    影响的版本:Oracle 9i, Oracle 10g
    对于绑定变量列中的特殊值或非均匀分布列上的绑定变量会造成非高效的执行计划被选择并执行。
   
        要注意的是,Bind Peeking只发生在硬分析的时候,即SQL被第一次执行的时候,之后的变量将不会在做peeking。我们可以看出,Bind
    peeking并不能最终解决不同谓词导致选择不同执行计划的问题,它只能让SQL第一次执行的时候,执行计划选择更加准确,并不能帮助OLAP
    系统解决绑定变量导致执行计划选择错误的问题。这也是OLAP不应该使用绑定变量的一个原因。

        更确切地说,绑定变量窥探是在SQL解析的物理阶段,查询优化器将会窥探绑定变量的值并将其作为字面量来使用。即ORACLE首次解析
    SQL时会将变量的真实值代入产生执行计划,后续对所有使用该绑定变量SQL语句都采用首次生存的执行计划。如此这般?那性能究竟如何?
    结果是并非最佳的执行计划的使用。此问题在Oracle 11g中得以解决。  
        请参考:Oracle自适应共享游标

二、示例绑定变量窥探
    1、创建演示环境       

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

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

SQL> select sum(object_id) from t where id<900;     -->发布SQL 查询语句                                              
                                                                                                                     
SUM(OBJECT_ID)                                                                                                       
--------------                                                                                                       
        446549                                                                                                       
                                                                                                                     
SQL> select * from table(dbms_xplan.display_cursor()); -->由其执行计划可知,当前的SQL语句使用了全表扫描              
/**************************************************/                                                                 
/* Author: Robinson Cheng                         */                                                                 
/* Blog:   http://blog.youkuaiyun.com/robinson_0612     */                                                                 
/* MSN:    robinson_0612@hotmail.com              */                                                                 
/* QQ:     645746311                              */                                                                 
/**************************************************/                                                                 
				                                                                                                             
PLAN_TABLE_OUTPUT                                                                                                    
---------------------------------------------------------------------------                                          
SQL_ID  bz6h6fdsxgjka, child number 0                                                                                
-------------------------------------                                                                                
select sum(object_id) from t where id<900                                                                            
                                                                                                                     
Plan hash value: 2966233522                                                                                          
                                                                                                                     
---------------------------------------------------------------------------                                          
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                          
---------------------------------------------------------------------------                                          
|   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |                                          
|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |                                          
|*  2 |   TABLE ACCESS FULL| T    |   900 |  7200 |     3   (0)| 00:00:01 |                                          
---------------------------------------------------------------------------                                          
                                                                                                                     
Predicate Information (identified by operation id):                                                                  
---------------------------------------------------                                                                  
                                                                                                                     
   2 - filter("ID"<900)                                                                                              
                                                                                                                     
SQL> select sum(object_id) from t where id<10;   -->发布另一条SQL 查询语句                                           
                                                                                                                     
SQL> select * from table(dbms_xplan.display_cursor()); -->此时的查询生成的执行计划走索引范围扫描                     
                                                       -->由于字面量不同,因此两条SQL语句生成了不同的SQL_ID与执行计划
PLAN_TABLE_OUTPUT                                                                                                    
--------------------------------------------------------------------------                                           
SQL_ID  6y2280pyvacfq, child number 0                                                                                
-------------------------------------                                                                                
select sum(object_id) from t where id<10                                                                             
                                                                                                                     
Plan hash value: 4270555908                                                                                          
                                                                                                                     
-------------------------------------------------------------------------------------                                
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                
-------------------------------------------------------------------------------------                                
|   0 | SELECT STATEMENT             |      |       |       |     3 (100)|          |                                
|   1 |  SORT AGGREGATE              |      |     1 |     8 |            |          |                                
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |     9 |    72 |     3   (0)| 00:00:01 |                                
|*  3 |    INDEX RANGE SCAN          | T_PK |     9 |       |     2   (0)| 00:00:01 |                                
-------------------------------------------------------------------------------------                                
                                                                                                                     
Predicate Information (identified by operation id):                                                                  
---------------------------------------------------                                                                  
                                                                                                                     
   3 - access("ID"<10)                                                                                               

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

SQL> variable v_id number;   -->定义绑定变量                                                                         
SQL> exec :v_id:=900;        -->给绑定变量赋值                                                                       
                                                                                                                     
PL/SQL procedure successfully completed.                                                                             
                                                                                                                     
SQL> select sum(object_id) from t where id<:v_id;                                                                    
                                                                                                                     
SUM(OBJECT_ID)                                                                                                       
--------------                                                                                                       
        446549                                                                                                       
                                                                                                                     
SQL> select * from table(dbms_xplan.display_cursor());   -->此时上一条SQL语句走了全表扫描,其SQL_ID 为7qcp6urqh7d2j  
                                                                                                                     
PLAN_TABLE_OUTPUT                                                                                                    
------------------------------------------------------------------------------                                       
SQL_ID  7qcp6urqh7d2j, child number 0                                                                                
-------------------------------------                                                                                
select sum(object_id) from t where id<:v_id                                                                          
                                                                                                                     
Plan hash value: 2966233522                                                                                          
                                                                                                                     
---------------------------------------------------------------------------                                          
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                          
---------------------------------------------------------------------------                                          
|   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |                                          
|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |                                          
|*  2 |   TABLE ACCESS FULL| T    |   900 |  7200 |     3   (0)| 00:00:01 |                                          
---------------------------------------------------------------------------                                          
                                                                                                                     
Predicate Information (identified by operation id):                                                                  
---------------------------------------------------                                                                  
                                                                                                                     
   2 - filter("ID"<:V_ID)    -->谓词信息表明此时使用了绑定变量                                                       
                                                                                                                     
SQL> exec :v_id:=10;         -->对绑定变量重新赋值                                                                   
                                                                                                                     
PL/SQL procedure successfully completed.                                                                             
                                                                                                                     
SQL> select sum(object_id) from t where id<:v_id;   -->再次执行SQL语句                                               
                                                                                                                     
SUM(OBJECT_ID)                                                                                                       
--------------                                                                                                       
           254                                                                                                       
                                                                                                                     
SQL> select * from table(dbms_xplan.display_cursor());  -->此时执行计划中依然选择的是全表扫描                        
                                                        -->其SQL_ID同上一次执行的SQL语句相同,即实现了完全共享       
PLAN_TABLE_OUTPUT                                       -->对于未使用绑定变量时id<10的情形则为走索引范围扫描         
-----------------------------------------------         -->由此可知,并非最佳的执行计划被执行                        
SQL_ID  7qcp6urqh7d2j, child number 0                                                                                
-------------------------------------                                                                                
select sum(object_id) from t where id<:v_id                                                                          
                                                                                                                     
Plan hash value: 2966233522                                                                                          
                                                                                                                     
---------------------------------------------------------------------------                                          
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                          
---------------------------------------------------------------------------                                          
|   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |                                          
|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |                                          
|*  2 |   TABLE ACCESS FULL| T    |   900 |  7200 |     3   (0)| 00:00:01 |                                          
---------------------------------------------------------------------------                                          
                                                                                                                     
Predicate Information (identified by operation id):                                                                  
---------------------------------------------------                                                                  
                                                                                                                     
   2 - filter("ID"<:V_ID)                                                                                            
                                                                                                                     
SQL> alter system flush shared_pool;    -->清空共享池,此时共享的父游标与子游标全部释放                              
		                                                                                                                 
SQL> print v_id;                                                                                                     
                                                                                                                     
      V_ID                                                                                                           
----------                                                                                                           
        10                                                                                                           
                                                                                                                     
SQL> select round(avg(object_id)) from t where id<:v_id;   -->使用id<10来执行SQL语句                                 
                                                                                                                     
ROUND(AVG(OBJECT_ID))                                                                                                
---------------------                                                                                                
                   28                                                                                                
                                                                                                                     
SQL> select * from table(dbms_xplan.display_cursor());    -->此时该SQL语句使用了最佳的执行计划,即走索引范围扫描     
                                                                                                                     
PLAN_TABLE_OUTPUT                                                                                                    
---------------------------------------------------------------------------------------                              
SQL_ID  0bx53mgt4qqnt, child number 0                                                                                
-------------------------------------                                                                                
select round(avg(object_id)) from t where id<:v_id                                                                   
                                                                                                                     
Plan hash value: 4270555908                                                                                          
                                                                                                                     
-------------------------------------------------------------------------------------                                
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                
-------------------------------------------------------------------------------------                                
|   0 | SELECT STATEMENT             |      |       |       |     3 (100)|          |                                
|   1 |  SORT AGGREGATE              |      |     1 |     8 |            |          |                                
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |     9 |    72 |     3   (0)| 00:00:01 |                                
|*  3 |    INDEX RANGE SCAN          | T_PK |     9 |       |     2   (0)| 00:00:01 |                                
-------------------------------------------------------------------------------------                                
                                                                                                                     
Predicate Information (identified by operation id):                                                                  
---------------------------------------------------                                                                  
                                                                                                                     
   3 - access("ID"<:V_ID)                                                                                            
                                                                                                                     
SQL> exec :v_id:=900;                   -->为变量赋新值                                                              
                                                                                                                     
PL/SQL procedure successfully completed.                                                                             
                                                                                                                     
SQL> select round(avg(object_id)) from t where id<:v_id;                                                             
                                                                                                                     
ROUND(AVG(OBJECT_ID))                                                                                                
---------------------                                                                                                
                  497                                                                                                
                                                                                                                     
SQL> select * from table(dbms_xplan.display_cursor()); -->此次运行的SQL语句本该使用全表扫描,而此时选择了索引范围扫描
                                                                                                                     
PLAN_TABLE_OUTPUT                                                                                                    
--------------------------------------------------------------------------------------                               
SQL_ID  0bx53mgt4qqnt, child number 0                                                                                
-------------------------------------                                                                                
select round(avg(object_id)) from t where id<:v_id                                                                   
                                                                                                                     
Plan hash value: 4270555908                                                                                          
                                                                                                                     
-------------------------------------------------------------------------------------                                
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                
-------------------------------------------------------------------------------------                                
|   0 | SELECT STATEMENT             |      |       |       |     3 (100)|          |                                
|   1 |  SORT AGGREGATE              |      |     1 |     8 |            |          |                                
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |     9 |    72 |     3   (0)| 00:00:01 |                                
|*  3 |    INDEX RANGE SCAN          | T_PK |     9 |       |     2   (0)| 00:00:01 |                                
-------------------------------------------------------------------------------------                                
                                                                                                                     
Predicate Information (identified by operation id):                                                                  
---------------------------------------------------                                                                  
                                                                                                                     
   3 - access("ID"<:V_ID)                                                                                            
                                                                                                                     
SQL> drop table t;                                                                                                   

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

四、延伸参考
    Oracle自适应共享游标
    绑定变量及其优缺点 

    父游标、子游标及共享游标 
    dbms_xplan之display_cursor函数的使用 
    dbms_xplan之display函数的使用 
    执行计划中各字段各模块描述


   

<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'; ``` --- 通过以上方法,可以定位是绑定变量窥探、统计信息缺失还是执行计划固化导致的问题,并针对性优化。
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

清风智语

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值