【DB笔试面试574】在Oracle中，什么是RBO和CBO？

• 2019 年 10 月 10 日
• 筆記

♣

题目部分

在Oracle中，什么是RBO和CBO？

♣

答案部分

Oracle数据库中优化器（Optimizer）是SQL分析和执行的优化工具，是Oracle数据库中内置的一个核心模块。优化器的目的就是为了得到目标SQL的执行计划。Oracle数据库里的优化器又分为RBO（rule-Based Optimizer，基于规则的优化器）和CBO（Cost-Based Optimizer，基于成本的优化器）这两种类型。从Oracle 10g开始，Oracle数据库默认都是基于CBO的优化方式。

（一）RBO

RBO的执行机制非常简单，就是在优化器里面嵌入若干种规则，如果执行的SQL语句符合某个规则（Rank，共有1~15共15个等级），那么Oracle会按照规则（Rank）制定出相应的执行计划。由于RBO只是简单的去匹配Rank，所以它的执行计划在很多时候并不是最佳的。例如，某个表的其中一列数据分布非常不均匀，其中90%的数据内容是一样的，并且在这个字段上有索引。如果在目标SQL语句的谓词里有这个字段，那么RBO就会选择走索引。而这是一种非常慢的执行路径，因为Oracle要先访问索引块，在索引上找到相应的键值，然后按照键值上的ROWID再去访问表中的相应数据。其实，在这种情况下，选择全表扫描是最优的，但是RBO不会这么选择。RBO的缺点主要有：

①执行计划出了问题，很难对其做调整。

②执行计划会受目标SQL的写法、表在WHERE条件中出现的先后顺序等因素的影响。

③Oracle很多新特性不被RBO支持。

④制定出差的执行计划的概率比较大。

⑤忽略了SQL中表本身的统计信息情况。

有的时候即使修改了优化器模式或者使用了RULE Hint，Oracle依然不会使用RBO（而是强制使用CBO），这些情况包括：

l 当RULE和DRIVING_SITE联合使用时，RULE会失效。

l 目标SQL中涉及的对象有IOT（Index Organized Table）。

l 目标SQL中涉及的对象有分区表。

l 使用了并行查询或者并行DML。

l 使用了星型连接。

l 使用了哈希连接。

l 使用了索引快速全扫描。

l 使用了函数索引。

（二）CBO

从Oracle 7开始就引入了CBO。CBO是基于成本的优化器，它根据可用的访问路径、对象的统计信息、嵌入的Hint来选择一个成本最低的执行计划。优化器在使用CBO时，主要参照的是表、列及索引的统计信息。DBA可以通过设置初始化参数OPTIMIZER_MODE来决定到底使用哪个优化器，也可以用ALTER SESSION来改变当前SESSION中OPTIMIZER_MODE的值。除此之外在SQL中嵌入Hint可以指定具体某个SQL使用哪个优化器。OPTIMIZER_MODE选项有：ALL_ROWS、FIRST_ROWS_n（这里的n只能为1、10、100、1000）、FIRST_ROWS、CHOOSE和RULE，默认为ALL_ROWS。CBO包含的组件主要有：查询转换器（Query Transformer）、评估器（Estimator）和计划生成器（Plan Generator），如下图所示：

查询转换器的作用是改变查询语句的形式以产生较好的执行计划，主要地转换技术有：视图合并（View Merging）、谓词推进（Predicate Pushing）、非嵌套子查询（Subquery Unnesting）和物化视图的查询重写（Query Rewrite with Materialized Views）等。评估器通过计算选择性（Selectivity）、基数（Cardinality）、成本（Cost）这3个值来评估执行计划的总体成本。计划生成器的作用就是生成大量的执行计划，然后选择其中总体成本最低的一个。

CBO的缺点主要有：

①　CBO会默认目标SQL语句WHERE条件中出现的各个列之间是独立的，没有关联关系，并且CBO会依据这个前提条件来计算组合可选择率、Cardinality，进而来估算成本并选择执行计划。但这种前提条件并不总是正确的，在实际的应用中，目标SQL的各列之间有关联关系的情况实际上并不罕见。在这种各列之间有关联关系的情况下，如果还用之前的计算方法来计算目标SQL语句整个WHERE条件的组合可选择率，并用它来估算返回结果集的Cardinality的话，那么估算结果可能就会和实际结果有较大的偏差，导致CBO选错执行计划。目前可以用来缓解上述问题所带来负面影响的方法是使用动态采样或者多列统计信息，但动态采样的准确性取决于采样数据的质量和采样数据的数量，而多列统计信息并不适用于多表之间有关联关系的情形，所以这两种解决方法都不能算是完美的解决方案。

②　CBO会假设所有的目标SQL都是单独执行、并且是互不干扰的，但实际情况却完全不是这样。在执行目标SQL时所需要访问的索引叶子块、数据块等可能由于之前执行的SQL而已经被缓存在Buffer Cache中，所以这次执行时也许不需要耗费物理I/O去相关的存储上读要访问的索引叶子块、数据块等，而只需要去Buffer Cache中读相关的缓存块就可以了。所以，如果此时CBO还是按照目标SQL是单独执行，不考虑缓存的方式去计算相关成本值的话，就可能会高估走相关索引的成本，进而可能会导致选错执行计划。

③　CBO对直方图统计信息有诸多限制。表现为两点，（1）在Oracle 12c之前，Frequency类型的直方图所对应的Bucket的数量不能超过254，这样如果目标列的distinct值的数量超过254，Oracle就会使用Height Balanced类型的直方图。对于Height Balanced类型的直方图而言，因为Oracle不会记录所有的Nonpopular Value的值，所以在此情况下CBO选错执行计划的概率会比对应的直方图统计信息是Frequency类型的情形要高。（2）在Oracle数据库里，如果针对文本型的字段收集直方图统计信息，那么Oracle只会将该文本型字段的文本值的头32字节给取出来（实际上只取头15字节）并将其转换成一个浮点数，然后将该浮点数作为上述文本型字段的直方图统计信息存储在数据字典里。这种处理机制的先天缺陷就在于，对于那些超过32字节的文本型字段，只要对应记录的文本值的头32字节相同，Oracle在收集直方图统计信息的时候就会认为这些记录该字段的文本值是相同的，即使实际上它们并不相同。这种先天性的缺陷会直接影响CBO对相关文本型字段的可选择率及返回结果集的Cardinality的估算，进而就可能导致CBO选错执行计划。

④　CBO在解析多表关联的目标SQL时，可能会漏选正确的执行计划。因为随着多表关联的目标SQL所包含表的数量的递增，各表之间可能的连接顺序会呈几何级数增长，即该SQL各种可能的执行路径的总数也会随之呈几何级数增长。假设多表关联的目标SQL所包含表的数量为n，则该SQL各表之间可能的连接顺序的总数就是n!（n的阶乘）。在Oracle 11gR2中，CBO在解析这种多表关联的目标SQL时，所考虑的各个表连接顺序的总和会受隐含参数“_OPTIMIZER_MAX_PERMUTATIONS”的限制，这意味着不管目标SQL在理论上有多少种可能的连接顺序，CBO至多只会考虑其中根据“_OPTIMIZER_MAX_PERMUTATIONS”计算出来的有限种可能。这同时也意味着只要该目标SQL正确的执行计划并不在上述有限种可能之中，则CBO一定会漏选正确的执行计划。

虽然CBO有上述这些缺点，但是瑕不掩瑜，CBO是目前Oracle中解析目标SQL的不二选择，并且随着Oracle数据库版本不断的进化，CBO也会越来越完善。

& 说明：

有关CBO和RBO的更多内容可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2140698/

本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。