深入了解SQL性能杀手FILTER操作_PART2

接：深入了解SQL性能杀手FILTER操作_PART1



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上下载，功能非常强劲。

此工具详细用法不做赘述，针对此工具，Apress也出了一本书籍，感兴趣的可以学习一下：

回归正题，现在要找出是不是新版本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。

下一篇：深入了解SQL性能杀手FILTER操作_PART3