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

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

图：处理前的cpu使用

图：处理前物理IO的图像

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

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

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

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

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

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

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

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

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博客

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

最新文章