oracle 执行计划变更-白红宇

oracle 执行计划变更

阅读量：2496 次

发布时间：2019-05-11

本文共 3276 字，大约阅读时间需要 10 分钟。

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

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

感谢分享。

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

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

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

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

DBA_HIST_SQL_PLAN DBA_HIST_SQLSTAT DBA_HIST_SNAPSHOT

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

1.查询当前在活动的会话获得SQL_ID值

                  1          SYS AS SYSDBA at v880 >select USERNAME,SQL_ID from v$session where status='ACTIVE' AND SCHEMA#>0;         
       
                  2                    
       
                  3          USERNAME                       SQL_ID         
       
                  4          ------------------------------ -------------         
       
                  5          CYP_NW_APP                     dxx8pvcttf5qv         
       
                  6          PRODUCT_PUB                    5c53uzwqswhtb

我们可以获得一个sql_id='dxx8pvcttf5qv'

2.获得此sql_id对应的sql语句

1	select sql_id,sql_fulltext from v$sql where sql_id='dxx8pvcttf5qv';

从查询结果sql_fulltext,我们可以获得sql语句.

3.查询此sql_id历史执行信息

                  01          select a.INSTANCE_NUMBER,         
       
                  02                a.snap_id,         
       
                  03                a.sql_id,         
       
                  04                a.plan_hash_value,         
       
                  05                b.begin_interval_time         
       
                  06          from dba_hist_sqlstat a, dba_hist_snapshot b         
       
                  07          where sql_id = 'dxx8pvcttf5qv'         
       
                  08            and a.snap_id = b.snap_id         
       
                  09          order by instance_number, snap_id;         
       
                  10                    
       
                  11          INSTANCE_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.查询变更前后的执行计划

                  01          select 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                    
       
                  15          SQL_ID        PLAN_HASH_VALUE  ID OPERATION            OPTIONS    OBJECT_OWN OBJECT_NAME     DEPTH   COST TIMESTAMP         
       
                  16          dxx8pvcttf5qv 1904478120 11 TABLE ACCESS BY INDEX ROWID CYP_NW_APP ENT_PRODUCT 11 14780 2010-06-24 14-14-36         
       
                  17          dxx8pvcttf5qv 1904478120 12 INDEX RANGE SCAN CYP_NW_APP IDX_ENT_PRODUCT_2 12 14767 2010-06-24 14-14-36         
       
                  18          dxx8pvcttf5qv 1904478120 13 TABLE ACCESS BY INDEX ROWID CYP_NW_APP ENT_USER 9 1 2010-06-24 14-14-36         
       
                  19          dxx8pvcttf5qv 1904478120 14 INDEX UNIQUE SCAN CYP_NW_APP SYS_C00124956 10 0 2010-06-24 14-14-36         
       
                  20                    
       
                  21          SQL_ID        PLAN_HASH_VALUE  ID OPERATION            OPTIONS    OBJECT_OWN OBJECT_NAME     DEPTH   COST TIMESTAMP         
       
                  22          dxx8pvcttf5qv 2125777269 11 TABLE ACCESS FULL CYP_NW_APP ENT_PRODUCT 11 21329 2010-06-17 03-14-15         
       
                  23          dxx8pvcttf5qv 2125777269 12 TABLE ACCESS BY INDEX ROWID CYP_NW_APP ENT_USER 9 1 2010-06-17 03-14-15         
       
                  24          dxx8pvcttf5qv 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.根据执行计划的不同点查找原因

1	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的执行计划是否变更.

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

转载于:http://blog.itpub.net/133735/viewspace-707229/

你可能感兴趣的文章