踩坑CBO，解决那些坑爹的SQL优化问题_PART2

接PART1：http://blog.chinaunix.net/uid-7655508-id-5834507.html

2.CBO优化器坑的解决之道

 本部分主要分享下日常常见优化器问题案例，有的问题不仅限于CBO优化器，由于CBO是目前广泛使用的优化器，因此，一律纳入CBO问题。

2.1 FILTER性能问题

FILTER操作是执行计划中常见的操作，这种操作有两种情况：

1.只有一个子节点，那么就是简单过滤操作。

2.有多个子节点，那么就是类似NESTED LOOPS操作，只不过与NESTED LOOPS差别在于，FILTER内部会构建HASH表，对于重复匹配的，不会再次进行循环查找，而是利用已有结果，提高效率。但是一旦重复匹配的较少，循环次数多，那么，FILTER操作将是严重影响性能的操作，可能你的SQL几天都执行不完了。

 

下面看看各种情况下的FILTER操作：

单子节点：

很显然ID=1的FILTER操作只有一个子节点ID=2，这种情况下的FILTER操作也就是单纯的过滤操作。   

多子节点：
FILTER多子节点往往就会有巨大性能问题，主要出现在子查询无法UNNEST查询转换，经常遇到的情况就是NOT IN子查询、子查询和OR连用、复杂子查询等情况。

2.1.1 NOT IN子查询中的FILTER

先来看下NOT IN情况：
 

针对上面的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，那该怎么做呢？

1）  将NOT IN部分的匹配条件，针对本例就是ANTI_TEST1.object_id和ANTI_TEST2.object_id均设为NOT NULL约束。

2）  不改NOT NULL约束，则需要两个object_id均增加IS NOT NULL条件。

3）  改为NOT EXISTS。

4）  改为ANTI JOIN形式。

以上四种方式，大部分情况下均能达到让优化器走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回表需要过滤大量数据。实际此步骤有：27w行

也就是这条SQL要运行10天以上了，太慢了。

针对此问题的分析如下：

1）  查询和NULL AWARE ANTI JOIN相关的隐含参数是否有效

2）  收集统计信息是否有效

3）  是否是新版本BUG或者升级中修改了参数导致的

 

针对第一种情况：

  参数是TRUE，显然没有问题。

针对第二种情况：
  收集统计信息发现无效。
  那么此时，只能寄希望于第三种情况：可能是BUG或者升级过程中修改了其它参数影响了无法走NULL AWARE ANTI JOIN。ORACLE BUG和参数那么多，那么我们怎么快速找到问题根源导致是哪个BUG或者参数导致的呢？这里给大家分享一个神器SQLT，全称（SQLTXPLAIN），这是ORACLE内部性能部门开发的工具，可以在MOS上下载，功能非常强劲。

回归正题，现在要找出是不是新版本BUG或者修改了某个参数导致问题产生，那么就要用到SQLT的高级方法：XPLORE。XPLORE会针对ORACLE中的各种参数不停打开、关闭，来输出执行计划，最终我们可以通过生成的报告，找到匹配的执行计划来判断是BUG问题还是参数设置问题。

使用很简单，参考readme.txt将需要测试的SQL单独编辑一个文件，一般，我们测试都使用XPLAIN方法，调用EXPLAIN PLAN FOR进行测试，这样保证测试效率。

 

SQLT找出问题根源：

 
  最终通过SQLT XPLORE找出问题根源在于新版本关闭了_optimier_squ_bottomup参数（和子查询相关）。从这点上也可以看出来，很多查询转换能够成功，不光是一个参数起作用，可能多个参数共同作用。因此，关闭默认参数，除非有强大的理由，否则，不可轻易修改其默认值。至此，此问题在SQLT的帮助下，快速得以解决，如果不使用SQLT，那么解决问题的过程显然更为曲折，一般情况下，估计是让开发先修改SQL了。

 

  思考一下，原来的SQL是不是还可以更优化呢？

  很显然，如果要进一步优化，要彻底对SQL进行重写，通过观察，2个子查询部分有相同点，经过分析语义：查找表DT_MBY_TEST_LOG在指定INSERT_TIME范围内的，按照每个TBILL_ID取最小的INSERT_TIME，并且ID不在子查询中，然后结果按照INSERT_TIME排序，最后取TOP 199。

  原SQL使用自连接、两个子查询，冗余繁杂。自然想到用分析函数进行改写，避免自连接，从而提高效率。改写后的SQL如下：

执行计划：

  至此，这条SQL从原来的走FILTER需要耗时10天，到找出问题根源可以走NULL AWARE ANTI JOIN需要耗时7秒多，最后通过彻底改写耗时3.8s。

未完待续：参考PART3：