About me:Oracle ACE pro,optimistic,passionate and harmonious. Focus on ORACLE,MySQL and other database programming,peformance tuning,db design, j2ee,Linux/AIX,Architecture tech,etc
全部博文(172)
分类: Oracle
2022-04-14 10:28:36
再来看下常见的OR与子查询连用情况,在实际优化过程中,遇到OR与子查询连用,一般都不能unnest subquery了,可能会导致严重性能问题,OR与子查询连用有两种可能:
1) condition or subquery
2) subquery内部包含or,如in (select … from tab where condition1 or condition 2)
还是通过一个具体案例,分享下对于OR子查询优化的处理方式,在某库11g R2中碰到如下SQL,几个小时都没有执行完:
先来看下执行计划:
怎么通过看到这个执行计划,一眼定位性能慢的原因呢?主要通过下列几点来分析定位:
1) 执行计划中的Rows,也就是每个步骤返回的cardinality很少,都是几行,在分析表也不是太大,那么怎么可能导致运行几个小时都执行不完呢?执行时间与执行计划关键指标不匹配。很大原因可能就在于统计信息不准,导致CBO优化器估算错误,错误的统计信息导致错误的执行计划,这是第一点。
2) 看ID=15到18部分,它们是ID=1 FILTER操作的第二子节点,第一子节点是ID=2部分,很显然,如果ID=2部分估算的cardinality错误,实际情况很大的话,那么对ID=15到18部分四个表全扫描次数将会巨大,那么也就导致灾难产生。
3) 很显然,ID=2部分的一堆NESTED LOOPS也是很可疑的,找到ID=2操作的入口在ID=6部分,全表扫描DEALREC_ERR_201608,估算返回1行,很显然,这是导致NESTED LOOPS操作的根源,因此,需要检验其准确性。
主表DEALREC_ERR_201608在ID=6查询条件中经查要返回2000w行,计划中估算只有1行,因此,会导致NESTED LOOPS次数实际执行千万次,导致效率低下,应该走HASH JOIN,需要更新统计信息。
另外ID=1是FILTER,它的子节点是ID=2和ID=15、16、17、18,同样的ID 15-18也被驱动千万次。
找出问题根源后,逐步解决。首先要解决ID=6部分针对DEALREC_ERR_201608表按照查询条件substr(other_class, 1, 3) NOT IN (‘147’,‘151’, …)获得的cardinality的准确性,也就是要收集统计信息。
然而发现使用size auto,size repeat,对other_class收集直方图均无效果,执行计划中对other_class的查询条件返回行估算还是1(实际2000w行)。
1)DEALREC_ERR_201608与B_DEALING_DONE_TYPE原来走NL的现在正确走HASH JOIN。Build table是小结果集,probe table是ERR表大结果集,正确。
2)但是ID=2与ID=11到14,也就是与TMI_NO_INFOS的OR子查询,还是FILTER,驱动数千万次子节点查询,下一步优化要解决的问题。
3)性能从12小时到2小时。
现在要解决的就是FILTER问题,对子查询有OR条件的,简单条件如果能够查询转换,一般会转为一个union all view后再进行semi join、anti join(转换成union all view,如果谓词类型不同,则SQL可能会报错)。对于这种复杂的,优化器就无法查询转换了,因此,改写是唯一可行的方法。分析SQL,原来查询的是同一张表,而且条件类似,只是取的长度不同,那么就好办了!
如何让带OR的子查询执行计划从FILTER变成JOIN。两种方法:
1)改为UNION ALL/UNION
2)语义改写.前面已经使用语义改写,内部转为了类似UNION的操作,如果要继续减少表的访问,则只能彻改写OR条件,避免转换为UNION操作。
再来分析下原始OR条件:
上面含义是ERR表的TMISID截取前8,9,10,11位与TMI_NO_INFOS.BILLID_HEAD匹配,对应匹配BILLID_HEAD长度正好为8,9,10,11。很显然,语义上可以这样改写:
ERR表与TMI_NO_INFOS表关联,ERR.TMISID前8位与ITMI_NO_INFOS.BILLID_HEAD长度在8-11之间的前8位完全匹配,在此前提下,TMISID like BILLID_HEAD ||’%'。
现在就动手彻底改变多个OR子查询,让SQL更加精简,效率更高。改写如下:
执行计划如下:
1)现在的执行计划终于变的更短,更易读,通过逻辑改写走了HASH JOIN,最终一条返回300多万行数据的SQL原先需要12小时运行的SQL,现在3分钟就执行完了。
2)思考:结构良好,语义清晰的SQL编写,有助于优化器选择更合理的执行计划,所以说,写好SQL也是门技术活。
通过这个案例,希望能给大家一些启发,写SQL如何能够自己充当查询转换器,编写的SQL能够减少表、索引、分区等的访问,能够让ORACLE更易使用一些高效算法进行运算,从而提高SQL执行效率。
其实,OR子查询也不一定就完全不能unnest,只是绝大多数情况下无法unnest而已,请看下例:
不可unnest的查询:
可以unnest的查询:
这2条SQL的差别也就是将条件or id3=id2+1000转换成or id3-1000=id2,前者不可以unnest,后者可以unnest,通过分析10053可以得知:
不可unnest的出现:
SU: Unnesting query blocks in query block SEL$1 (#1) that are valid to unnest.
Subquery Unnesting on query block SEL$1 (#1)SU: Performing unnesting that does not require costing.
SU: Considering subquery unnest on query block SEL$1 (#1).
SU: Checking validity of unnesting subquery SEL$2 (#2)
SU: SU bypassed: Invalid correlated predicates.
SU: Validity checks failed.
可以unnest的出现:
并且将SQL改写为: