为啥SQL Profile不起作用了,你知道吗?

发表于:2022-12-02 09:56

字体: | 上一篇 | 下一篇 | 我要投稿

 作者:白鳝    来源:白鳝的洞穴

#
SQL
分享:
  有个客户前阵子一条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的自己判断来选择,从而避免错误使用。不过我觉得遇到类似问题,往往都和索引设计比较混乱有关,优化索引设计可以从更上游去解决此类问题。
  本文内容不用于商业目的,如涉及知识产权问题,请权利人联系51Testing小编(021-64471599-8017),我们将立即处理
《2023软件测试行业现状调查报告》独家发布~

关注51Testing

联系我们

快捷面板 站点地图 联系我们 广告服务 关于我们 站长统计 发展历程

法律顾问:上海兰迪律师事务所 项棋律师
版权所有 上海博为峰软件技术股份有限公司 Copyright©51testing.com 2003-2024
投诉及意见反馈:webmaster@51testing.com; 业务联系:service@51testing.com 021-64471599-8017

沪ICP备05003035号

沪公网安备 31010102002173号