从一条SQL看基于ORACLE的SQL优化_PART1

1.内容简介

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

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

  内容大纲如下：

2.主要内容

2.1 从一条巨慢的SQL的开始

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

1）了解SQL结构：SQL中使用了哪些语法，这些语法是不是经常会导致性能问题，比如标量子查询的滥用。

2）获取执行计划：执行计划反应了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相关内容，甚至还需从大局观进行把控：物理模型的设计以及对具体的业务分析。

2.2快速解读执行计划

2.2.1 快速解读执行计划要点

 SQL执行计划作为SQL优化的一把钥匙，必须要很好的利用起来。经常看到开发人员喜欢用PL/SQL DEVELOPER之类的工具来看执行计划，这里我得提醒下，这种内部调用的是EXPLAIN PLAN FOR，可能不够准确，特别是有绑定变量的情况下，最重要的一点，对于长的SQL执行计划，简直没法进行分析，个人还是喜欢文本类型的执行计划，特别是真实的执行计划，能获取A-ROWS,E-ROWS这些指标的执行计划，让我对执行计划中的问题一览无余，特别对于巨慢的SQL，也可以运行个几分钟中断后获取部分信息来协助判断。执行计划要点如下：
  找入口：通过最右最上最先执行原则找出执行计划入口操作。对于巨长执行计划COPY到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执行很慢，可能就是统计信息不准确了。

2.2.2快速解读执行计划实例

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

2.3 从执行计划看SQL低效根源

  主表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不准确问题。

  未完待续，参考PART2：