极致之处,精彩无限 - 优化了一半的SQL

编辑手记:RWP(Real World Performance)团队是全球最优秀的性能优化团队,他们的目标在于系统性能千倍的提升。感谢刘永甫专家的授权,他从RWP团队转入售后,多年专注于性能优化。我们将会拣选他在职业生涯中一些经典的优化案例跟大家分享。

某次在给某知名通讯设备供应商做性能优化,快接近尾声的时候,偶然发现一个不是很TOP的TOP sql(一般刘老师会收集AWR 的TOP 50 sql,默认只有大概20个)使用了Hint,而其他SQL基本上都没有使用hint,其中必有隐情。顺手分析一下 :

虽然SQL平均执行时间0.25秒,但是执行次数多,因此也在TOP50之列。

SQL语句

SELECT /*+PUSH_PRED(HS)*/* FROM DMD_BOQ_PLAN_HEADER_T DBPH, DMD_PAYMENT_UNIT_V HS, DMD_PAYMENT_UNIT_CONTROL_T PUC WHERE DBPH.PAYMENT_UNIT_ID = HS.PAYMENT_UNIT_ID AND HS.PAYMENT_UNIT_ID = PUC.PAYMENT_UNIT_ID AND DBPH.BOQ_PLAN_HEADER_ID = :B1;

说明:其中 DMD_PAYMENT_UNIT_V是一个view。如下:

SELECT STAGE_ID  AS PAYMENT_UNIT_ID,......
 FROM HT_STAGES 
UNION ALL
SELECT DBT.CCM_BOQ_ID   AS PAYMENT_UNIT_ID,......
 FROM DMD_BOQ_T    DBT
 WHERE DBT.REGISTER_FLAG ='N';

VIEW使用的两个表转换成的PAYMENT_UNIT_ID字段的对应列(HT_STAGES.STAGE_ID和DMD_BOQ_T.CCM_BOQ_ID),都是选择性很好的列;SQL谓词条件使用的几个字段选择性也都非常好,字段上都有索引。

根据以上信息,这个SQL的执行时间,正常应该在1毫秒左右,而不应该是AWR报告中显示的250毫秒。

先来看执行计划:

时间主要消耗在ID=5的全表扫描上,按照正常的情况,这一步应该是最后完成,而且是应该使用DMD_PAYMENT_UNIT_CONTROL_T表PAYMENT_UNIT_ID字段上的索引。当前因为这两个表之间没有直接关联关系,这一步的操作相当于做了笛卡尔积,这不科学。ID=7的步骤是正确的。

我们再来看看没有使用hint的SQL执行计划:

这个执行计划问题更严重,因为没有做谓词推进(push_pred),view使用的两个表做了全表扫描,原来SQL使用push_pred的hint还是起到了重要的优化效果。只是仍没有解决DMD_PAYMENT_UNIT_CONTROL_T表的全表扫描问题,应该算是一个优化了一半的SQL。

尝试使用更多的hint来调整执行计划:

/*+PUSH_PRED(HS) leading(dbph hs puc) use_nl(hs) use_nl(puc) */ 仍然不起作用。 优化尝试1 改写SQL,强制将DBPH和HS放在一个内联视图里先做join(no_merge不能少),然后再与PUC做join,这个是完全等价的SQL: select * from (SELECT /*+ PUSH_PRED(HS) no_merge*/ hs.PAYMENT_UNIT_ID FROM DMD_BOQ_PLAN_HEADER_T DBPH, DMD_PAYMENT_UNIT_V HS WHERE DBPH.PAYMENT_UNIT_ID = HS.PAYMENT_UNIT_ID AND DBPH.BOQ_PLAN_HEADER_ID = :B1 ) hs1, DMD_PAYMENT_UNIT_CONTROL_T PUC where HS1.PAYMENT_UNIT_ID = PUC.PAYMENT_UNIT_ID;

这样改动后,执行计划就完美了:

这个SQL的执行时间大概就是1ms。

有没有更好的优化方法?经过测试,答案是有的:

优化尝试2

根据等值传递原理 a.id=b.id and b.id=c.id 等价于 a.id=b.id and a.id=c.id

将 HS.PAYMENT_UNIT_ID = PUC.PAYMENT_UNIT_ID

改成 DBPH.PAYMENT_UNIT_ID = PUC.PAYMENT_UNIT_ID

即:

SELECT *

FROM DMD_BOQ_PLAN_HEADER_T DBPH,

DMD_PAYMENT_UNIT_V HS,

DMD_PAYMENT_UNIT_CONTROL_T PUC

WHERE DBPH.PAYMENT_UNIT_ID = HS.PAYMENT_UNIT_ID

AND DBPH.PAYMENT_UNIT_ID = PUC.PAYMENT_UNIT_ID

--AND HS.PAYMENT_UNIT_ID = PUC.PAYMENT_UNIT_ID

AND DBPH.BOQ_PLAN_HEADER_ID = :B1;

经过这样的修改后,不用任何的hint,执行计划都是完美的。

这个案例应该是优化器的考虑不周所致,遇到这种情况,我们就需要考虑通过改写SQL来实现优化的目的。

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2017-05-08

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

一次数据库响应慢的问题诊断(r6笔记第39天)

今天接到开发一个同事的电话,说前端系统那边反馈有一个查询很慢,初步怀疑是有一些并发或者锁之类的问题导致的。 接到问题之后,自己还是带着一些的紧迫感来处理的。 首...

2495
来自专栏编程

计算机专业基础书籍推荐

编辑:小虾米 夏沫 本文根据网上的参考资料以及自己的学习经验总结整理而成,旨在给大家做一个参考,希望大家在工作、学习中遇到相关的问题之后能从这些参考书中获得帮...

19110
来自专栏杨建荣的学习笔记

记一次数据同步需求的改进(二) (r7笔记第5天)

在之前写过记一次数据同步需求的改进(一) (r7笔记第2天)之后,就开始着手对这个需求进行实践。 所谓实践出真知,在实际做的时候才发现可能计划的再好,做的时候还...

3258
来自专栏数据和云

DBA入门之路:察微知渐细致入微

在DBA的职业生涯中,要面临无数的艰难险阻、排忧解难,所以细致入微,严谨认真的风格必不可少。养成了察微知渐的习惯,才能在分析诊断故障时层剖缕析,直指核心;而我也...

2013
来自专栏小怪聊职场

MySQL(八)|MySQL中In与Exists的区别(2)

1203
来自专栏杨建荣的学习笔记

一条看似平常的报警邮件所做的分析(r8笔记第9天)

今天留意到一封报警邮件。内容如下: ZABBIX-监控系统: ------------------------------------ 报警内容: CPU u...

2334
来自专栏杨建荣的学习笔记

百倍性能的PL/SQL优化案例(r11笔记第13天)

我相信你是被百倍性能的字样吸引了,不过我所想侧重的是优化的思路,这个比优化技巧更重要,而结果嘛,其实我不希望说成是百倍提升,“”自黑“”一下。 有一个真...

3399
来自专栏杨建荣的学习笔记

Oracle中的段(r10笔记第81天)

Oracle的体系结构中,关于存储结构大家应该都很熟悉了。 估计下面这张图大家都看得熟悉的不能再熟悉了。 ? 简单来说,里面的一个重要概念就是段,如果是开发...

3388
来自专栏沃趣科技

ASM 翻译系列第三十三弹:REQUIRED_MIRROR_FREE_MB的含义

原作者:Bane Radulovic 译者: 陈亚军 审核: 魏兴华 DBGeeK社区联合出品 原文链接:http://asmsupportguy....

34510
来自专栏杨建荣的学习笔记

解析实时的DB time过程分析(r6笔记第35天)

在我们查看awr报告的时候总是会有一个关键指标需要注意,那就是DB time,这个指标一般都是通过awr报告来看到的。 比如我们得到的awr报告头部显示的下面的...

3257

扫描关注云+社区