oracle手工让执行计划失效,生产系统SQL执行计划突然变差怎么办？

由于各种各样的原因，DBA有时会遇到SQL执行计划突然变差的情况，导致CPU和IO资源消耗过高，整个系统性能下降。

很多人遇到这种情况的通常做法是，立即收集表的统计信息，让优化器重新对SQL做硬解析，期待能够恢复原来的执行计划。

但是，这样做有一些问题：

1、

如果是大表，收集统计信息的时间会比较长，而且执行计划变差一般伴随着CPU利用率高和IO繁忙，这个时间会更长；

2、

有些DBA在收集统计信息时，没有使用no_invalidate=>false选项，即使收集了统计信息，执行计划却没有立即改变。因为该参数的默认值是AUTO_INVALIDATE，优化器会选择5个小时内的某个时间点来对SQL重新做硬解析。因为不了解这个参数，有人还会在收集完统计信息后flush shared_pool来强制对所有SQL做硬解析。

3、

有些SQL执行计划改变是跟统计信息没有关系的，即使重新收集了统计信息，执行计划还是无法恢复正常。

遇到执行计划突然变差，刘老师的建议是：先用SQL profile(10g及以上版本)固定执行计划为原来正常的执行计划，让业务先恢复正常，再慢慢查找原因。

很多DBA习惯于使用coe_xfr_sql_profile.sql脚本来固定sql 执行计划，但是这个脚本操作起来比较麻烦，而且容易出错。这个脚本的正确用途是用来做不同数据库之间sql执行计划的固定。

最方便的脚本是：coe_load_sql_profile.sql，使用这个脚本，只需要输入几个参数，就能完成快速恢复执行计划的任务。

具体步骤如下：

1、用DBA权限的用户登录sqlplus (不能是sys用户，可以是system用户)

2、执行脚本 SQL>coe_load_sql_profile.sql

3、输入第一个参数：需要恢复执行计划的sql_id

4、输入第二个参数：再输入一次相同的sql_id

5、此时会显示该sql_id对应的几个执行计划的plan_hash_value，第三个参数需要你选择最优执行计划对应的那个plan_hash_value

6、最后一步，输入连接sqlplus用户的密码，导出sql profile信息到一个表。如果不需要导出sql profile信息，最后一步exp操作可以从原脚本中屏蔽(注释掉以HOS exp开头那一行)。

下面是一个具体的实例截图(没有最后做exp导出输入密码的步骤)：

注：

coe_load_sql_profile.sql 脚本可以从MOS网站下载的sqlt工具包里面获取，或者加入刘老师的QQ群16778072 索取