本文作者：老猫

前言

关于SQL的问题，老猫最常遇见的就是让客户头大分析起来麻烦的性能问题，巴特，对于老猫这种作为长期支持各种客户的老司机，这根本不叫搞事情呀！
对客户而言，最怕遇到的就是trouble shooting有一定难度，原因还多样的wrong result了，因为目前的应用多数是web的，终端用户很分散，wrong result很难被发现，爱玩藏猫猫无所谓啦，但造成很严重的业务问题，就是wrong result的不对了！
今天，老猫就来与大家分享一下最近遇到的wrong result的案例，中间涉及10053 trace的简单解读、bug的查找以及最后给出相应的开发建议，快get起来！

问题来了

自从干上第三方，平时忙的要死，周末还有团队的技术沙龙，不是听课就是讲东西。只有周六可以好好的睡个大懒觉。某周六正睡的好好的，被一阵电话铃声吵醒，“老猫，这边有条SQL，本来应该只返回一条记录，结果却返回了很多条记录，这是为什么呢？SQL已经发你邮箱了！”
问题就是集结号，老猫立刻就精神饱满的投入到问题分析的进程中了；

简单问题简单看

老猫带着疑惑打开邮件，果然是与我们合作已久的客户了，给出的信息都很全，首先关注具体的SQL语句，语句很简单易懂，经过简化脱敏后的SQL是这样的：

可以看到，语句逻辑无非是多个表union all之后的结果集先做一次group by，然后在此结果集的基础上，再次进行group by操作，不过这次group by前先使用to_char函数将cdate列格式化成只含月份数据的信息；
单看语句并不能说只返回一条记录，不过接下来看看客户发过来的结果信息就可以作出判断了：

再明显不过了，明明都是201705的数据，分组之后却还是单独列出来，这样看来，是遇到了wrong result的相关bug了。

没错，wrong result就是这样，来无声去无息，它并没有具体的报错码，我们通常也不能一眼看出他的错误发生在哪一步了，这个时候如果是你，你会怎么接着往下分析呢？小小思考之后不如看看老猫如何获取解决问题的钥匙。
关于wrongresult：
通常来说，wrong result并不好判断，特别是当查询非常复杂时，通常我们是无法简单判断最后的结果应该是什么样的，所以是属于非常严重而且又隐蔽的一种报错；关于wrong result，MOS上有一篇比较实用的文章-《Wrong Results Issues - Recommended Actions (文档 ID 150895.1)》，其中列出了出现wrongresults的几种常见情况：
当遇到ORA-1795/ORA-1791/ORA-1722等错误时，可能需要注意，这些错误的背后根本原因就是wrong result了；
涉及到排序组合等操作时存在的wrong result；
涉及NULL值比较的时候存在的wrong result；
涉及并行操作的时候存在的wrong result；
同时，在文章中给出了几个可选的解决/规避方案，以及并给出oracle提供的诸如dbms_sqldiag包，SPM工具等都可以用来收集并协助判断问题；

特征分析

对于相对来说简单的语句，以老猫的经验，倒是不必把它想的太过复杂了，我们可以先通过语句本身和结果集的特征来简单分析一下：
1.回想一下语句本身，是否存在不必要的冗余部分呢？上面的语句是否可以直接简化成如下呢？而简化以后是否还会出现wrong result呢？

2.从结果来看，针对语句，似乎只做了第一层group by，而没有做第二层group by；从语句的逻辑上看，做第二层group by的根本意义在于将cdate类型格式化月份进行统计，而结果本身确实有已经被格式化成月份了；如果说完全没有做第二层group by，那么似乎返回的结果可能就不是按月份来显示的了，而如果说做了第二层group by，那为什么结果又不对呢？到这里，问题变得有意思起来，是to_char函数改变了什么还是group by针对丢失了呢？
对于to_char函数带来的影响我们无法直接辨别，但是group by到底有没有做，我们可以通过语句执行时的执行计划来判断。
幸好，我们有客户给过来的10053trace文件，其中显示的执行计划是这样的：

看起来是只有一个group by，看起来，优化器还是比较聪明的，已经意识到了语句中的两个group by的冗余性；
我们知道，优化器在生成执行计划之前，会对语句进行优化改写，这个过程其实可以在10053的trace 文件中找到，通过关键字”Final query”即可找到10053最后用来生成执行计划的语句了：

格式化之后查询语句是：

仔细看一看问题出在了哪里，可以看到最后的GROUP BY "from$_subquery$_002"."CDATE并不是我们预期的，我们预期的应该是GROUP BY to_char(from$_subquery$_002.CDATE,'yyyymm')；而这里的不正确导致了整个语义的变化，自然结果也就存在错误了；

新的问题来了：在原语句中存在两个group by，内层的没有带to_char函数，外层带有to_char函数，那么这里是在按照我们之前预期的简化之后将to_char函数去掉了呢，还是优化器偷懒直接将内层的group by作为整个语句的分组函数了；

进一步定位

通常来说，SQL在被优化器进行查询转换的时候是经过了很多步骤才转换成其最终的步骤的，这里我们看到final的查询并不是我们所期待的，那我们就必须从trace文件中final query位置往前检索，对比前后的变化，看看是哪一步把关键的group by弄丢了或是弄错了。
很快，我们找到了这样一个步骤：

语句格式化后是这样的：

所在的步骤涉及到一个关键词是”after group-by elimination”，翻译过来就是group-by消除，看起来这个地方出了问题；
找到问题，来解决问题就简单了，MOS搜索关键字”group by elimination”很快就能找到一个bug，bug给出的workaround都是将参数”_optimizer_aggr_groupby_elim”设置为false，而且bug指向12.1.0.2的版本，经过对比，我们才知道事实上在11g中时没有这个参数的，也就是说它是12c新引进的一个参数，显然，这个系统是从11g或更早版本升级上来的，如果在测试阶段做了SPM测试，也许就能在此之前避免该错误；我们从已有的10053trace文件中也能看到数据库的版本是12c：

我们简单让客户测一下，结果如下：


在会话级别将参数设置为false，则可看到执行计划正常没有将group by去掉，而执行结果也就是我们预期的了；

Bug定位与根因分析

还有一点需要注意的，我们注意到虽然_optimizer_aggr_groupby_elim参数引起的bug不少，但是毕竟这里改参数只是oracle提供的一个workaround，我们还需要通过精确的匹配相应的bug，针对具体bug打上具体的补丁：
我们使用关键字“wrong results“”和”_optimizer_aggr_groupby_elim”进行搜索，可以找到文档如下，其给出的描述与我们遭遇的案例一致：

其解释也比较清楚，出现这种bug是有条件的：

1. 内层查询的group by的聚合列与外层的聚合列存在关系；
2. 外层的聚合列在group by时存在一些函数转换的操作；
   简单的说，就是CBO在这里自作聪明，认为里层外层都是针对同一个列的，而就忽略了外层聚合时使用的函数，导致了bug，而这个步骤是在由“_optimizer_aggr_groupby_elim”作为开关控制的group-by elimination步骤里实现的；最终的建议打patch 21826068；
   打完patch以后，再打开参数_optimizer_aggr_groupby_elim；

最后的建议：

我们在最后再来回顾我们的原始语句：

与bug文档给出的示例语句有什么差别呢？

能发现其区别吗？其实最简单的一点就是，我们的语句是可以手动简化的，而MOS文档中给出的案例则并不能手动简化，也就是说，像MOS文档给出的案例属于被动遇上了bug，并没有好的办法，而我们客户给过来的语句则是，本可以主动避免，却因为一些因素撞到了ORACLE bug的枪口上；
老猫作为资深开发及DBA，日常的工作就是分析各种客户的问题、bug，ORACLE作为一个功能强大的数据库软件，必然是存在许多bug的，我们在日常使用使遇到各种bug也并不奇怪；
但是，如这里我们的CASE所示，老猫能给大家的忠告则是，作为开发人员，如果有一些语句能自己简化的最好自己来简化，最好不要麻烦ORACLE来帮你解决，一方面是简化需要消耗资源，另一方面，它能不能把你简单就能做到的事做好，就要带着问号去了！