阅读量:10
前言
一个复杂问题通常可以拆解成若干个简单问题来分析，如何从各个维度来分析和确认一个问题，并最终给出合理的解决方案却又是一个比较复杂的过程；今天给大家分享的就是，从一个"简单"的报错最后定位到bug，并最终结合具体CASE给出解决方案的案例，希望大家能从中领悟到解决问题过程中的各种技巧和思路，同时告诉大家有些oracle bug并不可怕，其实我们自己就可以给出解决方案。
“简单”的问题
某日，正是工作繁忙时，某系统的月度批量任务执行过程中出现报错，该批量任务已经连续执行了N个月了，但是这个月执行重跑了很多次，就是过不去；目前应用团队的开发DBA已经定位到问题SQL了。
报错信息如下：
分析一下报错：
1.直接导致出错的原因是递归SQL执行出错（抛ORA-604）
2.而导致递归SQL执行出错的原因是数据类型不一致（抛ORA-932），期望的是NUMBER类型，而实际上拿到的是T_INDEX_STR类型
看完这个报错之后，心想可不要被看起来比较复杂的SQL语句给吓到了，可以快速定位到问题；
如果是你，你会如何来分析这个问题呢？不妨先开动一下脑筋，看看你的思路是不是跟作者一样呢……
快速解决“简单问题”
最终的报错信息是类型不一致，这种报错引发的第一个想法就是隐式类型转换出现了问题，有了这个想法，其实直接看执行计划就可以帮忙确认有没有隐式转换：
很幸运，执行计划里可以看到了明显的隐式转换，因为SQL中的写法是T.STR=1，而实际上T.STR类型并不是数字类型，跟应用确认这里T.STR类型确实是一个自定义类型T_INDEX_STR的子类型；
自定义类型的子类型？对于开发说的一些东西有些疑惑，不过我们先抛开这些，解决问题要紧；既然隐式类型转换出错，那我们就应该避免隐式类型转化：将原来的T.STR=1改成T.STR=’1’，并且需要应用去检查业务数据，是不是存在非法数据，原本预期都是‘12345’的字符数据，现在可能存在类似’abcdefg’这样的字符，可能就是这些数据无法转换成数字，进而导致了报错；
在提完变更之后，立刻就修改存储过程里的SQL语句，很快就跑到了这个问题SQL，满心期待的等着继续，然而........
事与愿违，问题依旧，这个SQL依然没能正常跑下去！问题在哪呢？我们前面的思考有什么问题？还是说，有多个地方出现了隐式转换，并没有再执行计划中显示出来？反思一下，分析才刚刚开始………
反思&重新开始
首先，确认执行计划中隐式转换已经消失：
看来隐式转换已经不存在了，再反思一下，刚刚的想法是对的吗？
再次思考这个地方的时候，我发现前面犯了一个经验主义的错误：当看到类型不匹配的时候，都没有细细思考，就去找隐式转换，一旦找到了隐式转换，就笃定是这里的问题，以至于忽略了真正的问题！
再仔细看看报错信息和隐式转换的情况：
报错信息显示的期望的是NUMBER类型，而隐式转换使用的是TO_NUMBER函数，显然TO_NUMBER函数并不要求传入一个NUMBER类型的数据，所以，问题从一开始就并不是出在这个地方！
经验主义误导人，看来这个问题不可轻视，摒弃原来固有的想法，重新从头来分析这个问题，找出类型匹配错误的位置；
我们先仔细看看SQL是不是有什么特殊的地方：
从语句上看，就是一个merge语句，不过这里使用了一个看起来比较特殊的函数F_INDEX_STRING_CHARS，这个函数返回了一个表类型的数据，通过TABLE函数将其转换成一个表，再与D表关联，除此之外，就并没有什么其他特殊的地方了；
与这个特征有关吗？如果是你，你会如何继续分析呢？思考一下，精彩马上继续……
直奔关键点
其实，从前面的语句即使看到了特殊的函数，我也并不着急去分析那个函数，毕竟我不是开发DBA，对业务逻辑并没有那么清楚，通常对于存储过程、函数等大段的代码读起来，还是会有些吃力的；作为运维DBA，我们对ORACLE的本质应该要更清楚，我们更应该优先发挥我们自己的特长；
这里，SQL报错为递归语句出现了报错，因为执行计划我们是可以看到的，所以很显然问题并不是在解析阶段，而是在执行阶段，我们不妨设置一个10046事件追踪整个SQL的执行过程，看看是执行什么递归SQL或者是哪个阶段出现了问题；很快，我们就抓到了问题SQL语句：
具体的递归SQL语句格式化之后如下：
这条语句在解析的时候就遇到了ORA-932的错误，也就是前面抛出的类型不匹配的错误；在数据库上手动执行上述语句，同样会报错；很快我们就可以用排除法找到了导致报错的关键那一行，即IDX这个字段导致了错误；
从这里看，KOKBF$1是一个特殊的表TABLE(XXX)的别名，而IDX字段的内容是LPAD(TO_CHAR(VALUE(KOKBF$1)), 3, '0')，而这里ORACLE居然把一个表作为作为参数传给VALUE函数后，它认为将会返回一个数字或者字符串，但是显然并不是如此，我们简单通过别的方式同样验证一下也能得出类似的错误：
这样看来，oracle在形成前面的递归SQL时出现了问题！递归SQL的语法存在问题，显然是oracle的bug；
知道是oracle的bug了，但是应用维护人员确认说，上个月同样执行过这个存储过程，执行过这个SQL，但是没有任何问题，为什么今天执行就出错了呢？
这一问，有些犀利了，不过也给了我们提醒，这里的CASE我们应该是能找到触发条件的，况且即使上MOS搜索bug，我们也需要更精确的一些条件才能去匹配。
接下来，如何进一步分析呢？是上个月的数据与这个月的数据不一致了？还是什么其他原因呢？
深入挖掘
既然看到递归SQL出错，那就首先分析递归SQL的特征，与我们的原始merge语句有什么关联吗？
我们可以看到，原始merge语句也是将F_INDEX_STRING_CHARS函数的返回值作为一个表，其别名为W，只不过IDX字段实际上是W.INDEX， STR字段实际上是W.STR字段；而在递归语句中，将KOKBF$1传入VALUE函数后，直接将各个列名丢弃了；
那这里是自定义函数，自定义类型出现了问题吗？看来，还是得好好分析自定义函数和自定义类型的定义：
这个自定义函数很人性化，将几个自定义类型的定义也写到了注释里，从这些定义来看，并不复杂，不过我们只需要关注几个特征点：
两个特征
1.F_INDEX_STRING_CHARS函数返回的确实是一个叫T_INDEX_STRS的TABLE类型数据；
2.其中T_INDEX_STRS包含两列，分别是NUMBER类型的INDEXES列和VARCHAR类型的STR列；
并没有什么问题呀，定义本身没有什么问题，只是递归语句确确实实丢掉了一些东西？
理论上正确的递归语句应该是需要加上列名的：
看起来，还是跟自定义类型有关，在同一个递归SQL中其他的表（比如XXX_DAEMON表）的列就并没有被丢弃；
接下来，就需要深入研究了，开始深入研究的时候，自己模拟和重现错误就非常重要了，模拟应该从哪里着手开始呢？……..
开始模拟
首先我们将模拟的目标指向自定义类型，同样创建了相同的函数、类型的定义，同样在SQL语句中使用类型转换，语句执行没有任何问题，然而在执行计划的谓词过滤中，我们能清楚的看到了类似的LPAD(TO_CHAR(VALUE(KOKBF$)),3,'0')字样，从理论上看，这个谓词条件应该也是存在问题的，但是并不影响执行；
于是我们针对这个SQL的解析
设置一个10053事件
，看看这个谓词信息是怎么来的：
在10053的trace文件中表明，我们执行的语句最终在优化器转换的时候，被转换成了
SELECT COUNT(*) "COUNT(*)" FROM TABLE("TEST"."F_INDEX_STRING_CHARS"('1100000000')) "KOKBF$0"
WHERE LPAD(TO_CHAR(VALUE(KOKBF$0)),4,'0') LIKE '%1%';
这样的一个语句，然而这个语句实际上是不可执行的，这只是一个中间状态，最终的执行过程中用的谓词条件是：LPAD(TO_CHAR(SYS_OP_ATG("KOKBF$0"."SYS_NC_ROWINFO$",2,3,2)),4,'0') LIKE '%1%'
只不过，在执行计划显示时使用了这个中间状态的谓词信息；
那么我们的merge语句在产生递归语句的时候，为什么会去通过一个中间状态的SQL来形成递归SQL呢？
大胆猜测&深入测试
再回归我们的递归SQL语句，看看原始SQL的执行计划：
其中：递归SQL的前面部分实际上是调用了函数TBL$OR$IDX$PART$NUM，顾名思义我们也能知道，这显然是针对某个分区表，通过传入某些参数值来找到其所在的分区号；
而执行计划部分我们也能看到，在第5-12步，自定义类型表其实前面与XXX_DAEMON和XXX_TOPIC表已经有过关联，均没有问题，而这里的递归SQL正是为了满足执行计划中的PARTITION LIST SUBQUERY而做的，看起来，问题不仅仅是与自定义类型有关，还与PARTITION PRUNNING有关；
PARTITION PRUNNING(分区修剪)
当访问分区表的时候，优化器分析FROM和WHERE子句来消除不必要的分区，减少无效数据的访问量，在相关的执行计划中我们经常能看到类似的关键字：
partition list single
partition list iterator
partition list subquery
partition range single
partition range iterator
partition range subquery
如果Oracle在解析时能够辨别连续的分区能被访问，那么执行计划中会通过PSTART和PSTOP来显示开始和结束的分区值。除此之外包括动态修剪的其他分区修剪，PSTART和PSTOP值会显示为KEY。
如果真正能使用上PARTITION PRUNING，事实上PARTITION PRUNING对大分区表（分区数特别多或者单个分区特别大）的访问效率是有很大帮助的；
这样，模拟过程就不能单单测试自定义类型表，还得结合分区修剪的操作来模拟；于是，新的模拟方案立刻形成：
第一步：创建一个分区表（list分区），手动将统计信息值设置的比较大：
第二步：构造类似的merge语句，其中在子查询中对IDX字段增加一个冗余的to_number函数，其中子查询单独执行时没有任何问题：
第三步：确认执行计划中，出现了PARTITIONLIST SUBQUERY：
第四步：执行语句，出现报错：
第五步：同样通过10046跟踪，读取trace文件，可以看到出错的递归语句如下：
由此，可以看到，该问题确实与PARTITION PRUNING有关，经过仔细测试，确切的说，实际上是与PARTITION PRUNING的SUBQUERY有关，看起来ORACLE在分区修剪SUBQUERY模块的代码出现了异常，特别是在与自定义类型表来关联的时候；实际上在测试过程中，调整相关SQL语句，能抛出各种各样的错，本质上还是PARTITION PRUNING SUBQUERY调用递归SQL时的错误语法造成的：
得出结论&解决方案
根据前面的分析与测试，可以看到这里出现报错的两个关键因素：
1.自定义类型表
2.PARTITIONSUBQUERY
再结合具体的SQL语句和原执行计划：
我们也从两个方面考虑解决方案：
解决方案
1. 去除自定义类型表：
对于这一方案，实际上在保留PARTITION PRUNING SUBQUERY的前提下，让自定义类型表不参与PARTITION SUBQUERY的相关关联，在存储过程中，我们就可以将如执行计划第4-12步中涉及的SQL语句单独摘出来，创建一个临时表；即：将merge语句中的using 部分创建一个整表，然后再来关联；该方案保留了PARTITION SUBQUEYRY的优势，但是增加了代码的修改量；
2. 去除执行计划中的PARTITION LIST SUBQUERY：
对于这一方案，只需要将隐含参数”_subquery_pruning_enabled”设置为false（可以在会话级修改）即可禁用subquery pruning的功能；具体实施方法，包括在存储过程中设置会话参数，在系统级设置参数，乃至绑定执行计划等；该方案相对较好实施，但是执行计划的变化，也有可能带来执行效率大幅降低的问题；
修改参数后，SQL能正常执行（1 row merged）：
修改参数前后的执行计划对比（执行计划由LIST SUBQUERY变为LISTALL）：
解决方案能完美解决该类问题，事实上，我们上MOS去搜索partition pruning相关的关键字，其实可以看到这方面，特别是partition subquery相关的bug特别多，但是很遗憾，没有找到与我们这里CASE完全匹配的bug；看起来，这种用法还是很生僻的，partition subquery这部分的代码段可能还是比较粗糙，留下的隐患不少；不过还好，针对这个CASE，我们有自己的workaround；
最后的疑问
针对这个CASE中，还有一个问题就在于，同样一个SQL，为什么以前执行的时候没有报错，现在跑报错了呢？
其实如果你真的仔细阅读了本篇分享，你就会从两个层面来思考这个问题：一方面来说，语句没有变过的话，那么就一定使用了自定义表、自定义类型，所以这一块是没有变的；另一方面，对于PARTITION PRUNING SUBQUERY来说，也就跟执行计划有关了，显然，这里的问题正是由于执行计划的改变引发的；
细心的同学留心一下我们从模拟测试过程，我们就会知道能导致执行计划发生变化的几个关键点，如果，看完你还没有解开这个疑问，不妨自己手动测试一把，相信你也会有自己的收获；
总结
最后总结来看，其实这个CASE的本质是，SQL语句执行计划改变后遭遇了数据库的bug，而这个bug从这里来看，关联了两个必要条件，通过逐步分析，找出这两个关键点之后，也就能顺利的找到较好的解决方案；同时在解决问题的过程中，我们也会发现ORACLE的partition pruning subquery存在着较多bug，对于以后的问题分析过程中，一旦遇到存在partition pruning的情况，需要多留意；
