其实小y最擅长的是性能优化,所以今天开始,小y会陆续的分享更多的数据库性能优化案例(含SQL优化),包括运维DBA和开发人员在内,如果有足够的耐心可以阅读完这些分享,一定会或多或少有些收获的哦。
进入正题,如果您的日终跑批/清算/报表等程序时快时慢,或者从某一天以后就一直变慢,作为运维DBA或开发的您,会怎么下手?还有,除了解决问题外,你要如何解答领导最关心的一个问题,“为什么现在有问题,但是以前没有问题呢”!
如下图所示。先剧透一下,这个CASE可不是执行计划变化导致的问题哦!
另外,前阵子有部分朋友问,小y所在的团队是否可以提供对外的第三方服务,答案是YES!
有兴趣的朋友可以加一下小y的个人微信,微信号是 shadow-huang-bj,希望可以交到更多的朋友,并帮助到更多有需要的人。
小y,有空么?一会帮忙一起看一个报表的性能问题。
有个SQL语句一周前开始,性能急剧恶化,执行时间从10分钟以内变成了10个小时以上。
这样的一条SQL,按照小y的经验,驱动表只要选择小表,那么整个HASH JOIN的执行时间等同于两张表的单表访问时间,两张表加起来不到4G,通常都可以在5分钟内完成。这和以前的执行时间是相吻合的。
这里顺便说一下:很多开发写hint往往写的不完整,例如这个hint只写了表连接方式,单表访问路径只写了一张表,表的连接顺序没有写,其实并没有完全固定死执行计划。
接下来,看看执行计划是否发生变化,执行计划是否正确。
这是一个完美、最优的执行计划。唯一的小缺点是优化器评估hash join和hash group by的步骤用到了一些临时表空间,不过这也只是评估,不代表实际会发生。
对比了以前的执行计划,也是一样的。
既然执行计划没有问题,也没有发生改变,那么就需要将SQL的执行时间进行分解,看看时间到底消耗在了是CPU还是IO、集群、并发竞争等什么环节
1、每次执行时间39,615秒,超过10个小时
2、每次执行逻辑读只有45,276个block(块)
3、每次执行物理读451,421个block(块),
4、时间基本都消耗在CPU上,达到38719秒,超过10个小时,而在IO/集群/应用(锁)/并发环节消耗时间很小。
到了这里,经验丰富的DBA应该可以发现,该CASE出现了一些奇怪的现象。
不过还是要照顾一下大家,先来回答一些朋友心里可能的问题。
看到这里,也许有人会说:
是不是SQL语句执行过程中有什么异常的等待事件?
首先答案是NO!因为整个SQL的执行时间中,时间基本都消耗在CPU上,达到38719秒,超过10个小时,而在IO/集群/应用(锁)/并发环节消耗时间很小(加起来不到100秒)。如果SQL跑在CPU上,那么是不会有等待事件的线索的。时间分布如下图所示。
也许有人会说:
执行计划出现了temp表空间的使用,是不是hash join One-pass/Muti-pass导致SQL执行慢
到这里,小y开始感觉到了这个case需要更专注来解决了!
执行时间基本都耗在CPU上,这通常意味着所需要的数据基本都在内存中。
一个常识是,如果所需要的BLOCK在内存中,那么 CPU每秒可以处理10万甚至几十万的逻辑读!
但具体到这条SQL, 10的小时的CPU时间,处理的逻辑读,才有45万!
45万的逻辑读刚好对应4G的大小,即两张表的大小之和。
目前确实有一些奇怪的地方,小y接下来需要:
可惜的是,由于AWR报告只保留7天,因此未能获取到原来的执行时间的分解的情况,也就没有办法做正常和异常时刻的比对。接下来,这是一个SELECT语句,可以直接跑起来重现问题,这样小y可以观察到更多的线索!
将这条SQL语句重新跑起来,然后开启其他窗口观察,一开始的1分钟内还算正常,在不断的读取小表和大表,IO差不多到30M,然后IO就急剧的下降了,这个时候等待事件是ON CPU。
小y立马查看了SQL的执行进度,v$session_longops中表SMALL_TABLE已经扫描完成,但另外一张表BIG_TABLE全表扫描的进度进本停留在82%的位置!如下图所示。
接下来,读者朋友们,可以停一下,把上述现象总结一下,再思考个几分钟、
如果是您来接这个CASE,你会怎么继续往下查呢?
既然SQL执行是在CPU上,那么就不会有什么等待事件的线索留出来,既然在CPU上,那么必然要去看call stack,这是小y多年养成的习惯了。
通过oradebug short_stack,间隔几秒抓取了三次。如下图所示:
在出门抽烟的这一根烟的功夫里,小y不断思考着几个问题。
下图的这个函数qerhjWalkHashBucket,将所有问题都彻底解释清楚了! qerhjWalkHashBucket就表示在做hash join的过程中需要遍历hash bucket中的数据。
ORACLE内部的执行过程,可以简化为
HASH的目的是为了打算数据到各个桶中。每个算法都有优缺点。
那么HASH JOIN有什么缺点呢?
我们是否命中了该缺点呢?!
很显然,当其中一个桶里 (bucket)的数据很多的时候,那么一个值到该桶里比较起来就很需要遍历很多的数据,逐条比对了!那么一个值的比对就需要很久了!
一个BLOCK可以存储几十到几百条记录,一条记录需要到一个记录很多的桶里去比较很久,自然就出现了每个小时只能处理几千个逻辑读的情况了!
同时,比较的过程是消耗CPU的 (当驱动表读进PGA里后就在PGA内存中了)
那么为什么以前不出呢?那是因为以前驱动表的关联字段的数据分布是均匀的!而自从某一天以后,表关联字段的分布开始不均匀了!
知道原因了,那么解决方案就多种多样了?
hash join不适合驱动表表关联字段分布不均匀的情况,因此解决方案有多种
1) 采用use_merge的hint而非use_hash,无法修改程序的情况可以通过sql profile指定执行计划
2) 对驱动表small_table.id=0的数据进行调查和处理,为什么会在某一天突然出现大量id=0的数据,是否可以删除
……
可以看到:
如果觉得我的文章对您有用,请点赞。您的支持将鼓励我继续创作!
赞3
添加新评论0 条评论