前言
作为经验丰富的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到了吗^_^？
如果读完您有所收获，不如一起转发分享，您的转发是我们持续分享的动力！
招聘啦：中亦科技北京/上海/广州/济南都在招中级和高级oracle dba,有志同道合的朋友或者身边有合适的朋友请帮忙推荐，简历请发51994106@qq.com。
我们的新公众号中亦课堂已搭建完成，继我们的【技术人生系列】后【中亦实验室】会在中亦课堂里正式和大家见面，后面我们会持续给大家分享工作中的实战案例，以案例分析为形式，原理讲解为内涵，传递学习方法为核心，让你的工作更轻松，给你带来持续的竞争优势、绩效突破与提升。
更多内容请关注——中亦课堂
中亦课堂，带你从容进步
往期经典回顾
关注公众号，回复数字查看精彩往期！
回复“039”第三十九期：技术人生系列·我和数据中心的故事（第三十九期）-温馨提示：数据库升级到11G/12C的潜在风险提示
回复“040”第四十期：技术人生系列·我和数据中心的故事（第四十期）-一次巧妙的SQL优化
回复“041”第四十一期：技术人生系列·我和数据中心的故事（第四十一期）-风险提示--这么用X86，小心ORACLE+RAC中招
回复“042”第四十二期：技术人生系列·我和数据中心的故事（第四十二期）-风险提示--风险提示：修改参数一定要确保重启后仍然生效！！
回复“043”第四十三期：技术人生系列·我和数据中心的故事（第四十三期）--这个锅，我们运维，不背！ 开发和运维都该看看
回复“044”第四十四期：技术人生系列·我和数据中心的故事（第四十四期）--数据库高手带你从“山穷水尽”到“柳暗花明”
回复“045”第四十五期：技术人生系列·我和数据中心的故事（第四十五期）--看高手如何用机器学习来分析解决数据库的疑难故障
回复“046”第四十六期：技术人生系列·我和数据中心的故事（第四十六期）--风险提示：操作系统动态增加CPU带来的数据库重启风险
回复“047”第四十七期：技术人生系列·我和数据中心的故事（第四十七期）--从一次性能压力测试达不到TPS的优化案例看应用设计的重要性
回复“048”第四十八期：技术人生系列·我和数据中心的故事（第四十八期）--X86比小型机要慢很多？性能优化专家帮你来翻案！
回复“049”第四十九期：技术人生系列·我和数据中心的故事（第四十九期）--从案例看一个好 的DBA为什么这么值钱
回复“050”第五十期：技术人生系列·我和数据中心的故事（第五十期）--生产比测试慢？从一个案例思考应用程序行为与服务器配置之间的性能关系
回复“051”第五十一期：技术人生系列·我和数据中心的故事（第五十一期）--从一个常见ORA错误的分析过程看工程师的必备技能
回复“052”第五十二期：技术人生系列·我和数据中心的故事（第五十二期）--数据库大版本升级27个约束消失之谜
回复“053”第五十三期：技术人生系列·我和数据中心的故事（第五十三期）--发现隐患，就在上线前
回复“054”第五十四期：技术人生系列·我和数据中心的故事（第五十四期）--不惧bug！一个oracle bug的分析和规避
回复“055”第五十五期：技术人生系列·我和数据中心的故事（第五十五期）--这不仅仅是SQL优化！
