前言
作为经验丰富的DBA，我们经常会遇到各式各样的bug；然而，并不是每个bug都可以简单的直接通过现象匹配出来，很多时候，我们可能需要通过不同的维度先收集很多信息，甚至先找出问题的解决/绕行方案，再来确定问题的bug的源头；
今天的分享，我们就来看一个用特别的方法来定位、确认bug的案例，通过这个案例，我们可以举一反三，触类旁通的了解更多oracle的小技巧；在案例分享中，方法比结论来的更重要！
升级遇到问题
客户有一套系统需要从10.2.0.4升级到11.2.0.4，在此之前，我们对系统做了几轮SPA性能测试。在测试过程中，发现以下SQL执行效率出现严重衰减。
回到过去
由
于此SQL在10.2.0.4环境中是正常的（快的），只是到11.2.0.4环境中变慢了，
我们可以通过一个参数将11.2.0.4的优化器回退到10.2.0.4
。然后再执行此SQL：
优化器回退到10.2.0.4之后，执行计划发生变化，执行时间也下降到60s，比10.2.0.4中的214s还要快：
既然10g环境执行计划是正常的，那为什么升级到11g之后，执行计划会变慢呢？
10g/11g环境执行计划比对
11.2.0.4环境开启10053跟踪后，执行此SQL，得到执行计划慢的10053 trace。
11.2.0.4环境先设置OPTIMIZER_FEATURES_ENABLE='10.2.0.4'，再开启10053跟踪，执行此SQL，得到执行计划快的10053 trace。
比对两者执行计划，发现以下不同：
10.2.0.4环境快的执行计划：IDX_C….BYFLAG1_2和LL…STER做完HASH JOIN RIGHT ANTI后，是和LL..SE表做NESTED LOOPS。
11.2.0.4环境慢的执行计划：IDX_C…BYFLAG1_2和LL…STER做完HASH JOIN RIGHT ANTI后，却是和IDX_...COMCODE做NESTED LOOPS。
可以看出，升级到11.2.0.4后，表的连接顺序变了，那表的连接顺序为什么会变呢？
仔细看上面的两个执行计划，其实不难看出两个执行计划中，
第16/17步中的HASH JOIN RIGHT ANTI的Cardinality估算差异很大
：
通过10053，我们可以看到HASH JOIN RIGHT ANTI的Cardinality是怎么算出来的：
10.2.0.4优化器模式，10053 trace：
Anti Join Card: 0.000000 = outer (102993.000000) * (1 - sel (1.000000))
以上公式，1 - sel (1.000000)=0，所以不管前面值是多少乘以0，最终都是0，四舍五入后cardinality为1.
11.2.0.4优化器模式，10053 trace：
Anti Join Card: 1029.930000 = outer (102993.000000) * (1 - sel (1.000000))
到11.2.0.4后，优化器算法做了改动，即使1 - sel (1.000000)=0，也只是将前面的估算值乘以1%而已，四舍五入后刚好是1030.
也就是说，由于11.2.0.4优化器模式中，HASH JOIN RIGHT ANTI的Cardinality估算偏大，极有可能导致了HASH JOIN RIGHT ANTI之后的结果集和后续的表的连接顺序选错。
从上面测试来看，回退了优化器版本，执行计划也就恢复了正常；
显然在这里，我们需要升级到11.2.0.4，如果为了某一个或者某一类SQL将优化器仍旧设置为10.2.0.4版本的话，我们的升级意义也就不大了！
这里大家不妨先思考一下，设置参数OPTIMIZER_FEATURES_ENABLE为不同的版本的本质是什么呢？
我们先不给出解答，先继续分析眼前的问题；
求助于MOS
看起来，好像是优化器方面的问题，与ANTI join相关，于是先使用关键字：ANTI join cardinality在MOS中匹配搜索，发现以下BUG和本案例现象吻合：
Bug 8335178 - Cardinality of ANTI / SEMI joins is overestimated (文档 ID 8335178.8)
该BUG会导致在ANTI/SEMI join时的Cardinality估算过高，确定受影响的版本是11.1.0.7，号称11.2.0.1已经解决，但是我们在11.2.0.4上似乎仍然命中了这个问题：
根据该BUG提供的解决方法：
Set "_optimizer_improve_selectivity "= FALSE
然而，经测试，并没有效果！通常来说，问题到了这里似乎卡住了，接下来我们应该怎么接着分析呢？继续通过各种我们认为的关键字在MOS上搜索？还是寻找一些别的特殊方法？
这里，不妨停下来想一想，如果是你，你会怎么做呢？
分析问题的暴力美学
前面，我们其实有提到一个问题，OPTIMIZER_FEATURES_ENABLE参数设置为不同版本的本质是什么呢？事实上，如果我们仔细对比读过10053的trace文件，就会发现，在调整OPTIMIZER_FEATURES_ENABLE参数后，在trace中的很多参数发生改变，另外还有一些fix的状态出现disable/enable的变化；
所以，本质上，OPTIMIZER_FEATURES_ENABLE的变化除了优化器相关具体代码可能存在的变化外，很大一部分的改变是对于不同隐含参数的默认值修改、不同bug修复的开关！
知道了这一原理，我们其实就可以在不调整OPTIMIZER_FEATURES_ENABLE整体参数的情况下，逐个调整隐含参数、bug修复的开关来验证具体是哪个参数影响了这里SQL的执行计划；
1.取出以上测试案例，两个10053 trace文件中优化器参数部分。
2.比对出参数差异部分，这些差异的参数，就是回退优化器版本到10.2.0.4底层会修改的参数集合。
将差异部分处理成修改命令，放到para配置文件中：
编辑脚本，依次测试每个参数，并取出对执行计划产生影响的参数
his.log中记录的是修改参数后，HASH JOIN RIGHT ANTI的Cardinality仍然是1030的：
那7356191又是什么呢？
查询MOS，找到以下BUG:7356191，该BUG与BUG: 8335178恰好相反，意思是在ANTI/SEMI join时的Cardinality估算低，确定受影响的版本是10.2.0.3/4，已经在11.2上修复，也就是说11.2之后ANTI/SEMI join的Cardinality估算会加大，此SQL在11.2.0.4上执行HASH JOIN RIGHT ANTI的Cardinality估算成1030（10g环境估算为1），偏高。
说明11.2之后，ANTI/SEMI join时的Cardinality估算确实加大了。
参数测试
从上面脚本执行结果，会影响到HASH JOIN RIGHT ANTI的Cardinality算法的，有下面三个参数设置：
到这里，我们似乎知道了问题的答案，但是这个问题的推导逻辑似乎还并没有捋清楚，为什么需要这两个参数同时设置？这里，我们遇到的到底是什么问题？
基于结论的反向推测
首先，此SQL在11.2.0.4环境执行变慢的原因是HASH JOIN RIGHT ANTI的Cardinality估算过高导致。这个问题正好命中BUG: 8335178，Oracle可解决方法是设置：
所以，BUG: 8335178和BUG:7356191其实在本质上是Oracle不同版本上的同一个问题。
总结
问题SQL执行计划变慢的原因是，升级到11.2.0.4之后，优化器对ANTI/SEMI join时的Cardinality的算法发生改变，最终通过MOS搜索和暴力破解，找到了回退到10.2.0.4算法的参数：
从这个CASE，我们能看到，很多时候对于bug的确认，我们需要的是先确认结论，然后根据结论再来捋清问题的由来；
而这里，最重要的是，找到问题结论的方法，通过设置10053的trace中列出的那一系列参数，精准匹配确认bug，这个方法你get到了吗^_^？
本文转载于中亦安图
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31547066/viewspace-2284115/，如需转载，请注明出处，否则将追究法律责任。
