ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle 执行计划变更

oracle 执行计划变更

原创 Linux操作系统 作者:babyyellow 时间:2011-09-13 11:00:16 0 删除 编辑
oracle 的执行计划经常会遭遇绑定变量 偷窥问题,导致执行计划变更,带来性能问题

同事发在内网的一个帖子转这里了。

感谢分享。

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

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

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

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

DBA_HIST_SQL_PLAN    DBA_HIST_SQLSTAT    DBA_HIST_SNAPSHOT

我在生产库sunha5上做个测试,查询it商城的sql:

1.查询当前在活动的会话获得SQL_ID值
1SYS AS SYSDBA at v880 >select USERNAME,SQL_ID from v$session where status='ACTIVE' AND SCHEMA#>0;
2 
3USERNAME                       SQL_ID
4------------------------------ -------------
5CYP_NW_APP                     dxx8pvcttf5qv
6PRODUCT_PUB                    5c53uzwqswhtb
我们可以获得一个sql_id='dxx8pvcttf5qv'

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

3.查询此sql_id历史执行信息
01select a.INSTANCE_NUMBER,
02      a.snap_id,
03      a.sql_id,
04      a.plan_hash_value,
05      b.begin_interval_time
06from dba_hist_sqlstat a, dba_hist_snapshot b
07where sql_id = 'dxx8pvcttf5qv'
08  and a.snap_id = b.snap_id
09order by instance_number, snap_id;
10 
11INSTANCE_NUMBER    SNAP_ID SQL_ID        PLAN_HASH_VALUE BEGIN_INTERVAL_TIME
12--------------- ---------- ------------- --------------- ---------------------------------------------------------------------------
13              1      17370 dxx8pvcttf5qv      2125777269 24-6月 -10 11.00.44.900 上午
14              1      17371 dxx8pvcttf5qv      2125777269 24-6月 -10 12.00.46.879 下午
15              1      17372 dxx8pvcttf5qv      2125777269 24-6月 -10 01.00.48.962 下午
16              1      17373 dxx8pvcttf5qv      1904478120 24-6月 -10 02.00.50.872 下午
17              1      17374 dxx8pvcttf5qv      1904478120 24-6月 -10 03.00.52.840 下午
18              1      17375 dxx8pvcttf5qv      1904478120 24-6月 -10 04.00.54.780 下午
截取了一段查询信息,可以看到sql历史执行信息中,在6月24日时执行计划有变更.

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

4.查询变更前后的执行计划
01select sql_id,
02        plan_hash_value,
03        id,
04        operation,
05        options,
06        object_owner,
07        object_name,
08        depth,
09        cost,
10        timestamp
11   from DBA_HIST_SQL_PLAN
12  where sql_id = 'dxx8pvcttf5qv'
13  and plan_hash_value in (1904478120,2125777269);
14 
15SQL_ID        PLAN_HASH_VALUE  ID OPERATION            OPTIONS    OBJECT_OWN OBJECT_NAME     DEPTH   COST TIMESTAMP
16dxx8pvcttf5qv 1904478120 11 TABLE ACCESS BY INDEX ROWID CYP_NW_APP ENT_PRODUCT 11 14780 2010-06-24 14-14-36
17dxx8pvcttf5qv 1904478120 12 INDEX RANGE SCAN CYP_NW_APP IDX_ENT_PRODUCT_2 12 14767 2010-06-24 14-14-36
18dxx8pvcttf5qv 1904478120 13 TABLE ACCESS BY INDEX ROWID CYP_NW_APP ENT_USER 9 1 2010-06-24 14-14-36
19dxx8pvcttf5qv 1904478120 14 INDEX UNIQUE SCAN CYP_NW_APP SYS_C00124956 10 0 2010-06-24 14-14-36
20 
21SQL_ID        PLAN_HASH_VALUE  ID OPERATION            OPTIONS    OBJECT_OWN OBJECT_NAME     DEPTH   COST TIMESTAMP
22dxx8pvcttf5qv 2125777269 11 TABLE ACCESS FULL CYP_NW_APP ENT_PRODUCT 11 21329 2010-06-17 03-14-15
23dxx8pvcttf5qv 2125777269 12 TABLE ACCESS BY INDEX ROWID CYP_NW_APP ENT_USER 9 1 2010-06-17 03-14-15
24dxx8pvcttf5qv 2125777269 13 INDEX UNIQUE SCAN CYP_NW_APP SYS_C00124956 10 0 2010-06-17 03-14-15
我们从查询结果中可以看到变更前后执行计划除了一个索引不同外,其他都一样
plan_hash_value = 1904478120  ---此执行计划多走一个索引IDX_ENT_PRODUCT_2
plan_hash_value = 2125777269

5.根据执行计划的不同点查找原因
1select * 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的执行计划是否变更.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/133735/viewspace-707229/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 发布防扒提示,
请登录后发表评论 登录
全部评论
oracle MySQL Postgresql 专职数据库dba。 系统架构师。 mysql 官方认知dba 。 15年专职dba 经验。

注册时间:2010-12-02

  • 博文量
    252
  • 访问量
    1490645