本次给大家带来的是 ORACLE 巧妙使用谓词推入优化SQL。直入正题:
本周,开发反馈跑批速度太慢,申请优化并提供了相关SQL,该SQL是一条标准的insert* select插入语句,去除无关的部分,SQL如下:
业务需求,5秒以内,查看执行计划:
发现 MCR_BILL_M表与CAP_OVERDUE_HIS表走了谓词推入,CAP_OVERDUE_HIS表与MCR_BILL_D表走了全表扫描。
我们知道,当SQL语句包含不能合并的视图同时视图有谓词过滤也就是where过滤条件的时候,CBO会将谓词过滤条件推入视图,这就是谓词推入。这里MX表就是成功走了连接列谓词推入,但是TER表却失败了。
首先,查看TB表数据量:
仅仅170条,看来MX表谓词推入的效果是成功的(原因最后会解释),再检查TER内部两个表的字段过滤性,发现这两个表结果集分别是1100W,33W,占据源表95%以上数据量,走索引是没戏了,表面看 确实就是最优计划了,有的人看到这里会说:结果集差异那么大,为什么不走NL呢?
是的,一般情况下确实走NL会快很多,但是各位注意到没有:
这个count(*)太致命,无法走NL,CAP_OVERDUE_HIS表还是得走全表扫描,如下图所示:
最终必须要hash做group,那么到这里基本就需要分析SQL的本质了。
既然MX能够谓词推入成功,那么使用 push_pred强行推入谓词到TER表中是否可以呢?
下面让我们来做下实验:
整整32秒才出来结果!再看执行计划:
很明显,谓词虽然推入成功了,但是这个千万级大表CAP_OVERDUE_HIS的全表扫描没有避免,整体效率没有得到提升。无法谓词推入,源表也没有好的过滤方式,到这里,我们就不能纠结TER内部逻辑了,总览整个SQL发现
关键信息:
可知TB表是主表,而且总体只有170条数据,左连接的概念相信各位都知道,既然取的以TB表为准,那么,我们可以强行把TB表写入TER表里面,手动推入如下:
优化成功!秒杀效果!观察执行计划:
果然推入成功!不过各位注意到没有前面MX表的谓词推入标识没了,这里它走了常量谓词推入(观察7的谓词条件可知PR被推入视图)。
这里我们将插入的TB表作为驱动,在不改变整体取数逻辑的情况下强行驱动了TER内部两个表,这才是谓词推入的意义:帮助ORACLE尽可能过滤无用的数据。
实际生产中,经常遇到谓词推入引发的性能问题,有说谓词推入好的也有坏的,众说纷纭,那么,为什么谓词推入是好是坏呢?
我们要明白其本质,下面做个简单说明:
常量谓词推入(非连接列推入)基本对于性能的提升都是有益处的(相信CBO的判断)。
连接列谓词推入经常引发性能问题,因为连接列谓词推入需要传值(传入视图内),有传值操作的表连接只有NL或者FILTER。
FILTER专门针对半连接或者反连接,即where后面包含的子查询语句,
而谓词推入专门针对from后面的子查询,所以连接列谓词推入,被推入的视图一般都是NL的被驱动表。
所以,如果驱动表ROWS过大以及CBO估算失误算的过小,就会导致被推入的视图扫描次数过多,总耗时比原来反而多了。
通过本案例,对于这块的理解更加深刻了,在此发出来,与众君共勉,如有问题,欢迎指正!