oracle一条sql运行时间很长,SQL执行时反复一慢两快的问题

SQL执行的时间，在正常情况下应该是稳定的。如果第一次快，第二次慢，那么可能就是由于cardinality feedback的缘故，我们可以设置”_OPTIMIZER_USE_FEEDBACK”= false来规避。但是这次遇到的问题却是执行过程两快一慢，执行过程是慢->快->快->慢->快->快->慢->快->快->……，执行了慢之后，还能再快回来，这是怎么回事呢？

这个sql初次执行的时候是快的，然后把这次快的执行计划用spm固定下来，再次执行的时候，就发生了慢快快的现象。

这些现象的原因，是hint中了oracle的2个bug，我用流程图显示如下：

稍微解释一下：

我们看到，一开始SQL发起的时候，由于cardinality feedback(CFB)，且hint中Bug 14147762 Cardinality feedback causes worse plan for JPPD，(这也是通常Cardinality feedback产生第一次快后面慢的原因，可以禁用Cardinality feedback解决，但是我们不想禁用oracle的新特性，我们需要了解其中的root cause，找出解决办法)，产生了坏的执行计划，后续，检查该sql语句是否在SQL Management Base(SMB)中，由于保存过搞语句及其执行计划，所以在smb中，再进一步检查执行计划是否在smb中，由于之前保存的是那个好的执行计划，因此该执行计划不在smb中。由于执行计划不在smb中，尝试reproduce，但是此时又hint中另外一个Bug 12980183 –SPM cannot reproduce execution plan(也是和语句中含有push join pred有关，因为含有JPPD，造成无法reproduce成功。其实这个bug也容易绕过，就是使用outline，不使用SPM。)。于是无法成功reproduce执行计划。无法成功reproduce原来的执行，oracle将reproduce标记为从YES改为NO，然后使用了坏的执行计划。——执行的慢。

然后我们继续跑该sql，由于reproduce状态改变，oracle重新继续解析，注意，此时CFB不会介入，在10053的trace中也可以看到没有CFB的介入，没有看到/*+ OPT_ESTIMATE */ 的hint。 此时由于没有没有CFB的介入，生成的执行计划是好的。继续检查该sql和sql的执行计划是否在SMB中，结果是语句确实在SMB中，执行计划也是在SMB中(因为是好的执行计划)，将reproduce标记从NO改为YES，使用该好的执行计划，也是在SMB中的执行计划。——执行的快。

好了，我们继续跑第三次，再次由于reproduce状态改变，oracle重新解析，且CFB不介入。生成好的执行计划，继续检查该sql和sql的执行计划是否在SMB中，结果是语句确实在SMB中，执行计划也是在SMB中(因为是好的执行计划)，注意，此时不需要改变reproduce的标记。然后使用该好的执行计划，所以，——执行的快。

继续执行第四次，由于之前reproduce标记未变，因此，CFB介入，此时hint中Bug 14147762，生成坏的执行计划，重复到了第一步的情况，因此出现了慢的情况。

在上述的解释中，我们基于下面的2个假设：

1. 当reproduce的状态发生的时候，CFB不介入。

2. sql的再次解析发生在：

1)reproduce status change

2)cardinality feedback take effect

1

2

3

4

1.当reproduce的状态发生的时候，CFB不介入。

2.sql的再次解析发生在：

1)reproducestatuschange

2)cardinalityfeedbacktakeeffect

最终的我们找到了一慢两快的原因，为上述的2个bug申请Patch解决(在11.2.0.3上的patch，到11.2.0.4上已经fix掉)。