风驰电掣：有效缩短SQL优化过程三步走！

2017 年 7 月 13 日 DBAplus社群 梁敬彬

作者介绍

梁敬彬，福富研究院副理事长、公司唯一四星级内训师，国内一线知名数据库专家，在数据库优化和培训领域有着丰富的经验。多次应邀担任国内外数据库大会的演讲嘉宾，在业界有着广泛的影响力。著有多本畅销书籍，代表作有《收获，不止Oracle》。

学习完第一章内容的小王，知道了解决问题要先整体再局部，从此不再盲目解决问题了。关于整体性方面，他不仅知道了五大性能报告的具体用途，还明白了如何获取到这些报告，甚至还知道如何解读这些报告。如今的他会对着获取到的这几张报告眉头紧锁，认真研读，自言自语，不住点头，指点江山，激扬文字……仿佛一位住在地下室的青年，正在思考国家下一步该怎么走？如何突破美国封锁？如何收复台湾？如何保住南沙钓鱼岛……

这个结果似乎让我们有些热血澎湃，不过提升效率的重点内容会出现在本章，能否从地下室搬出来，就在此一举了。

故事中有一个细节，便是小王光发现问题就整整花了1个小时，这时间可不短啊。如果系统故障要花整整1小时才能找到，那这1小时的时间对客户来说，是多么难熬的1小时啊。

而且，更可悲的是，这1小时小王并没有解决问题，故障依旧。

有人问，为啥会花费1小时的时间呢？嗯，本章就开始探讨这个问题。首先说明调优时间都花在哪儿，接下来分析如何缩短，并结合案例来巩固知识，最后大家思考回顾。

总体学习思路如下图所示：

一、SQL调优时间都去哪儿了

我们先来分析调优时间都去哪儿了。1小时才能得到解决方案固然让人接受不了，事实其实更糟糕，小王是只花费1小时吗？显然不止1小时！

他到下午还在解决这个故障，甚至第2天他还在解决这个问题，这已经1天了。小王只花了1天时间吗？显然不止1天！

实际情况是小王最后崩溃了无法解决问题。显然这是无限期！

面对这么凄惨的事情，我这里总结出几点经验，来分析这个问题。

1、不善于批处理频频忙交互

故事中没有细说小王和求助者之间的交互细节，但是从花了整整1小时才发现需要加索引来看，小王的工作效率肯定不高。我们可以猜测他发生了什么事。也许小王本身无法访问这个系统，需要通过对方执行自己的脚本来反馈结果。于是他想了解这个语句的返回记录的量，又想要了解这个语句的执行计划，又想了解这个语句对应的表和索引的信息……电话、QQ、邮件不断地交互，于是1小时就这么过去了。就像下图所示这样：

2、无法抓住主要矛盾瞎折腾

小王没有从整体出发来考虑问题，没有想明白是整体问题还是局部问题，一路抓瞎，无端耗费时间却徒劳无功。

3、未能明确需求目标白费劲

有的SQL其实执行得虽然慢，但是客户并不是很在意，这样的SQL除非是耗尽资源影响到全系统，否则不见得就需要立即优化。

不过这是我提出的一个重要意识，在小王的案例里，这个问题倒是不明显，因为这个SQL客户已经明显感觉慢，提出要优化了。

4、没有分析操作难度乱调优

其实有一个意识非常重要，SQL好调优吗，调优空间大吗？小王遇到这个SQL的时候，其实应该首先知道这个SQL返回记录有多少，如果很少，就说明调优空间很大。反之就要考虑特殊手段了。在小王的案例中，小王根本没有这个意识。

二、如何缩短SQL调优时间

1、先获取有助调优的数据库整体信息

如何缩短SQL调优时间呢？我觉得要先把你进行SQL优化的思路理顺。当你要优化SQL时，你的一般流程是什么？

首先要知道整个数据库的运行情况，我们上一章中介绍过数据库AWR报告等调优工具，不过介绍得并不全面，因为AWR报告等是在数据库出问题时的利器。可是如果数据库当前没有出问题呢？其实不见得，很多时候系统没问题是因为你没触发这个问题，其实是有问题的。比如某表的索引失效了，某SQL访问该列时一定只能走全表扫描；比如某表的属性被设置了并行度，这意味着所有扫描该表的SQL都会并行执行，这可能会产生严重的资源争用从而让系统瘫痪；比如你的全局临时表被收集了统计信息，访问该表的SQL就可能会出现错误的执行计划等。

不过你的AWR报告却可能发现不了这些问题，比如该时段和这些对象相关联的SQL根本就没有执行。没发现问题并不代表没有问题！

因此我们需要获取所有可能有问题的对象，同时也需要一键获取所有的相关时段的AWR等数据库整体性能报告，获取数据库的整体信息。假如这些信息能一键快速获取，那解决问题的效率肯定会高很多。

特别提醒：

这里再次强调获取的相关时间段问题，我们自动获取AWR等几大性能报告是需要选择时间段的。那么如何选择呢，能高效全自动判断吗？其实动动脑子就可以想到，我们可以在数据库里自动判断哪些时段的资源消耗最大，然后直接就提取该时段的性能报告，甚至可以取前几名时段，然后我们去自动提取，这不行吗？

2、快速获取SQL运行台前信息

接下来，在获取到数据库整体信息后，调优的方向就非常明确了，对具体的SQL进行调优。执行计划是SQL调优的重要武器，通过分析SQL计划，我们可以判断SQL的访问路径是否高效，从而进行调整优化。关于执行计划的获取手段有6种之多，这是为啥呢？各有什么区别？答案依然展示在后面的章节中。

还需要将执行计划和运行时的统计信息结合在一起分析，这样才会更准确。比如SQL产生了多少逻辑读，多少物理读，是否有排序，是否有递归调用，等等。具体细节依然见后续章节。

3、快速拿到SQL关联幕后信息

当获取到SQL的执行计划后，诸多的确认都和该SQL对应的表和索引有关。比如当我们怀疑驱动表的顺序有错时，我们就会去看看这些表的实际大小和对应的统计信息是否准确；我们也关心表的类型是什么，比如是否是分区表，在哪个列有分区，分区的类型是什么，等等。

除了关注表的信息，我们也很关心索引的信息。比如看到执行计划中非常适合走索引的查询走了全表扫描，我们就会去看看是否该列无索引，如果发现有，就看看此列索引是否失效了。

一般我们也会关心索引的类型是什么，是Btree索引还是位图索引还是函数索引；是单列索引还是组合索引，如果是组合索引，哪列在前；如果索引建在分区表上，我们还关心是全局索引还是局部索引等。

总之，我们希望能一目了然地掌握该SQL涉及的所有表和列的相关信息，最好一键就展现在我们面前。这样，解决问题就非常高效了！

大家可能已经猜到了，我们又要来一个一键获取了，这次获取的是SQL对应表和索引的相关信息，没错，你猜对了。干货脚本可通过以下链接获取：

https://github.com/liangjingbin99/shouhuo/tree/master/%E7%AC%AC02%E7%AB%A0

总体情况，详见下图：

说了这么多，你是不是有些跃跃欲试了，可能特别想知道到底该如何一键获取SQL整体信息和SQL的相关信息吧。很显然，这样效率一定能提升很多！好吧，下面就跟随我一起看看吧。

三、从案例看快速SQL调优

1、获取数据库整体的运行情况

步骤1（构造环境，对当前数据库进行各种操作）

sqlplus "/ as sysdba" @d:\mkdb.sql

步骤2（运行脚本，对当前数据库进行整体提取）

sqlplus "/ as sysdba" @spooldb.sql

步骤3（输出对应日志文件，以供后续进行分析）

输出如下5个文件，其中ADDM是最近一小时文件，ash是最近半小时文件，而AWR文件是最近一小时和最近7天的两个文件，spool打头的文件输出数据库所有的相关信息。有了这5个文件，基本上数据库的情况可以了解得比较清晰。具体文件如下图所示：

具体这5个文件我们就不一一打开给大家看了，否则篇幅显得过大。其中spool打头的这个文件基本涵盖了你想获取的所有相关数据库信息。如：数据库版本、数据库参数、主机参数、异常的表和索引信息（表分区有无异常、并行度问题、索引是否失效、是否过大、是否为分区索引、索引类型、哪个列上有索引、索引过多、组合索引的组合列过多、全局临时表的异常信息收集情况，等等）、日志切换情况、序列情况、异常触发器、异常外键设置等。

如果要在书中展现，预计要有20多面，这里只告诉读者大致的内容。在线上会展现脚本，并进行视频演示，给读者看实际操作。

2、获取SQL的各种详细信息

步骤1（构造环境，执行部分效率低下的SQL）

sqlplus "/ as sysdba" @d:\mksql.sql

步骤2（对当前的性能低下的SQL进行收集）

步骤3（对当前的性能低下的SQL进行收集）

我们获取到了这个SQL的详细执行计划和对应的表及索引信息，从而大大提升了效率，如下图所示：

大家不要小看了这个一键获取SQL相关详细信息的威力。当你认为一条SQL有问题时，只要获取到这些信息，根本就不需要进行任何与现场人员的交互动作，因为你要的东西都已经被收集在一起了。让我们一起看看下面一个技术人员解决问题的一个思维片段。

这语句该如何优化，让我先瞧瞧这语句的执行计划是啥？

哦，原来执行计划就在这里，太详细了！

奇怪，这里的执行计划不对啊，应该是要用索引啊，怎么会没有，难道是统计信息不准确，还是说没建索引，还是说索引失效了？

咦，相关统计信息的收集是正常的，奇怪。我再看看，这列有索引吗？咦，是有，奇怪了。哦，索引失效了，原来如此。看来我重建一下索引问题应该可以解决……

看完这段话有何感触，如果没有高效的一键收集手段，你的所有信息都要在数据库中查询而获取，如果需要让他人给你信息，那就更麻烦了，小王1小时后才得出如何优化的事情，正是因为无法直接获取全部有效信息，不断交互导致的。

四、总结与延伸

1、场景再现

我们来假想一下学过这两章内容和拥有工具脚本的小王，是什么样的场景吧。

场景1：

一键获取数据库整体信息，发现整体主机资源不足，并定位到耗尽资源的外部应用程序，然后协调相关人员，将外部应用移走或者优化，问题迅速准确地得到解决。

场景2：

一键获取数据库整体信息，根据在第1章中学到的相关知识，发现是某些SQL需要优化，然后根据一键所得的SQL详细信息，判定SQL问题在哪里，问题迅速准确地得到解决。

场景3：

一键获取数据库整体信息，也获取到SQL相关详细信息，不过不知该如何下手优化，求助他人。由于提交的信息详细，无须交互迅速得到对方的建议帮助，问题迅速准确地得到解决。

2、脑洞大开

学到这里，大家已经明白笔者的用意了，后续请读者下载一下脚本（链接https://github.com/liangjingbin99/shouhuo/tree/master/%E7%AC%AC02%E7%AB%A0），体验一下高效的过程。其实完善是没有尽头的，这只是一个大概，更细节的实现会一直在公众号上更新，比如一键获取数据库整体信息中我就没有提到数据库告警日志、监听日志、主机和数据库是否需要打哪些补丁。比如获取的AWR和ASH等信息里，能否直接对这些报告的SQL进行超链接，进入到详细SQL信息中……有没有脑洞大开的感觉？

这两章如果展开讲，可以单独成书。这两章的知识落地到软件中，就是一个非常好的数据库高效分析诊断工具。是不是再次脑洞大开？

嗯，笔者会无偿地将自己的经验分享给大家，能帮到大家也是笔者最大的快乐！

相关专题：