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个假设:

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

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

This site uses Akismet to reduce spam. Learn how your comment data is processed.