|
|
|
|
移动端

深入解析和定制Oracle优化工具

首先不会Oracle的我觉得也可以听懂。哈哈,因为我不会专门讲oracle里的太细的东西。这部分的内容比较通用,可以借鉴思路。

作者:r15笔记第16天来源:杨建荣的学习笔记|2018-01-31 18:32

技术沙龙 | 邀您于8月25日与国美/AWS/转转三位专家共同探讨小程序电商实战

首先不会Oracle的我觉得也可以听懂。哈哈,因为我不会专门讲oracle里的太细的东西。这部分的内容比较通用,可以借鉴思路。

我会在我的平台里面糅合这些思想,总之有货有料之后,加上时间和精力,就好比阳光空气水。

ppt有一部分是我在InfoQ的一次大会上做的一个简单的分享,今天在原来的ppt基础上重新做了一番解读。

这是我眼中的一些问题,有些Oracle已经做好了,对于一个成熟的商业软件来说,尽管功能上满足了,还是有些地方值得改进,或者说他们做得还不够好的地方。

这也体现了处理问题的几个阶段,有些人头疼止疼,有些人能够提前发现问题,有些人可以更早的规避问题,如果从这个境界来说,越到高的境界其实会比较尴尬,因为问题完全没发生就扼杀在摇篮里了,很难体现出价值,会比较尴尬。

有一个故事是关于扁鹊的,我没有求证出处,但是能够说明问题。

用扁鹊的话来说就是:"我大哥的医术之高,可以防患于未然,一个人的病未起之时,他一望气色便知,然后用药将其调理好,所以天下人都以为他不会治病,他便一点名气都没有。我二哥的能耐,是能治病初起之时,防止别人酿成大病。病人刚开始感冒咳嗽时,他就用药将人治好了,所以我二哥的名气仅止于乡里,被人认为是治小病的医生我呢,就因为医术最差。所以一定要等到这个人病入膏肓、奄奄一息,然后下虎狼之药,起死回生。这样,全世界便都以为我是神医。想想看,像我大哥这样治病,人的元气丝毫不伤,我二哥治病,这个人元气稍有破损就补回来了,像我这么治病呢,命是救回来了,可元气大伤,您说,我们家谁医术最高明?

所以对于运维来说,说句私心话,有些时候甚至希望有些问题让他发生,才能被大家重视,有些人可能对此有很深的体会。本意上我希望大家能够多碰到一些问题,多解决一些问题,三观肯定是正的。

说了这么多问题,我们来看看Oracle优化工具定制和这个有什么关系。

我们先来看看Oracle的优化工具,如果你没听过其实也没关系,你可以这样设想一个场景,有一个数据库,cpu负载突然在凌晨的一个时间点升高,造成了业务的阻塞,引发了一系列的问题。如果你是个DBA,该如何思考和处理。

假设你早上10点到公司,而问题发生在凌晨2点,这个问题该如何诊断和分析。

因为对于数据库来说,那个故障状态已经过去了,如何捕捉那个时间点的问题呢,这个词语在早些年会被经常提到,那就是诊断。

我们要分析这个问题,如果在oracle9i的版本中,那简直就是噩梦。我知道早期的阿里DBA里被这种问题搞得很痛苦。如果凌晨2点出问题,怎么解决呢。那就是2点的时候守在电脑前面。

有的同学说,这种方式实在太low了。oracle有statspack啊。有这个工具不假,但是问题很可能被平均化,比如数据库的负载在一个小时内分别是1%,100%,如果平均下来就是50%,问题被平均化,那会屏蔽掉太多的问题,屏蔽不意味着解决。

所以,我说的最基础的工具就是AWR,能够监控数据库的整体负载。周期可以在半个小时到1个小时都有。这样的缺点很明显,问题发生了一段时间之后你才能发现属于后知后觉。

在这个基础上改进,oracle做了一个相当大的改进,也就是远超MySQL在这方面的一个工具,ASH.

ASH简直被称为神器,它会在后台收集信息,频率是多少,1秒。所以我们诊断问题可以细化到秒级。这个对性能影响大吗,肯定有,但是非常非常低。

ASH有个缺点就是没法和一些详细的信息关联起来,因为它只抓取的是活跃会话,有些信息是没有的。所以不能说它是万金油,但是换一个角度来看,引起的问题的大部分都是活跃会话。所以这个覆盖面基本足够了。

说完了AWR,ASH,来看看ADDM.

大家都知道Oracle的现在版本是12c,12cr1在大概6年前就发布了,12cr2是被DBA公认为稳定的版本,这个版本大家足足等了6年左右。这对Oracle和很多DBA来说是很难描述的一种窘态。

所以Oracle肯定也意识到了这个问题,这方面我猜是和SQL Server的玩法类似了,那就是一年一个版本,这样就会弱化大家对版本的敏感性,所以今年会推出18c,也就是说到的自治数据库,接下来还会有19c,20c(这个是真有)

有的同学说Oracle都自动优化了,是不是没Oracle DBA什么事儿了,其实不然,too simple.

Oracle不可能一下子推出一个本来没有的东西,12c有了cdb,相当于在一个30多年的建筑上动了地基,搞出一个自治数据库,难道改动很大,其实不然,如果我们仔细看会发现,其实这些都会成为自治数据库的一些关键组件。而这些Oracle已经有了一些自动化解决方案。

所以自动化诊断(ADDM),SQL自动优化建议(SQL Tuning,SQL Monitor,SQL profile)都是迭代完成的,引入了更加动态的处理方式不断完善而已。

尽管如此,这些优化工具在我看来还是半成品,因为使用起来还是不太爽。所以我就想办法来做一些改进,哪怕原来步骤需要手工操作3次,简化到2次,我认为也是优化。

这里需要大家注意的就是定制的时候,需要明确一把标尺,那就是你解决一个问题,解决的问题更多,还是带来的问题更多。做一件事情,能够做到思路共用,我觉得你做Oracle还是MySQL,还是其他数据库,都会有很大帮助的。

让我们来看看生成一个AWR报告的步骤,就好比大家去医院,先抽血(这里可以叫做采样),生成验血报告(AWR报告),然后大夫看哪个指标高了,哪个指标低了,很类似的。

说到这里,要提到一个人,那就是张晓明,他还真是个大夫,后来转oracle了。

生成一个awr报告的步骤如下:

我列了五个步骤,那就意味着五个步骤都需要人工操作介入。可以想象你有50个数据库,那会是多痛苦,这种感觉就好比你是一个班主任,一个班的孩子,你没法一个一个的去聊天谈话,我们只是需要把握一个整体的状态,然后更多帮大家解决问题(有性能故障的数据库)

我原来处理性能问题,每天要生成大量的报告,最后受不了了。要搞明白这个优化的定制,就得明白它的实现原理,我决定改进一下。

AWR的脚本调用关系如下,其实明白了调用关系,我们就可以有针对性的看看哪些地方可以改进了。

awrinput.sql是负责输入参数,awrinpunm是负责参数的名字,打问号的地方是关键,他的实现就是下面列到的一个包dbms_workload_repository

所以到了这里我们看看AWR的实现原理,有了这个图,ADDM,AWR,ASH都能看个大概了。

简单来说,就是Oracle从内存级别去抓取一些数据库的变化,然后通过MMON后台进程来协作,把数据写入快照,快照级别的性能差异就是AWR报告,而ADDM则是对快照级别的数据库进行分析,ASH略有不同。

明白了这些,开始干活吧,我们明确定制的方向。不是一口气吃个大胖子,也不是写出一个惊天的大作,能用能满足需求就行.

然后问题来了,我们定制AWR的时候其实还有些事情要做,我们快速得到AWR的意义是什么,为什么要看这个AWR报告。这个问题要想明白,比你忙里忙活定制好多了。

主要是因为这个,DB time,我认为它是看AWR最重要的一个指标,没有之一,如果没有这个参考,其他的数据库都失去了意义。

如果想看个正规的解读,可以看看这个解释,不懂也没关系,略过。

我们的方向明确了之后,定制就很容易了,我们定制输入的参数就可以了,这些完全可以预先生成。比如你得到了这样一个列表,你可以很清晰的看到哪个时间点的性能高了,我不用生成所有的快照报告,所以我后面达到的状态就是上班瞅一眼db time的值,如果高了就生成awr报告,否则就不用太关注了,该干嘛干嘛。

脚本其实很简单,明确了痛点,脚本也很短,其实需要就会转化为,DB time高不高,如果高生成awr报告。否则不生成。

脚本可以从这里下载,https://github.com/jeanron100/dbm_lite

另外提一下awr format,如果一个DBA前端开发能力很强悍,那战斗力是很惊人的,awr format就是一个DBA开发的,能够把awr报告做进一层提炼。

awr的部分其实花了80%的笔墨,如果定制ash,addm就很自然了,就跟大家去驾校考驾照,前面8节课都是练感觉,后面两节课很快就能学会。

ash的定制也是类似的思路,比awr还要简单一些。输入两个时间戳即可。

定制ADDM需要一些pl/sql的基础知识,它会在pl/sql里调用几个流程来创建优化任务,生成优化报告。我们还是动态绑定几个参数即可搞定。

补充下ASH的原理图。这个对大家理解ASH很有帮助。

这个数据是分为两份,一份是落盘,一份是在缓存里。所以一个数据库如果出现宕机,那很可能缓存级别的ASH是会丢失的,因为没有落盘。

这类问题是比较难查的。这方面需要我们在监控的细节上做更多的工作。

SQL优化的部分内容就更多了,说起来都是辛酸泪,限于时间,就先分享到这里吧。

我来继续补充一下SQL定制的一些尝试,算是抛砖引玉。

如果细分,可以分为这四类,怎么理解呢,ADDM里面会对潜在的SQL问题进行分析,是基于快照级别的。

他只会分析告诉你某个SQL执行花费了较多的时间,可能有问题,但是不能告诉你具体该怎么优化。

而SQL Tuning算是一个这方面的专家,他会告诉你哪个SQL有潜在问题,该加索引了,该调整统计信息了等等。

但是目前诟病比较多的就是这个SQL Tuning Advisor,因为根据我们的实践,绝大多数情况下,他给出的分析都不是很靠谱,我这么说可能oracle不乐意了。

为什么呢,因为有些表设计是基于业务的,我也知道加一个索引,对这个SQL会有帮助,但是其他的SQL,从设计角度来说,这个工具没法做到下钻,如果能做到,那么DBA的岗位就岌岌可危了。

所以我比较喜欢的工作方式就是,如果有性能问题,对某个SQL优化没有思路的时候,看看oracle怎么建议。

虽然绝大多数情况下我不会采用它的建议,但是有总有那么几次它给的建议是我没意识到的。所以这就是工具的好处,完全靠经验,还是会有疏漏,多年前的攻略到了如今,到已经集成到产品里面了,老DBA的日子其实不好过了。以前的RBO时代,SQL优化真是酸爽,DBA说啥就是啥。

再来看看SQL profile,如果你的优化经历中没有SQL Profile的经验,这个是要减分的。这么说绝对不是唬人,或者自立flag.

这种场景是DBA的价值被严重高估的时候,一般碰到这类问题的时候都是火烧眉毛。

改应用代码,根本不可能,甚至说能改,重新部署要重启服务,那肯定不靠谱,所以不改代码,不改SQL,不加索引,而且能优化SQL,这个操作会给你的职业生涯大大加分。

我的职业生涯中碰到过多次,大多数情况下是很拉风的,有两次比较尴尬,第一次是有个SQL优化后,一年后问题爆发出来,简单理解就是这个表原来是10万的数据,用绑定的执行计划效果很好,但是一年后数据量是1000万,那原来的执行计划就不行了,如果弄个并发,搞点负载上来,这个问题就会被放大。所以说SQL profile处理的正确姿势就是做为临时解决方案可行,但是绝对不建议做为永久的解决方案,如果一个数据库里有大量的执行计划绑定,就好比打了n多的补丁。就别说优雅了,看起来很简陋。

另外一个尴尬的情况就是优化过度,这个怎么理解呢,我给开发优化SQL达到了高效处理,反馈必达,基本他告诉我应用有卡顿的时候,我不到5分钟就优化好了,给应用同学造成的假象就是这个活很easy啊。开发的主管找我说,让我给开个权限,他们自己优化得了。实际情况是,还不成熟。

但是退一步来说,这种事情最终还是要被替代的,你不改进,那就oracle改进,这不18c来了,这部分肯定会有改动和优化。

简单举个例子。

如果某一个SQL执行计划很好,消耗很低,但是执行的时候效率很差,一定是哪里出了问题。

这就好比一个人简历看起来亮亮堂堂,但是工作能力一般。问题的瓶颈在那里呢,怎么下钻呢,从hr的角度来说,招人的工作完成了,但是用人部门来说,带来的影响是很大的。所以要找到这个瓶颈点,我们就需要做信息下钻。根据真实的执行情况来得到整个SQL的执行计划情况。

如上图所示,可明显看到做索引扫描的时候,估算是2000多条记录,但是实际上4G的记录,这可以分为几个分支来考虑,比如索引使用不当,统计信息不合理,查询条件不合理等等。逐个去排查下钻,很快就能定位到问题。

我知道一些高手做优化是反着来的,就是先看执行计划,然后反推SQL是什么,如果能达到这个水平,说明你是在和优化器在一起赛跑了。

要得到一个SQL的html报告,还是很简单的,直接调用这个包就好。

SQL monitor用好了,你的职业幸福度会大大提升,当然我花了不少时间琢磨这个东西,可以看看之前的总结。http://dbaplus.cn/news-10-705-1.html

SQL Monitor的报告分为几种级别,比如简化的文本,略有紫色的html,还有丰富的active版本,就好比小米手机一样,标准版,高配版,尊享版

说了SQL Monitor的优点,我更希望说说他的缺点

缺点也很明显,到目前为止,这部分都有待改进,尽管报告的内容丰富了很多,但是如果我说几天之前的一次性能问题,我想看看SQL monitor的报告,抱歉这个拿不到,勉强能实现需求的就是AWR中抽取的一个SQL报告。

所以这一点mysql做的好一些,有慢日志,都记下来了。oracle也有慢日志类似的实现,oracle里面,有些业务可能3秒都不是事儿。所以SQL Monitor默认的是5秒,当然这个可以调的。

我是个比较喜欢折腾的人,所以我就准备做一些改进,我的改进比慢日志优雅一些,那就是周期性扫描,如果发现SQL性能问题,就把对应的SQL生成一个SQL monitor报告,如果反复存在就生成一次。这个细节上其实还可以优化。

后期实现的一个目标如下。生成了大量的sql报告,我真是快到到上班的时候喝喝茶,看看报告的地步了。

有问题的时候就把SQL优化好,然后发给开发确认下,所以这样我和开发接下来战斗友谊,因为他们的问题基本上都脱不出我的眼睛。

sql profile的地方再啰嗦一句

如果你要高效的优化,sqlt是需要会用的,这是oracle coe部门在用的

简单展望一下,我觉得多年前的这个展望如今依旧适用。有的同学说我没有阿里那样的体量,优化工作难有动力,没有困难,你要给自己创造困难,我这里说的不是你把数据库停了,制造故障。

而是你做精细化运维,如果你把一个关键业务的系统优化到了极致,什么时候性能高,什么时候性能低,高是什么原因,都能摸得熟,那你已经在一个至高点了。

所以在这方面我完全不用羡慕那些高大上公司的兄弟,至少在oracle的体量上,堆不了太大的规模,主要是做关键业务,集中化管理。和MySQL管理的思路不大一样。

另外就是半自动化,现在自动化提的太多,有些关键的地方一定要做到半自动化,不怕慢,就怕错。这个地方一定要慎重。

我是不希望我的主库被随意改动,哪怕它的设置确实不合理,奇葩,如果在维护时间里,我可以把它掰正,但是除此之外主库就是主库。

对大家的优化来说,这只是开始,现在的时代给大家的挑战很大,不要轻视这些挑战,不要莫名的排斥,现在的发展变化已经远超出了我的预期,所所以我能想到更多的挑战和可能出现的很多问题,温水煮青蛙的故事我不希望在我们的身边发生。

我的分享就到这里,感谢大家。

【编辑推荐】

  1. 如何在数据库中查找和消除重复的数据?
  2. 关系型数据库RDBMS的旧与新 -- 谈谈NewSQL
  3. 数据库的七种武器,快来看看!
  4. MySQL数据库开发常见问题及优化
  5. 一文读懂大数据时代的结构化存储数据库——HBase
【责任编辑:庞桂玉 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

读 书 +更多

网络管理员考试全真模拟试题与解析

本书是按照全国计算机技术与软件专业技术资格(水平)考试《网络管理员考试大纲》的要求,参照《网络管理员教程》及近年来考试试题编写的。...

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊