这个问题一般比较多的出在月底月初,只要产生了硬解析就容易出问题。自从加了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的自己判断来选择,从而避免错误使用。不过我觉得遇到类似问题,往往都和索引设计比较混乱有关,优化索引设计可以从更上游去解决此类问题。