深入了解SQL性能杀手FILTER操作
点击上方“IT那活儿”，关注后了解更多内容，不管IT什么活儿，干就完了！！！
FILTER操作是执行计划中常见的操作，这种操作有两种情况：1.1 只有一个子节点，那么就是简单过滤操作。（不是本文重点）
1.2 有多个子节点，那么就是类似NESTED LOOPS操作，只不过与NESTED LOOPS差别在于，FILTER内部会构建HASH表（有缓存提高效率，缓存的bucket数目是1024个），对于重复匹配的，不会再次进行循环查找，而是利用已有结果，提高效率。但是一旦重复匹配的较少，循环次数多，也就是类似NESTED LOOPS，那么这种FILTER操作将是严重影响性能的操作，可能你的SQL几天都执行不完了。（本文讨论重点）
很显然ID=1的FILTER操作只有一个子节点ID=2，这种情况下的FILTER操作也就是单纯的过滤操作。
FILTER多子节点往往就是性能杀手，主要出现在子查询无法UNNEST查询转换，经常遇到的情况就是NOT IN子查询、子查询和OR连用、复杂子查询、CBO未做子查询UNNEST等情况。
3.1 NOT IN子查询中的FILTER
针对上面的NOT IN子查询，如果子查询object_id有NULL存在，则整个查询都不会有结果，在11g之前，如果主表和子表的object_id未同时有NOT NULL约束，或都未加IS NOT NULL限制，则ORACLE会走FILTER。11g有新的ANTI NA（NULL AWARE）优化，可以对子查询进行UNNEST，从而提高效率。对于未UNNEST的子查询，走了FILTER，有至少2个子节点，执行计划还有个特点就是Predicate谓词部分有：B1这种类似绑定变量的东西，内部操作走类似NESTED LOOPS操作。11g有NULL AWARE专门针对NOT IN问题进行优化，如下所示：通过NULL AWARE操作，对无法UNNEST的NOT IN子查询可以转换成JOIN形式，这样效率就大幅度提升了。如果在11g之前，遇到NOT IN无法UNNEST，那该怎么做呢？以上四种方式，大部分情况下均能达到让优化器走JOIN的目的，如下所示：说白了，unnest subquery就是转换成JOIN形式，如果能转换成JOIN就可以利用高效JOIN特性来提高操作效率，不能转换就走FILTER，可能影响效率，11g的NULL AWARE从执行计划里可以看出，还是有点区别，没有走INDEX FULL SCAN扫描，因为没有条件让ORACLE知道object_id可能存在NULL，所以也就走不了索引了。OK，现在来说一个数据库升级过程中碰到的案例，背景是11.2.0.2升级到11.2.0.4后下面SQL出现性能问题： 这里的ID=4和ID=8两个FILTER均有2个子节点，很显然是NOT IN子查询无法UNNEST导致的。上面说了在11g ORACLE CBO可以将NOT IN转换成NULL AWARE ANTI JOIN，并且在11.2.0.2上是可以转换的，到11.2.0.4上就不行了。两个FILTER操作的危害到底有多大呢，可以通过查询实际执行计划来看： 使用ALTER SESSION SET STATISTICS_LEVEL=ALL;截取2分25s的记录查看实际情况，ID=10步骤的CARD=141行就需要2分25s,主要是ID=11的索引较差，ID=11回表需要过滤大量数据。也就是这条SQL要运行10天以上了，简直太恐怖了。那么此时，只能寄希望于第三种情况（分析3）：可能是BUG或者升级过程中修改了其它参数影响了无法走NULL AWARE ANTI JOIN。ORACLE BUG和参数那么多，那么我们怎么快速找到问题根源导致是哪个BUG或者参数导致的呢？这里给大家分享一个神器SQLT，全称（SQLTXPLAIN），这是ORACLE内部性能部门开发的工具，可以在MOS上下载，功能非常强劲。此工具详细用法不做赘述，针对此工具，Apress也出了一本书籍，感兴趣的可以学习一下：回归正题，现在要找出是不是新版本BUG或者修改了某个参数导致问题产生，那么就要用到SQLT的高级方法：XPLORE。XPLORE会针对ORACLE中的各种参数不停打开、关闭，来输出执行计划，最终我们可以通过生成的报告，找到匹配的执行计划来判断是BUG问题还是参数设置问题。使用很简单，参考readme.txt将需要测试的SQL多带带编辑一个文件，一般，我们测试都使用XPLAIN方法，调用EXPLAIN PLAN FOR进行测试，这样保证测试效率。最终通过SQLT XPLORE找出问题根源在于新版本关闭了_optimier_squ_bottomup参数（和子查询相关）。从这点上也可以看出来，很多查询转换能够成功，不光是一个参数起作用，可能多个参数共同作用。因此，关闭默认参数，除非有强大的理由，否则，不可轻易修改其默认值。至此，此问题在SQLT的帮助下，快速得以解决，如果不使用SQLT，那么解决问题的过程显然更为曲折，一般情况下，估计是让开发先修改SQL了。 很显然，如果要进一步优化，要彻底对SQL进行重写。通过观察，2个子查询部分有相同点，经过分析语义：查找表DT_MBY_TEST_LOG在指定INSERT_TIME范围内的，按照每个TBILL_ID取最小的INSERT_TIME，并且ID不在子查询中，然后结果按照INSERT_TIME排序，最后取TOP 199。原SQL使用自连接、两个子查询，冗余繁杂。自然想到用分析函数进行改写，避免自连接，从而提高效率。至此，这条SQL从原来的走FILTER需要耗时10天，到找出问题根源可以走NULL AWARE ANTI JOIN需要耗时7秒多，最后通过彻底改写耗时3.8s。3.2 OR子查询中的FILTER
再来看下常见的OR与子查询连用情况，在实际优化过程中，遇到OR与子查询连用，一般都不能unnest subquery了，可能会导致严重性能问题，OR与子查询连用有两种可能：通过一个具体案例，分享下对于OR子查询优化的处理方式，在某库11g R2中碰到如下SQL，几个小时都没有执行完：
2）怎么通过看到这个执行计划，一眼定位性能慢的原因呢？主要通过下列几点来分析定位：- 执行计划中的Rows，也就是每个步骤返回的cardinality很少，都是几行，在分析表也不是太大，那么怎么可能导致运行几个小时都执行不完呢？执行时间与执行计划关键指标不匹配。很大原因可能就在于统计信息不准，导致CBO优化器估算错误，错误的统计信息导致错误的执行计划，这是第一点。
- 看ID=15到18部分，它们是ID=1 FILTER操作的第二子节点，第一子节点是ID=2部分，很显然，如果ID=2部分估算的cardinality错误，实际情况很大的话，那么对ID=15到18部分四个表全扫描次数将会巨大，那么也就导致灾难产生。
- 很显然，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行）。DEALREC_ERR_201608与B_DEALING_DONE_TYPE原来走NL的现在正确走HASH JOIN。Build table是小结果集，probe table是ERR表大结果集，正确。
但是ID=2与ID=11到14，也就是与TMI_NO_INFOS的OR子查询，还是FILTER,驱动数千万次子节点查询，下一步优化要解决的问题。
性能从12小时到2小时。
现在要解决的就是FILTER问题，对子查询有OR条件的，简单条件如果能够查询转换，一般会转为一个union all view后再进行semi join、anti join（转换成union all view,如果谓词类型不同，则SQL可能会报错）。对于这种复杂的，优化器就无法查询转换了，因此，改写是唯一可行的方法。分析SQL，原来查询的是同一张表，而且条件类似，只是取的长度不同，那么就好办了！4）如何让带OR的子查询执行计划从FILTER变成JOIN。两种方法：上面含义是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 ||’%’。6）现在就动手彻底改变多个OR子查询，让SQL更加精简，效率更高。改写如下：现在的执行计划终于变的更短，更易读，通过逻辑改写走了HASH JOIN，最终一条返回300多万行数据的SQL原先需要12小时运行的SQL，现在3分钟就执行完了。思考：结构良好，语义清晰的SQL编写，有助于优化器选择更合理的执行计划，所以说，写好SQL也是门技术活。 通过上述这个案例，希望能给大家一些启发，写SQL如何能够自己充当查询转换器，编写的SQL能够减少表、索引、分区等的访问，能够让ORACLE更易使用一些高效算法进行运算，从而提高SQL执行效率。 其实，OR子查询也不一定就完全不能unnest，只是绝大多数情况下无法unnest而已，请看下例：这2条SQL的差别也就是将条件or id3=id2+1000转换成or id3-1000=id2，前者不可以unnest,后者可以unnest，通过分析10053可以得知：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。
最终CBO先查询T3条件，做个UNION ALL视图，之后与T2关联。从这里来看，对于OR子查询的unnest要求比较严格，从这条语句分析，ORACLE可进行unnest必须要求对主表列不要进行运算操作，优化器自身并未将+1000条件左移，正因为严格，所以大部分情况下，OR子查询也就无法进行unnest了，从而导致各种性能问题。主要体现在UPDATE关联更新和标量子查询中，虽然此类SQL语句中并未显式出现FILTER关键字，但是内部操作和FILTER操作如出一辙。ID=2部分是where exists选择部分，先把需要更新的条件查询出来，之后执行UPDATE关联子查询更新，可以看到ID=5部分出现绑定变量：B1，显然UPDATE操作就类似于原来的FILTER，对于选出的每行与子查询表NEW_TAB关联查询，如果ID列重复值较少，那么子查询执行的次数就会很多，从而影响效率，也就是ID=5的操作要执行很多次。当然，这里字段ID唯一性很强，可以建立UNIQUE INDEX，普通INDEX灯，这样第5步就可以走索引了。这里为了举例这种UPDATE的优化方式，不建索引，也可以搞定这样的UPDATE：MERGR和UPDATE INLINE VIEW方式。MERGE中直接利用HASH JOIN，避免多次访问操作，从而效率大增，再来看看UPDATE LINE VIEW写法：UPDATE
(SELECT a.status astatus,
b.status bstatus
FROM old_tab a,
new_tab b
WHERE a.id=b.id
AND a.id >9000000
)
SET astatus=bstatus;
要求b.id是preserved key (唯一索引、唯一约束、主键），11g bypass_ujvc会报错，类似MERGE操作。2）再来看看标量子查询，标量子查询往往也是引发严重性能问题的杀手。标量子查询的计划和普通计划的执行顺序不同，标量子查询虽然在上面，但是它由下面的CUSTOMERS表结果驱动（上面的在后面执行，这个与普通执行计划顺序不同），每行驱动查询一次标量子查询（有CACHE例外），同样类似FILTER操作。如果对标量子查询进行优化，一般就是改写SQL，将标量子查询改为外连接形式（在约束和业务满足的情况下也可改写为普通JOIN）：通过改写之后效率大增，并且使用HASH JOIN算法。3）下面看一下标量子查询中的CACHE（FILTER和UPDATE关联更新类似），如果关联的列重复值特别多，那么子查询执行次数就会很少，这时候效率会比较好。标量子查询和FILTER一样，有CACHE，如上面的emp_a有108K的行，但是重复的department_id只有11,这样只查询只扫描11次，扫描子查询表的次数少了，效率会提升。针对FILTER性能杀手问题，主要分享这3点（3.1,3.2,3.3），当然，还有很多其它值得注意的地方，这需要我们日常多留心和积累，从而熟悉优化器一些问题的处理方法。
数据物理分布对FILTER节点执行次数的影响。
DROP TABLE t2;
CREATE TABLE t2 AS SELECT LEVEL ID FROM dual CONNECT BY LEVEL<=1024;
INSERT INTO t2 SELECT*FROM t2;
COMMIT;
对应的SQL如下：
SELECT COUNT(*)
FROM t2
WHERE ID NOT IN
(SELECT ID
FROM(SELECT ID
FROM t2
ORDER BY ID DESC)
WHERE ROWNUM<=100);
以上SQL的含义很简单，也就是对T2表中的ID降序排列，查询不在前100的数据量，然而执行却比正常的慢很多（这里作为演示，只是构造少量数据，实际数据量很多，SQL执行非常慢）。 通过前面有关FILTER内容可以知道，表T2的ID只有1024个不同值，由于FILTER有缓存，那么这个子节点正常的执行次数应该是1024次，但是上述执行计划的执行次数却是12432次，子节点的执行次数增加N倍是导致SQL变慢的主要原因。 解决这个问题，主要要搞清楚为什么执行次数不是1024次，在实际的应用中，表是按天分表的，每天的数据累计到前一天，执行计划未变，但是突然某一天变慢，通过分析，出现这种情况的原因，大概率是数据的分布不同。由于这里是按照ID构造HASH表，可以测试下按照ID顺序重新组织是什么情况。DROP TABLE t3;
CREATE TABLE t3 AS SELECT*FROM t2 ORDER BY ID;--按照关联列ID重新组织顺序
SELECT COUNT(*)
FROM t3
WHERE ID NOT IN
(SELECT ID
FROM(SELECT ID
FROM t3
ORDER BY ID DESC)WHERE ROWNUM<=100);
现在执行计划中子节点执行次数是1024次了，的确是数据的物理分布原因，看来FITER算法还不够完善，这点值得注意。 如果要进一步优化上述语句，可以通过改写成NOT EXISTS避免FILTER来提高效率，改写如下：SELECT COUNT(*)
FROM t3 a
WHERE not exists
(SELECT 1 from
(SELECT ID
FROM(SELECT ID
FROM t3
ORDER BY ID DESC)WHERE ROWNUM<=100) b
where a.ID = b.ID
);
通过执行计划可以看出，改写为NOT EXISTS后走HASH JOIN效率得到极大提升。 通过本文可以了解到，FILTER往往是导致SQL执行性能缓慢的元凶，主要是由于子查询未做UNNEST SUBQUERY查询转换，未UNNEST的原因有很多，比如统计信息不准，如果发现统计信息准确了还是无法UNNEST，那么要考虑SQL写法是否遇到优化器的限制或BUG，比如OR子查询。当然，FILTER由于内部构建HASH表，有缓存和HASH算法，对特定的查询效率可能不错，很显然是要求关联条件的重复值较少，这样子节点执行次数少，从而提高效率，所以在实际应用和优化中，还需要具体问题具体分析。
文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。
转载请注明本文地址：https://www.ucloud.cn/yun/129606.html
