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

数据库 Oracle
对于SQL PROFILE和SQL PLAN BASELINE,如果选择错误,就很容易引发不可预知的隐患,因此需要十分谨慎的选择。Oracle建议通过SPM的建议来选择,而不要依靠DBA的自己判断来选择,从而避免错误使用。不过我觉得遇到类似问题,往往都和索引设计比较混乱有关,优化索引设计可以从更上游去解决此类问题。​

有个客户前阵子一条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的自己判断来选择,从而避免错误使用。不过我觉得遇到类似问题,往往都和索引设计比较混乱有关,优化索引设计可以从更上游去解决此类问题。

责任编辑:武晓燕 来源: 白鳝的洞穴
相关推荐

2023-09-27 08:33:16

作用域CSS

2021-04-20 23:16:06

SparkSQL语法

2023-12-07 07:08:09

Angular函数

2023-02-02 10:19:05

Wi-Fi无线网络

2023-03-06 16:38:30

SQL数据库

2023-12-20 08:23:53

NIO组件非阻塞

2024-04-30 09:02:48

2023-12-12 08:41:01

2023-04-26 10:21:04

2024-05-28 09:12:10

2024-04-07 00:00:00

ESlint命令变量

2017-09-26 11:06:15

数据库索引查询

2024-08-08 09:15:08

SQL代码复制表

2019-06-14 15:36:13

Windows 10安全PC

2024-01-09 07:29:05

Argo代码库应用程序

2021-02-02 08:21:28

网络面试通信

2022-05-27 08:55:15

工具自动化软件

2020-10-28 11:20:55

vue项目技

2017-10-16 13:45:04

2024-07-30 08:22:47

API前端网关
点赞
收藏

51CTO技术栈公众号