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

性能优化可以从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. 敢于创新是性能优化的精髓。。

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2018-01-19

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏美团技术团队

sysbench在美团点评中的应用

如何快速入门数据库?以我个人经验来看,数据库功能和性能测试是一条不错的捷径。当然从公司层面,数据库测试还有更多实用的功能。这方面,美团点评使用的是知名工具sys...

3184
来自专栏大数据和云计算技术

腾讯实时分析平台Hermes介绍

腾讯大数据最近做了几件事,上线了一个官方网站http://data.qq.com/,将TDW(腾讯大数据库仓库)开源了,封闭的企鹅难得开放了一回。大数据网站上有...

49610
来自专栏蓝天

理解Load Average做好压力测试

SIP的第四期结束了,因为控制策略的丰富,早先的的压力测试结果已经无法反映在高并发和高压力下SIP的运行状况,因此需要重新作压力测试。跟在测试人员后面做了快一周...

552
来自专栏大数据

Solr:不止于文字

Solr于2004年首次创建时,打算成为OpenSource文本搜索引擎,为企业网站和内部文档搜索等用途提供类似Google的搜索功能。 基于Lucene搜索库...

2270
来自专栏企鹅号快讯

像Apache Storm一样简单的分布式图计算

作者:Kobi Hikri 翻译:无阻我飞扬 摘要:本文从计算机领域的“祖师爷”艾伦·图灵提出的图灵机概念开始,介绍了图形计算的概念,并以示例介绍了apache...

17110
来自专栏大数据和云计算技术

新数仓系列:HBase关键能力和特性梳理

最近看一本书,铃木敏文的《零售的哲学》,里面提到一个很有意思的观点,711核心使命是提供便利,围绕便利场景,提供一系列食品、ATM服务等,而不是和超市去PK货物...

3154
来自专栏量化投资与机器学习

战斗民族开源神器ClickHouse:一款适合于构建量化回测研究系统的高性能列式数据库(一)

编辑部原创 编译:wally21st、 西西 未经允许,不得转载 对于一些私募、投资机构和个人来说,量化投资研究、回测离不开数据的支持。当数据量达到一定数量,如...

3738
来自专栏编程札记

MongoDB替换MySQL?

早在上个世纪就有什么面向对象数据库等非关系型数据库扬言要替代关系型数据库,但是到现在,那些数据库都默默无闻,关系型数据库却依旧是应用最广泛的数据库,为什么?

3455
来自专栏张善友的专栏

PostgreSQL 与 MySQL 相比,优势何在?

一、 PostgreSQL 的稳定性极强, Innodb 等引擎在崩溃、断电之类的灾难场景下抗打击能力有了长足进步,然而很多 MySQL 用户都遇到过Serve...

2376
来自专栏Golang语言社区

为什么go语言适合开发网游服务器端

从网游的角度看: 要成功的运营一款网游,很大程度上依赖于玩家自发形成的社区。只有玩家自发形成一个稳定的生态系统,游戏才能持续下去,避免鬼城的出现。而这就需要多次...

3207

扫码关注云+社区