绑定变量偷窥

oracle 的执行计划经常会遭遇绑定变量 偷窥问题，导致执行计划变更，带来性能问题


SQL执行计划变更导致数据库负载突升,让我们措手不及,有没有好的处理办法呢?


让我们来查查这个语句的历史执行信息,看看是否发生变化,何时发生了变化.


如果发生了变化,找出以前的执行计划,与当前的执行计划进行对比,有什么不同.


oracle 10G中我们可以通过下面的三个视图查询到语句的历史执行信息.
DBA_HIST_SQL_PLAN    DBA_HIST_SQLSTAT    DBA_HIST_SNAPSHOT


我在生产库sunha5上做个测试,查询it商城的sql:
1.查询当前在活动的会话获得SQL_ID值
SYS AS SYSDBA at v880 >select USERNAME,SQL_ID from v$session where status='ACTIVE'AND SCHEMA#>0;
 
USERNAME                       SQL_ID
------------------------------ -------------
CYP_NW_APP                     dxx8pvcttf5qv
PRODUCT_PUB                    5c53uzwqswhtb
我们可以获得一个sql_id='dxx8pvcttf5qv'


2.获得此sql_id对应的sql语句
select sql_id,sql_fulltext from v$sql where sql_id='dxx8pvcttf5qv';
从查询结果sql_fulltext,我们可以获得sql语句.


3.查询此sql_id历史执行信息
select a.INSTANCE_NUMBER,
     a.snap_id,
     a.sql_id,
     a.plan_hash_value,
     b.begin_interval_time
from dba_hist_sqlstat a, dba_hist_snapshot b
where sql_id ='dxx8pvcttf5qv'
 and a.snap_id = b.snap_id
order by instance_number, snap_id;
 
11 INSTANCE_NUMBER    SNAP_ID SQL_ID        PLAN_HASH_VALUE BEGIN_INTERVAL_TIME
12 --------------- ---------- ------------- --------------- ---------------------------------------------------------------------------
13              1     17370dxx8pvcttf5qv     212577726924-6月 -1011.00.44.900上午
14              1     17371dxx8pvcttf5qv     212577726924-6月 -1012.00.46.879下午
15              1     17372dxx8pvcttf5qv     212577726924-6月 -1001.00.48.962下午
16              1     17373dxx8pvcttf5qv     190447812024-6月 -1002.00.50.872下午
17              1     17374dxx8pvcttf5qv     190447812024-6月 -1003.00.52.840下午
18              1     17375dxx8pvcttf5qv     190447812024-6月 -1004.00.54.780下午
截取了一段查询信息,可以看到sql历史执行信息中,在6月24日时执行计划有变更.


我们具体查查看变更前后的执行计划有什么区别.


4.查询变更前后的执行计划
select sql_id,
       plan_hash_value,
       id,
       operation,
       options,
       object_owner,
       object_name,
       depth,
       cost,
       timestamp
  from DBA_HIST_SQL_PLAN
 where sql_id ='dxx8pvcttf5qv'
 and plan_hash_value in (1904478120,2125777269);
 
15 SQL_ID        PLAN_HASH_VALUE  ID OPERATION            OPTIONS    OBJECT_OWN OBJECT_NAME     DEPTH   COST TIMESTAMP
16 dxx8pvcttf5qv190447812011TABLE ACCESS BY INDEX ROWID CYP_NW_APP ENT_PRODUCT11147802010-06-2414-14-36
17 dxx8pvcttf5qv190447812012INDEX RANGE SCAN CYP_NW_APP IDX_ENT_PRODUCT_212147672010-06-2414-14-36
18 dxx8pvcttf5qv190447812013TABLE ACCESS BY INDEX ROWID CYP_NW_APP ENT_USER912010-06-2414-14-36
19 dxx8pvcttf5qv190447812014INDEX UNIQUE SCAN CYP_NW_APP SYS_C001249561002010-06-2414-14-36
20  
21 SQL_ID        PLAN_HASH_VALUE  ID OPERATION            OPTIONS    OBJECT_OWN OBJECT_NAME     DEPTH   COST TIMESTAMP
22 dxx8pvcttf5qv212577726911TABLE ACCESS FULL CYP_NW_APP ENT_PRODUCT11213292010-06-1703-14-15
23 dxx8pvcttf5qv212577726912TABLE ACCESS BY INDEX ROWID CYP_NW_APP ENT_USER912010-06-1703-14-15
24 dxx8pvcttf5qv212577726913INDEX UNIQUE SCAN CYP_NW_APP SYS_C001249561002010-06-1703-14-15
我们从查询结果中可以看到变更前后执行计划除了一个索引不同外,其他都一样
plan_hash_value = 1904478120  ---此执行计划多走一个索引IDX_ENT_PRODUCT_2
plan_hash_value = 2125777269


5.根据执行计划的不同点查找原因
select * from dba_objects where object_name='IDX_ENT_PRODUCT_2';
从索引'IDX_ENT_PRODUCT_2'的信息中看到, last_ddl_time='2010-06-24 14-01-56' ,应该是这个原因导致执行计划的改变.


我上面是举个例子,当数据库突然有异常sql时,排除程序更新的原因,我们可以按照这个思路去查询异常sql的执行计划是否变更.