从一条巨慢SQL看基于Oracle的SQL优化（（重磅彩蛋+技术分享））

3405969869

腾科IT教育集团

本次分享的内容是基于Oracle的SQL优化，以一条巨慢的SQL为例，从快速解读SQL执行计划、如何从执行计划中找到SQL执行慢的Root Cause、统计信息与cardinality问题、探索性能杀手Filter操作、如何进行逻辑重写让SQL起飞等多个维度进行解析，最终优化巨慢SQL语句，希望能够抛砖引玉，和大家一起探讨SQL优化方法。

另外，还简单介绍了两种解决疑难SQL优化问题的工具：10053和SQLT，特别是SQLT，往往在无计可施过程中，可能建立奇功，建议大家抽空研究下SQLT工具。最后对本次分享进行总结和思考：分享SQL Tuning RoadMap以及SQL Tuning最佳实践的相关内容。

大纲如下：

这条巨慢SQL执行预计耗时12小时以上，返回百万行数据。首先我们接手一条SQL优化问题，至少需要做以下两件事：

了解SQL结构：SQL中使用了哪些语法，这些语法是不是经常会导致性能问题，比如标量子查询的滥用。获取执行计划：执行计划反应了SQL的执行路径，直接影响了SQL的执行效率。如何从执行计划中找出问题，是SQL Tuning的关键。

言归正传，先揭开巨慢SQL的神秘面纱：

这条语句其实就是查询DEALREC_ERR_201608表，有各种复杂的子查询，初看此子查询，我基本已经了解问题大概出在什么地方了，先卖个关子，看执行计划先：

这种执行计划拿到手，其实很容易找出问题：

（1）分析指标问题：Rows，也就是每步骤的cardinality很小，说明每步返回的结果行数很少。这点值得怀疑。

（2）由于cardinality很少导致了Operation走了一系列Nested Loops操作，我们知道，NL操作，一般是驱动表返回的结果行数很少，被驱动表走索引，返回的最终结果比较少（一般最多几千行），效率会很高。

以上两点值得注意：如果cardinality是准确的，那么这个执行计划中走一系列Nested Loops的部分应该没有多大问题，但是，如果cardinality不是准确的呢？那就是大问题。这也就是一些初级开发人员的思维一样，经常喜欢对数据的处理使用循环，如果循环的次数少那还好，如果循环次数很多，那就会很慢。循环操作完全依赖于循环的次数，从SQL执行计划里看，也就是依赖于驱动表返回的结果行数，很显然，这种不适合大量数据运算。

（3） 在ID=1中有个Filter，这个Filter的子操作是ID=15~18的全表扫描。Filter可是执行计划里的一个大问题，当然，这里的问题Filter必须有2个或2个以上子节点的操作，如果是单节点，那只是简单的过滤条件而已。

对于一般的SQL优化，必须得分析SQL的语法结构，语义以及解读SQL执行计划，以SQL执行计划为基准，分析执行计划中的问题，来进行SQL Tuning，基本能解决大部分SQL优化问题了。

当然，以我的理解，SQL优化不仅需要很强的逻辑思维、正确的理论指导、各种SQL语法的精通、熟悉index的使用、了解CBO相关内容，甚至还需从大局观进行把控：物理模型的设计以及对具体的业务分析。

SQL执行计划作为SQL优化的一把钥匙，必须要很好地利用起来。经常看到开发人员喜欢用PL/SQL Developer之类的工具来看执行计划。这里我得提醒下，这种内部调用的是Explain Plan For，可能不够准确，特别是有绑定变量的情况下，最重要的一点，对于长的SQL执行计划，简直没法进行分析。个人还是喜欢文本类型的执行计划，特别是真实的执行计划，能获取A-ROWS，E-ROWS这些指标的执行计划，让我对执行计划中的问题一览无余，特别对于巨慢的SQL，也可以运行个几分钟中断后获取部分信息来协助判断。

执行计划要点如下：

找入口：通过最右最上最先执行原则找出执行计划入口操作。对于巨长执行计划Copu到UE里使用光标缩进下探法则可找出入口，由于执行计划是锯齿状结构，父节点的子操作是向右缩进的，因此，从ID=0开始，光标向下向右缩进下探，直到缩进不了停止，然后按照同级别的，也就是格式的垂直线是同一级的，上面的是入口。

看关系：各操作之间的关系：Nesed Loops、HASH JOIN、Filter等是否准确，以及操作的顺序是否准确，直接关系此操作甚至影响整个SQL的执行效率。

理顺序：一步走错，满盘皆输。通过理清执行计划顺序找出key steps。

重操作：执行计划中的Operation和Predicate部分是需要关注的核心内容，从操作中看出不合理部分，以此建立正确索引等优化措施。

求真实：执行计划中指标是估算的，估算的指标和实际情况很可能不匹配。所以优化SQL需要了解每步骤真实的基数、真实执行时间和Buffer gets等，从而准确找出问题Root cause。（可以根据谓词手动计算、建议采用display_cursor方式获取A-ROWS、A-TIME等信息，工具有很多，也可以使用sql monitor等），如果采用Explain Plan For、SET AUTOTRACE之类的看执行计划，由于指标信息是不准的，要获取真实的信息，还需要手动根据谓词去计算，然后比较估算的和真实的差别，从而判断问题。

轻成本：COST虽然是CBO的核心内容，但因为执行计划中COST不一定准确反应SQL快慢，因此不要唯COST论，COST只是一个参考指标，当然可以通过执行计划判断一些COST是否明显存在问题，比如COST非常小，但是SQL执行很慢，可能就是统计信息不准确了。

以上执行计划入口是ID=6(全表扫描)，返回行数1，之后与ID=7的做Nested Loops操作。详细见分析部分。

问题：为什么要寻找执行计划入口？为什么要分析执行计划各步骤顺序和关系？

各种操作之间的关系是由cardinality等各种因素触发的，不正确的cardinality会导致本来应该走HASH JOIN的走了Nested loops Join。往往入口处就有问题，导致后续执行计划全部错误，所以明确各种步骤的关系，有助于找出影响问题的根源步骤。

理清执行计划顺序，有助于理解SQL内部的执行路径，通过执行的实际情况判断出不合理步骤操作。

重操作、求真实、轻成本是通过执行计划优化SQL的重要方法。

这里的入口是ID=6的全表扫描，返回行是1行，不是准确的，很显然，找到入口的问题，已经可以解决一部分问题了。

主表DEALREC_ERR_201608在ID=6查询条件中经查要返回2000w行，计划中估算只有1行，因此，会导致Nested Loops次数实际执行千万次，导致效率低下。应该走HASH JOIN，需要更新统计信息。另外ID=1是Filter，它的子节点是ID=2和ID=15、16、17、18，同样的ID 15-18也被驱动千万次。

找出问题根源后，逐步解决。

第一次分析：解决ID=6步骤估算的cardinality不准确问题。

找出入口操作ID=6,由于ID=6操作的cardinality估算为1导致后续走一系列Nested Loops影响效率。cardinality的计算与谓词紧密相关，所以要找出ID=6的谓词，根据谓词手动计算真实card与估算card之间的区别。尝试收集统计信息，检验效果。

现在的问题，也就是转为对表DEALREC_ERR_201608统计信息准确性的问题，特别是统计信息对谓词计算的准确性。

尝试更新统计信息：

发现使用size auto,size repeat,对other_class收集直方图均无效果，执行计划中对other_class的查询条件返回行估算还是1（实际返回2000w行）。如何解决？card的计算和谓词紧密相关，查看谓词：

substr(other_class, 1, 3) NOT IN (‘147’,‘151’, …)

怎么办？思绪万千，灵光乍现！

Hints:cardinality(a,20000000)，use_hash等可以。

还有更好的办法吗？

突然想起11g有个统计信息收集新特性：扩展统计信息收集。

exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>‘xxx',tabname=>‘DEALREC_ERR_201608',method_opt=>'for columns (substr(other_class, 1, 3)) size skewonly',estimate_percent=>10,no_invalidate=>false,cascade=>true,degree => 10);

扩展统计信息一收集，执行计划如下：

DEALREC_ERR_201608与B_DEALING_DONE_TYPE原来走NL的现在正确走HASH JOIN。Build table是小结果集，probe table是ERR表大结果集，正确。

但是ID=2与ID=11到14，也就是与TMI_NO_INFOS的OR子查询，还是FILTER,驱动数千万次子节点查询，下一步优化要解决的问题。

性能从12小时到2小时。到这里结束了吗？

统计信息的问题还是很多的，一个表的统计信息收集，特别是自动收集，不一定能让所有相关SQL找到最佳执行路径，特别是SQL条件复杂、数据倾斜、表类型定义不准确等情况，特别是使用了复杂条件，CBO无法准确计算对应谓词的card，或者类型定义不准确，本来是日期的用了VARCHAR2，内部全部要转为数字来计算选择性，很显然，乱定义列类型也是有问题的。所以有针对性地修正收集的统计信息，是很有必要的。

疑问1：100%收集为什么还没有走正确执行计划？

统计信息收集比例高不代表就可以翻译对应谓词的特征，而且统计信息内部有很多算法限制以及不完善的情况，比如11g的扩展统计信息来继续完善，12c也有很多统计信息完善的特性。所以并不是比例低就不好，比例高就好！统计信息的收集要满足核心SQL的执行效率，对于非核心SQL一定程度上可以不用过度关注，因为统计信息很难满足所有相关SQL的最佳执行。

疑问2：统计信息各种维度收集了包括直方图都收集了怎么不起作用？

直方图有很多限制，12c之前，只有频度直方图和等高直方图两种，对很多值的分布不能精确表示，所以有很多限制。因此，12c又增加了2种直方图：顶级频度直方图和混合直方图。另外直方图还有只存储前32位字符的限制。
  
疑问3：直方图只对走索引的有作用？

很显然不对，直方图只是反应数据的分布，数据的分布正确，对应谓词可以查询出比较准确的cardinality，从而影响执行计划，所以对全表也是有用的。

疑问4：收集或更新了统计信息，执行计划怎么变得更差了？

很有可能，比如把原来的直方图给去掉了可能导致执行计划变差。因此，一般更新使用size repeat，除非是确认需要修改某些直方图，另外谓词和统计信息紧密相关，某些谓词条件一旦收集统计信息，可能就计算不准确了。

疑问5：执行计划中cardinality显示的和已有统计信息计算不一致？

Oracle CBO内部算法很复杂，而且Bug众多，遇到问题要大胆怀疑。

疑问6：统计信息应该按照Oracle建议自动收集？

具体问题具体分析，是让Oracle自动还是自己写脚本收集，都需要长期实践总结，对于一个复杂系统来说采样比例和method_opt很多需要定制设置。

疑问7：为什么唯一性很好的列，还需要收集直方图？

选择性的内部计算是要转成数字的：CBO内部计算选择性会先将字符串转为RAW，然后RAW转为数字，左起ROUND15位。如果字符串的唯一性好， 但是计算成数字后唯一性不好，则会导致执行计划错误，这时候也需要收集直方图。

疑问8：我需要根据统计信息以及CBO公式去计算COST吗？

不需要，除非你很喜欢研究，这样做只会得不偿失。了解各种JOIN算法、查询转换特性、索引等效率和哪些有关即可，COST不是最需要关心的指标，我们应该关心SQL高效运行所需的执行路径和执行方法，是否可以达到及早过滤大量数据，JOIN方法和顺序是否正确，是否可以建立高效访问对象等。

为什么会形成Filter操作？（多子节点，单子节点纯粹过滤操作）

Filter形成于查询转换期间，如果对于子查询无法进行unnest转换来消除子查询，则会走Filter。走Filter说明子查询是受外表结果驱动，类似循环操作！很显然，如果驱动的次数越多，效率越低！

查询转换是能够生成高效SQL执行计划的重要步骤，查询转换不能做好，后面的很多执行路径就没法走了。掌握查询转换机制，对如何写高效的SQL，调优SQL至关重要，了解的越深，对CBO就越了解。

下面是CBO组件图，熟悉对应组件是SQL优化必须的内容：

[color=rgb(0,