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

首先不会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管理的思路不大一样。

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

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

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

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