106-跟专家学习SQL优化-2

这是墨天轮上看到的一篇优化文章. 这篇文章以图为主,没有任何原理上的解释,大家看看你们能不能根据这些截图, 得出优化建议.

待优化SQL:

SQL执行计划:

(图1)

SQL历史执行情况:

(图2)

作者将SQL的select 部分拿出来测试执行,执行时间0.55秒:

(图3)

根据上面信息, 专家给出了优化方法 :

(此处留做读者思考时间)

1-创建驱动表E表上的一个覆盖索引;

2-给not exists 子查询内加个/*+ Hash_AJ */的hint:

优化后的执行计划:

(图4)

"优化后，执行时间从原来的65.98秒(s)变为0.02秒(s)，逻辑读从原来的4177变为57，执行时间上性能提高大概3200多倍，逻辑读减少大概73倍。"

上面基本上是原文全文.

老虎刘点评:

既然逻辑读是用测试执行的4177来比较, 那么执行时间也应该用测试执行的0.55秒跟0.02秒比较才对.

为什么生产系统平均执行时间60多秒, 测试执行只有0.55秒, 这个作者没有给出解释.

如果根据图1的执行计划来进行优化, 那么我会给出下面的优化建议:

1-创建一个函数索引(因为没有具体的数据分布信息,暂用这种极限优化思维),对应的where条件的写法也要配合索引进行改写;

2-再把not exists里面的E.ID_ 加上to_char函数(更正:下面应为to_char):

对应的执行计划是(全表扫描和index full scan都解决了):

优化依据:

根据图1显示的执行计划,第3步的全表扫描估值为1, 说明这一步E表的谓词条件过滤性很好,需要一个索引. 同时cost对应的891告诉我们, 这个E表不算大,最多也就几十万记录,几十M大小, 同时B表也不大,应该只有几万条记录.

执行计划第4步的index full scan明显不正常(正常的Nested Loops应该使用index unique scan), 再结合图3图4执行计划下面显示的 filter("E"."ID_"= SYS_OP_C2C("B"."EID"), 可以确定是发生了隐式类型转换, 由此可以推测出E表的ID_ 字段类型应该是varchar2类型(或char), 而B表的EID字段类型应该是varchar2(或char). 通过对驱动表E的ID_加to_char函数做主动类型转换, 避免在被驱动表B的EID上做隐式类型转换.

这些对得出优化结论非常重要信息原文没有提及.

我的这个优化方法,如果真如图1执行计划显示的那样, 预期优化后的执行时间也就十几毫秒. 但是再仔细想一想,事实应该并非如此.

根据SQL实际执行时间60多秒这个事实, 图1执行计划中,驱动表E过滤后得到的真实结果集应该远大于估算的结果集1, 只有这样,才能对得上平均每次buffer gets 2605万(图2红框)这个数字. 即被驱动表B的index full scan, 可能要被驱动执行几十万次, 才会有那么大的buffer gets量.

表的统计信息一般是在凌晨收集, 在那个时间段, 业务数据没有代表性,生成的执行计划也是不可信的. 所以这个SQL就不能按照图1执行计划显示的数据去优化.

驱动表E返回的结果集大, 虽然我上面的优化方法在驱动表几十万记录的情况下也远比优化前效率高很多, 但是相对来说不如hash join更适合这个SQL,而且用了hash join, 隐式类型转换的问题也就无关紧要了.

分析到了这里,原来专家给的优化建议才是更符合实际生产数据分布的.专家用的两个优化手段, 最起作用的是那个/*+ hash_aj */的hint,就是让优化器在驱动表估值为1的情况下,仍然使用hash join的执行计划. 另外创建的覆盖索引属于锦上添花, 对性能的提升影响不大,生产上对这种索引是不建议创建的.

总结:

原文作者通篇没有提到为什么要使用hash join的执行计划(跟图1所示执行计划的优化思路是不符的,相反的).这种估值明显不准的执行计划, 一般在调试时会生成带A-rows的执行计划. 如果能够采集到60多秒执行情况下的sql monitor信息,就能够更清晰的看到执行计划中真实的结果集大小, 这种简单SQL的优化其实在1~2分钟内就能得出结论. 在这种素材信息给的不是很全的案例上做二次分析, 消耗的时间远比直接分析要多得多.

(完)