劲爆ORACLE优化,你不必是专家

游东旭 2018-01-18
194


性能优化可以从PLAN开始,但是不能以PLAN结束。对于一些优化需求,我们可以看看执行计划,不过加HINT一般不是办法,我们可以从应用、业务找突破口,甚至可以把自己当外行,突破自己的定式思维,或许能有意想不到的收获。

曾经的案例


某单位一套核心系统,业务量还比较可以的,为了更好吸引用户,做过一次秒杀活动。秒杀活动还没有正式开始前,相关业务单位做了一次压力测试,评估一下活动对数据库服务的杀伤力。


不过,经过好多次压测,CPU都是100%的使用率,让他们有了危机感,一怕活动不能正常进行,二怕把库搞死了影响其他业务。于是这个性能优化有了我们的参与。

在系统出现问题时,系统的一些性能情况如下:


1.CPU使用率100%,居高不下,一点都没有空闲的感觉。

2.大量的latch:cache buffers chains等待事件。

3.其他业务也很卡,甚至影响登录。

下面是对这个案例的详细分析和解决。


问题分析


日常业务库平稳运行,突然告警CPU使用率100%,经过检查和沟通证实,是有部门做秒杀活动的模拟压力测试:


看看当时的AWR TOP EVENT信息



可以看到CBC等待非常严重。看这期间都在做什么查询呢:



前两位SQL都是秒杀活动核心的SQL,平均每分钟执行450次以上,但是这个不准确,因为模拟测试时是高并发状态,所以并发个数远大于450。这两个SQL都有个特点,执行时间都在16秒以上,对于生产系统的事务来说,这是不是长了些?然后开始找这两个SQL的优化空间。


相关SQL如下(这只是其中一个影响整体性能最为明显的SQL之一):



这SQL是绑定变量方式,我们取一个绑定值进行测试,执行时长和计划、逻辑读等信息如下:



发现实际执行更慢,需要1分钟50秒,逻辑读高达725371。这说明AWR信息确实只是平均情况,还是找找优化空间。


通过这SQL不难看出,smphonegood****view是一个视图,里面的数据表比较多,并且数据量不是很大,但是大部分都是ACCESSFULL,并且有几个表在这视图里出现3至4次,相当于查一次该视图,就会全查这某些表3到4次。从此可以看出,性能消耗主要在视图这儿。

优化方案


共经历了两次优化,第一次优化了20倍左右的性能,但是杀伤力之大,库还是承受不住。第二次完美收官。

初次优化


方案:让视图不要嵌套,加了一个HINT: no_merge(m)*/


再看执行情况:



执行耗时从1分50秒下降为1秒多点,逻辑读从720000下降为35016,优化效果极其明显,很兴奋很自信地把方案提交给应用了。


应用实施之后,再次进行压力测试,结果让人失望。看着CPU又100%,并且没有一点回撤的趋势。这优化效果都不可以?所以又进行了第二次的优化。


再次优化


看到前面那么好的优化效果都不行,得出绝招了。

 

经过思考后再出发,列出CPU 100%的问题点:


(1)多表被并发查太多次,并且全表扫;

(2)并发量大;

(3)问题还是在视图这儿;


分析视图看看:


1、 数据量

Select count(1) from SMPHONEGOOD****VIEW;

只有941条;


2、 组成

全是conf配置表,数据量都是50万以下,大部分表都是4至5万行!


方案一个大胆的猜想,如果把视图的数据放在一个TABLE里面,直接查只有941条数据的TABLE,这是不是会避开多个conf表的热快问题?


不管应用是否接受,先试验一下:



执行耗时只要4毫秒,逻辑读946,比上面优化方案的1秒46毫秒、30000多逻辑读,还要好很多很多倍,比飞机还快,但是把视图改为TABLE,意味着数据实时性会下降,如果某个CONF表更新了数据,不特意维护TABLE的数据,那会数据不精确,所以这是一个很有可能被否定的方案,重点要看视图相关的表数据是否会变化!!

 

在和应用业务人员沟通后,秒杀活动期间,视图相关的数据表不会变化,等于视图的ALL数据不会变化,都是一些配置表,更新频率很低,有时一个月都不会变化。所以,VIEW to TABLE的建议被接受了。


然后开始再次模拟压力测试,一切ok。


(备注:活动期间还是出现了CPU 100%的情况,不过只是极短暂的一会儿,马上又恢复正常,活动时的业务量远超压测预估。问题所在:压力测试还有点保守啊)。


性能观察


如下是秒杀核心SQL的性能变化展现:



经验总结


从这次方案可以总结出一点经验:


  1. HINT只是临时的纯技术解决方案,往往不是最好的优化。

  2. 敢于创新是性能优化的精髓。


,回复:prelection,你可以找到本文的相关视频文档。



相关阅读:

【演讲实录】RWP团队谈SQL优化

深入剖析-关于分页语句的性能优化

MySQL SQL优化之覆盖索引

从逻辑入手优化数据库性能

资源下载

关注微信:数据和云(OraNews)回复关键字获取

‘2017DTC’,2017DTC大会PPT

‘DBALIFE’,“DBA的一天”海报

‘DBA04’,DBA手记4经典篇章电子书

‘RACV1’, RAC系列课程视频及ppt

‘122ARCH’,Oracle 12.2体系结构图

‘2017OOW’,Oracle OpenWorld资料

‘PRELECTION’,大讲堂讲师课程资料

最后修改时间:2020-05-07 23:52:19
文章转载自游东旭,如果涉嫌侵权,请发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论