劲爆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 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

一个跨平台数据迁移的方案优化

如果有一套环境,业务优先级很高,服务器的服役时间比我工作时间都长,现在需要迁移到X86平台,而且经过评估,如果能够升级数据库的软件版本,可以使用到更多的特...

3074
来自专栏java架构学习交流

Java web轻量级开发面试教程读书笔记:数据库方面,如何准备面试

如果在面试或与资深人事交流的过程中,你能有效合理地展示出本章所给出的一些知识点,那么对你的评价就会是“对数据库有深入了解”,甚至能加上“有设计数据表的经验”,即...

2508
来自专栏沃趣科技

Oracle ASM神书《拨云见日 解密Oracle ASM内核》出版了

很高兴《拨云见日,解密Oracle ASM内核》(点击链接可申领书籍哦~)一书终于和大家见面了,我是这本书的组织者和主要译者之一,同时也负责了所有文章的技术审校...

1.3K51
来自专栏开源项目

码云周刊 | 面试之前,或许该高效率地学点干货!

一周热门资讯回顾 1、程序员多大年纪算高龄,届时该何去何从? ? 随着年龄的增长,程序员会相对难以保持技能更新。许多人宁愿留在自己的舒适区,不冒任何风险。即使...

2826
来自专栏牛客网

运维工程师养成实录:从确立目标到收获offer

作为一名渣硕,找工作陆陆续续从今年的三月份开始断断续续的刷一些题,看基础到八月份的猛攻阶段,到此,算是得到了一个不错的收获,也拿到了几个offer,算是对自己的...

81713
来自专栏Hongten

oracle系列--第一篇 数据库基础

1.1 数据管理概述 1.1.1 什么是数据管理 与我们人类相比,计算机的最大优势就是能够高速、精准地运行,其运行的过程就是执行程序代码和操作指令、处理数据...

932
来自专栏游戏杂谈

命名函数表达式探秘

本想直接转载过来的,发现效果不太好。而且想起之前好像看过中文,感谢随之漫笔的翻译,它为前端开发作出了不少贡献,很感谢这样的译者。

1022
来自专栏数说工作室

你的每一次点击行为,是如何变成数据的?| 聊一聊互联网公司的内部数据采集

数据是怎么来的? 在很多行业,数据都是人工收集来的,比如医学疾病数据、环境数据、经济数据等。数据的更新周期也比较长,比如年度、月度。 但互联网行业不一样,这个...

5607
来自专栏数据和云

Oracle LiveSQL在线SQL学习

在OOW前夕,我才注意到Oracle在10月14日发布的网站:livesql.oracle.com ,这是由Oracle的SQL专家Steven Feuerst...

3744
来自专栏全华班

一套标准的办公自动化解决方案(简要版)

? 解决什么问题 通过解决方案来实现: 1、通过畅通、高速的网络平台,先进的应用系统,打破企业部门之间的信息壁垒,实现整个公司信息和知识的共享; 2、 提高办...

3549

扫码关注云+社区