在Oracle中时长会出现一直运行正常的SQL突然运行很慢的问题。检查发现执行计划发生了改变，这时候就需要绑定执行计划。在Oracle 10g以后的版本中，可以使用SQL Profile或SPM（11g以后）来绑定执行计划。

1、SQL Profile

Oracle 10g中的SQL Profile是Oracle 9i中的stored outline的进化升级版，相比stored outline有以下优点：

• 更容易生成、更改和控制。
• 对SQL语句的支持做的更高，适用范围广。

SQL Profile有两种类型：

①Automatic类型

②Manual类型。

1.Automatic类型

Automatic类型的SQL Profile起始就是针对目标SQL的一些额外的调整信息，这些信息存储在数据字典中，有了Automatic类型的SQL Profile之后Oracle就会根据它目标SQL所涉及的统计信息等内容做相应的调整。

从某种意义上说Automatic类型的SQL Profile并不能完全起到稳定目标SQL执行计划的作用，但是的确可以用来调整执行计划。

缺点：只能用于单条语句固定执行计划，并不能广泛应用于生产中。

2.Manual类型

Manual类型的SQL Profile本质上就是一堆hint的组合，它可以在不更改SQL文本的前提下启到很大的稳定目标SQL执行计划的作用。

步骤：

①找出问题SQL并记录SQL_ID和PLAN_HASH_VALUE，使用hint优化该SQL，记录优化好的SQL_ID和PLAN_HASH_VALUE。

②运行coe_xfr_sql_profile.sql，将原SQL生成的文本记录文本A，优化后SQL生成的文本记录文本B，将文本B中的hint部分“偷梁换柱”到文本A中，并将文本A中force_match参数改为TRUE。

③运行修改后的文本A，再次执行原SQL确认执行计划。做到了不修改SQL文本固定执行计划。

• 承接上述实验，删除上述automatic类型的Profile，SYS_SQLPROF_016a75433f370000。

2、SPM（for Oracle 11g and later）

实际上SQL Profile是一种亡羊补牢的手段，相对被动。而SPM（SQL plan management）是一种主动的稳定执行计划手段，只有被验证过的执行计划才会被启用，彻底解决执行计划稳定性的问题。它既能主动的稳定执行计划，又保留了继续使用新的执行效率可能更高的执行计划的机会。当启用SPM之后每一个SQL都会存在对应的SQL plan baseline，这个SQL plan baseline是存储在执行计划中的，可以从DBA_SQL_PLAN_BASELINES中查看目标SQL的所有SQL plan baseline。只有DBA_SQL_PLAN_BASELINES中列ENABLED和ACCEPTED两个列都是“YES”，SQL plan baseline对应的执行计划才会被Oracle启用。如果一个SQL有超过1个以上ENABLED和2个ACCEPTED列都是“YES”，则Oracle会从中选择成本最低的执行计划作为该SQL的执行计划。

实际上在Oracle升级或者正常运行中都需要使用SPM来稳定执行计划，主要有两种方法：

①自动捕获。

②手工生成/批量导入（Oracle大版本升级中适用）。

参数OPTIMIZER_CAPTURE_SQL_PALN_BASELINES用于控制是否启用自动捕获SQL plan baseline，默认值是FALSE，不开启自动捕获SQL plan baseline。该参数设置为TRUE后，Oracle会对该参数影响范围的所有重复执行的SQL自动捕获其SQL plan baseline，并且针对SQL第一次捕获的SQL plan baseline的ENABLED和ACCEPTED两个列都是“YES”。

当该SQL执行计划发生改变再次捕获到SQL plan baseline的ENABLED依然是“YES”，但是ACCEPTED列为“NO”，表示后续变更执行计划虽然被捕获了，但是仍然使用第一次捕获的SQL plan baseline对应的执行计划。

参数OPTIMIZER_USE_SQL_PALN_BASELINES用于控制是否使用SQL plan baseline，默认为TRUE，默认Oracle在生成执行计划就会生成SPM，使用以后的SQL plan baseline。

1.自动捕获

• 查看当前参数，修改为会话级别的自动捕获，不开启SQL plan baseline。

2.手工生成SQL Plan Baseline

其本质是使用dbms_spm.load_plans_from_cursor_cache通过同SQL Profile一样“偷梁换柱”的方式修改目标SQL的执行计划。

• 继续使用上述实验语句，加入hint使其走Full Table Scan。

墨天轮原文链接：https://www.modb.pro/db/88224（复制链接至浏览器或点击oracle执行计划绑定 - 墨天轮查看)

关于作者

任艳杰，云和恩墨Oracle技术顾问，长期致力于大型Oracle数据库维护工作，具备扎实的理论基础和丰富的实践经验，擅长数据恢复，性能优化，数据迁移等。