编辑手记: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 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) */ 仍然不起作用。
改写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
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来实现优化的目的。