清除shared pool中某条sql的执行计划

       今天下午突然出现了大量的物理IO，通过生成ash报告查询出对应的sql_id以及发现这条sql走的是全表扫。因为使用了绑定变量，绑定变量导致之后的语句不作重新解析,重用了最差的执行计 划，而且还是好多个应用都在执行这条sql。对于一个一亿多条数据的表进行全表扫无异于是灾难性的，但是为什么会突然间出现错误的执行计划呢。首先先将问题进行处理，使其走正确的执行计划。

处理思路：
收集表的统计信息，清除library cache中现有的执行计划，从新执行该语句，使其从新硬解析生成新的执行计划。

                                               图：处理前的cpu使用

                                               图： 处理前物理IO的图像

                                              图：处理前的执行计划（全表扫）

1、收集表的统计信息
收集表的统计信息的同时收集该表索引的统计信息

1. exec dbms_stats.gather_table_stats('FHL_DCS','OPERATELOG',cascade=>true);

2、清除library cache内现有的执行计划。
        1）根据sql_id取到改sql的address值以及哈希值

1. select s.SQL_TEXT, s.ADDRESS || ',' || s.HASH_VALUE
   
2. from v$sqlarea s
   
3. where sql_id = '10kq6nc2rfrf0'；

        2）清理该sql在library cache中的执行计划

1. exec sys.dbms_shared_pool.purge('0000000141AE1310,91708864','c');

注：其中0000000141AE1310为该sql的address值，91708864为sql的哈希值。
        3）查看执行计划是否已清除

1. select * from v$sql_plan where sql_id = '10kq6nc2rfrf0'；

生产中我们常使用绑定变量降低硬解析的发生，但是对于同一条sql来说，当library cache存在这条sql的执行计划时，oracle不会再为它生成一个新的执行计划，即便这个执行计划是最差的。

                                           图：处理后的cpu使用情况

                                            图：处理后的物理IO变化
变化还是很明显的，在开始的时候物理IO都会达到120~150KIO/sec，处理后就将至4~10KIO/sec


                                             图：处理后的执行计划
可以看出来这时候，走的是相对正确的执行计划


关于10g使用dbms_shared_pool.purge需要执行alter session set events '5614566 trace name context forever';来激活
参考博客：http://blog.itpub.net/8984272/viewspace-619964/
参考 metalink Doc ID:  751876.1  
10.2.0.4默认不开启,要靠event 5614566或者补丁5614566来激活

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

转载于:http://blog.itpub.net/30820196/viewspace-2132266/