51Testing软件测试论坛

 找回密码
 (注-册)加入51Testing

QQ登录

只需一步,快速开始

微信登录,快人一步

手机号码,快捷登录

查看: 1484|回复: 0

为什么SQL Profile不起作用了?

[复制链接]
  • TA的每日心情
    无聊
    昨天 09:04
  • 签到天数: 1030 天

    连续签到: 1 天

    [LV.10]测试总司令

    发表于 2022-12-2 14:21:26 | 显示全部楼层 |阅读模式
    有个客户前阵子一条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

    x
    回复

    使用道具 举报

    本版积分规则

    关闭

    站长推荐上一条 /2 下一条

    小黑屋|手机版|Archiver|51Testing软件测试网 ( 沪ICP备05003035号 关于我们

    GMT+8, 2024-10-12 02:50 , Processed in 0.085609 second(s), 25 queries .

    Powered by Discuz! X3.2

    © 2001-2024 Comsenz Inc.

    快速回复 返回顶部 返回列表