踩坑CBO，解决那些坑爹的SQL优化问题_PART1

1.CBO优化优化器存在哪些坑

先来看一下，CBO优化器的组件：

从上图可以看出，一条SQL进入ORACLE中，实际上经过解析会将各部分进行分离，每个分离的部分独立成为一个查询块(query blocks)，比如子查询会成为一个查询块，外部查询又是一个查询块，那么ORACLE优化器要做的工作就是各查询块内部走什么样的访问路径更好（走索引、全表、分区？），其次就是各查询块之间应该走什么样的JOIN方式以及JOIN顺序，最终计算出那种执行计划更好。

  优化器的核心就是查询转换器、成本估算器以及执行计划生成器。
  Transformer(查询转换器)：

  
  从图上可以看出，优化器的第一核心装置就是查询转换器，查询转换器的主要作用就是研究各种查询块之间的关系，并从语法上甚至语义上给予SQL等价重写，重写后的SQL更容易被核心装置成本估算器和执行计划生成器处理，从而利用统计信息生成最优执行计划。

  查询转换器在优化器中有两种方式：启发式查询转换(基于规则)和基于COST的查询转换。启发式查询转换的一般是比较简单的语句，基于成本的一般比较复杂，也就是说，符合基于规则的ORACLE不管什么情况下都会进行查询转换，不符合的ORACLE可能考虑基于成本的查询转换。启发式查询转换历史悠久，问题较少，一般查询转换过的效率比不经过查询转换的要高，而基于成本的查询转换，因其与CBO优化器紧密关联，在10G引入，内部非常复杂，所以BUG也比较多，在日常优化过程中，各种疑难SQL，往往就出现在查询转换失败中，因为查询转换一旦失败，ORACLE就不能将原始SQL转换成结构更良好的SQL（更易于被优化器处理），显然可选择的执行路径就要少很多，比如子查询不能UNNEST,那么，往往就是灾难的开始。其实，查询转换中ORACLE做的最多的就是将各种查询转换成JOIN方式，这样就可以利用各种高效的JOIN方法了，比如HASH JOIN。

 

  查询转换共有30种以上的方式，下面列出一些常见启发式查询转换和基于COST。

启发式查询转换（一系列的RULE）：

很多启发式查询转换在RBO情况下就已经存在。常见的有：

Simple View merge (简单视图合并)、SU (Subquery unnest 子查询展开)、OJPPD (old style Join predicate push-down 旧的连接谓词推入方式)、FPD (Filter push-down 过滤谓词推入)、OR Expansion (OR扩展)、OBYE（Order by Elimination 排序消除)、JE （Join Elimination 连接消除或连接中的表消除）、Transitive Predicate (谓词传递）等技术。

 

基于COST的查询转换（通过COST计算）：

针对复杂的语句进行基于COST的查询转换，常见的有：

CVM (Complex view Merging 复杂视图合并）、JPPD (Join predicate push-down 关联谓词推入）、DP (Distinct  placement）、GBP（Group by placement）等技术。

通过一系列查询转换技术，将原始SQL转为优化器更容易理解和分析的SQL，从而能够使用更多的谓词、连接条件等，达到获得最佳计划的目的。查询转换的过程，可以通过10053获取详细信息。查询转换是否能够成功和版本、优化器限制、隐含参数、补丁等有关。

随便在MOS上搜索一下查询转换，就会出现一堆BUG：

竟然还是Wrong result（错误的结果），遇到这种BUG不是性能问题了，而是严重的数据正确性问题，当然，在MOS里随便可以找到一堆这样的BUG，但是，在实际应用中，我相信，你可能碰到的较少，如果有一天，你看到一条SQL查询的结果可能不对，那你也得大胆质疑，对于ORACLE这种庞然大物来说，遇到问题，质疑是非常正确的思考方式，这种Wrong result问题，在数据库大版本升级过程中可能见到，主要有两类问题：

1.原来结果正确，现在结果错误。--遇到新版本BUG

2.现在结果正确，原来结果错误。--新版本修复了老版本BUG

第一种情况很正常，第二种情况也可能存在，我就看到过一客户质疑升级后的结果不正确，结果经过查证之后，竟然是老版本执行计划就是错的，新版本执行计划是正确的，也就是错误了很多年，都没有发现，结果升级后是正确的，却以为是错了。 遇到错误结果，如果不是非核心功能，真的可能被深埋很多年。

Estimator(估算器)：

  很显然，估算器会利用统计信息（表、索引、列、分区等）来估算对应执行计划操作中的选择性，从而计算出对应操作的cardinality，生成对应操作的COST，并最终计算整个计划的COST。对于估算器来说，很重要的就是其估算模型的准确性以及统计信息存储的准确性，估算的模型越科学，统计信息能反应实际的数据分布情况，能够覆盖更多的特殊数据，那么生成的COST则更加准确。

  然而，这是不可能的情况，估算器模型以及统计信息中存在诸多问题，比如针对字符串计算选择性，ORACLE内部会将字符串转换为RAW类型，在将RAW类型转换成数字，然后左起ROUND 15位，这样会出现可能字符串相差很大的，由于转换成数字后超过15位，那么内部转换后可能结果相近，最终导致计算的选择性不准确。

        

Plan Generator(计划生成器)：

  计划生成器也就是分析各种访问路径、JOIN方法、JOIN顺序，从而生产不同执行计划。那么如果这个部分出现问题，也就是对应的部分可能算法不够完善或者存在限制。比如JOIN的表很多，那么各种访问顺序的选择成几何级数增长，ORACLE内部有限制值，也就是事实不可能全部计算一遍。   
  比如HASH JOIN算法是普遍做大数据处理的首选算法，但是由于HASH JOIN天生存在一种限制：HASH碰撞，一旦遇到HASH碰撞，必然导致效率大减。

  综合来说，CBO优化器的坑主要就出现在优化器组件以及组件相关的统计信息部分，比如查询转换器的一些限制导致某些SQL无法进行查询转换、CBO选择性算法的一些不完善导致选择性计算错误、统计信息自身存储限制如直方图只存储前32字节等，导致最终优化器无法选择optimal的执行计划。

  CBO优化器存在很多限制，详细可以参考MOS：Limitations of the Oracle Cost Based Optimizer (文档 ID 212809.1)。

未完待续，见：