ITPub博客

首页 > 数据库 > Oracle > ORACLE问题分析过程中的“暴力美学”--技术人生系列第五十六期--我和数据中心的故事

ORACLE问题分析过程中的“暴力美学”--技术人生系列第五十六期--我和数据中心的故事

Oracle 作者:记录每一次错误 时间:2018-12-04 17:13:03 0 删除 编辑

前言

作为经验丰富的DBA,我们经常会遇到各式各样的bug;然而,并不是每个bug都可以简单的直接通过现象匹配出来,很多时候,我们可能需要通过不同的维度先收集很多信息,甚至先找出问题的解决/绕行方案,再来确定问题的bug的源头;

今天的分享,我们就来看一个用特别的方法来定位、确认bug的案例,通过这个案例,我们可以举一反三,触类旁通的了解更多oracle的小技巧;在案例分享中,方法比结论来的更重要!


升级遇到问题

客户有一套系统需要从10.2.0.4升级到11.2.0.4,在此之前,我们对系统做了几轮SPA性能测试。在测试过程中,发现以下SQL执行效率出现严重衰减。

可以看出, 10g上执行时间为214s,在11g上执行居然要用9768s ,其他资源消耗也都出现大幅度增加:


回到过去


于此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到了吗^_^?

本文转载于中亦安图


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31547066/viewspace-2284115/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
性格开朗,有较强的学习能力,对oracle数据库的体系结构,搭建RAC,timesten,goldengate,分布式数据库,dataguard,系统调优有较深入的了解, 尤其是oracle优化,深入学习的主机命令,对数据库的优化,SQL语句的优化有深入的认识,目前正在shell脚本,mysql,以后会有计划学习大数据和python。

注册时间:2018-07-23

  • 博文量
    182
  • 访问量
    326356