为什么SQL Profile不起作用了？

lsekfe

有个客户前阵子一条SQL因为统计信息问题走错执行计划，导致CPU资源耗尽，系统出现严重故障，必须下线部分功能才临时解决了问题，后来在开发商的尝试下通过SQL PROFILE解决了错误执行计划的问题，恢复了系统。事后远程健康服务中心、Oracle原厂都参与了故障总结，都认为是因为统计信息不准导致了执行计划错误。当时我也提出了一个更为彻底的解决方案，就是合并USERID和日期的两个索引为复合索引，不过因为该表太大，开发商不太愿意重建索引，所以就没有执行。
　　这个问题一般比较多的出在月底月初，只要产生了硬解析就容易出问题。自从加了SQL PROFILE也消停了一阵子。不过昨天又出问题了。

早上突然CPU飙升到100%，因为出过类似问题，所以很快就怀疑到了这条SQL上了。做个AWRSQRPT发现确实存在两个执行计划，又有SQL用错索引了，似乎SQL PROFILE没起作用了。
　　故障报到远程健康服务中心的时候，我们的支撑人员建议他们用SQL PLAN BASELINE固化执行计划，很快就恢复了系统。虽然问题解决的很快，不过用户还是有些疑问，为什么上回出问题时候，研发部门采取的通过SQL PROFILE优化执行计划的策略失效了。
　　实际上用户是把SQL PROFILE当成绑定执行计划了，其实从原理上讲，SQL PROFILE并不是强行绑定执行计划，而是通过SPM分析发现统计信息与实际运行情况不符，因此通过SQL PROFILE设置了一些TABLE_STATS hint，从而让优化器可以使用更为精准的生成执行计划。下面这张图来自于Oracle的官方文档，可以很好的解释SQL PROFILE发挥作用的机理。

在SQL PROFILE提供的HINT中，并没有指定执行计划的内容，而只是设定了一些统计信息的纠正提示。因此设置了SQL PROFILE的SQL语句，SQL解析的时候，会使用PROFILE中的对象的统计信息来纠正执行计划。这样做的好处是灵活，比如某张表上的索引修改了。这条SQL解析的时候会考虑这些因素，选择较好的执行计划。不过也有不好的地方，那就是某些时候，执行计划还是会错误。
　　SQL PROFILE是Oracle 10g引入的新功能，从11g开始，Oracle也看到了SQL PROFILE存在的不足，因此引入了一个新的功，SQL PLAN BASELINE。SQL PLAN BASE LINE的作用与SQL PROFILE类似，不过采取的方法完全不同。按照ORACLE官方文档上的说法，SQL PLAN BASELINE是用于避免存在问题的执行计划的。SQL PLAN BASELINE采取的是强行绑定执行计划的方式。


上面这张图也来自于Oracle的官方文档，这张图十分清晰，从上面我们可以看出，SQL PROFILE是用于纠正过去错误的执行计划的，但是并不限定今后不会再次使用这个错误的执行计划。而SQL PLAN BASELINE是用于确保以后不会使用错误的执行计划的。
　　SQL PLAN BASELINE是一组可接受的计划。每个计划都使用一组Outline hint来实现，这些hint指定了特定的计划。而与之不同的是，SQL PROFILE也使用hint实现，但这些hint没有指定任何特定的计划，仅仅纠正了优化器估算成本时产生的错误统计信息。
　　因为SQL PROFILE不会将优化器约束到任何一个计划，所以SQL PROFILE比SQL PLAN BASELINE更灵活。初始化参数和优化器统计信息的更改使优化器能够选择更好的计划。而SQL PLAN BASELINE一旦设定，那么今后这条SQL就只能使用一个固定的执行计划了。当某条SQL根据绑定变量的不同会有多个不同的最优执行计划的时候，SQL PROFILE可以充分发挥其灵活性。但是SQL PROFILE会有一定的出错的可能性。
　　SQL PLAN BASELINE就简单粗暴的多了，它是强制指定执行计划。这对于某条SQL只有一个唯一的最优执行计划的时候是最为有效的。不过它的缺陷是缺乏灵活性。
　　对于SQL PROFILE和SQL PLAN BASELINE，如果选择错误，就很容易引发不可预知的隐患，因此需要十分谨慎的选择。Oracle建议通过SPM的建议来选择，而不要依靠DBA的自己判断来选择，从而避免错误使用。不过我觉得遇到类似问题，往往都和索引设计比较混乱有关，优化索引设计可以从更上游去解决此类问题。