编辑手记：RWP（Real World Performance）团队是全球最优秀的性能优化团队，他们的目标在于系统性能千倍的提升。感谢刘永甫专家的授权，他从RWP团队转入售后，多年专注于性能优化。我们将会拣选他在职业生涯中一些经典的优化案例跟大家分享。 某次在给某知名通讯设备供应商做性能优化，快接近尾声的时候，偶然发现一个不是很TOP的TOP sql（一般刘老师会收集AWR 的TOP 50 sql，默认只有大概20个）使用了Hint，而其他SQL基本上都没有使用hint，其中必有隐情。顺手分析一下： 虽然SQL平均执行时间0.25秒，但是执行次数多，因此也在TOP50之列。 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 ASPAYMENT_UNIT_ID,...... FROMHT_STAGES UNION ALL SELECT DBT.CCM_BOQ_ID ASPAYMENT_UNIT_ID,...... FROMDMD_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)*/ 仍然不起作用。 改写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。 有没有更好的优化方法？经过测试，答案是有的： 根据等值传递原理 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 ANDDBPH.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来实现优化的目的。 加入"云和恩墨大讲堂"微信群，参与讨论学习 搜索 盖国强（Eygle）微信号：eyygle，或者扫描下面二维码，备注：云和恩墨大讲堂，即可入群。每周与千人共享免费技术分享，与讲师在线讨论。
|
|
声明：文章版权归原作者所有 部分文章转自互联网 如有侵权请联系
[邮箱地址] 删除
|
注册
登录
