Oracle Optimizer CBO RBO

之前整理的一篇有关 CBO 和 RBO 文章：

Oracle CBO 与 RBO

http://blog.csdn.net/tianlesoftware/archive/2010/07/11/5709784.aspx

 

Oracle 数据库中优化器（ Optimizer ）是 SQL 分析和执行的优化工具，它负责指定 SQL 的执行计划，也就是它负责保证 SQL 执行的效率最高，比如优化器决定 Oracle 以什么样的方式来访问数据，是全表扫描（ Full Table Scan ），索引范围扫描（ Index Range Scan ）还是全索引快速扫描（ INDEX Fast Full Scan ： INDEX_FFS ） ; 对于表关联查询，它负责确定表之间以一种什么方式来关联，比如 HASH_JOHN 还是 NESTED LOOPS 或者 MERGE JOIN 。 这些因素直接决定 SQL 的执行效率，所以优化器是 SQL 执行的核心，它做出的执行计划好坏，直接决定着 SQL 的执行效率。

 

         Oracle 的优化器有两种：

                   RBO( Rule-Based Optimization ): 基于规则的优化器

                   CBO( Cost-Based Optimization ): 基于代价的优化器

        

         从 Oracle 10g 开始， RBO 已经被弃用，但是我们依然可以通过 Hint 方式来使用它。

 

 

一．        RBO 基于规则的优化器

在 8i 之前， Oracle 使用的是一种叫作 RBO （ Rule Based Optimizer ）的优化器，它的执行机制非常简单，就是在优化器里面嵌入若干种规则，执行的 SQL 语句符合哪种规则（ RANK ），则按照规则 (RANK) 制定出相应的执行计划，比如说表上有个索引，如果谓词上有索引的列存在，则 Oracle 会选择索引，否则选择全表扫描；又比如，两个表关联的时候，按照表在 SQL 中的位置来决定哪个是驱动表，哪个是被驱动表。

 

RBO 选择执行计划的一个优先级列表

        

Rank	Access Path
1	Single row by ROWID
2	Single row by cluster join
3	Single row by hash cluster key with unique or primary key
4	Single row by unique or primary key
5	Cluster Join
6	Hash cluster key
7	Indexed cluster key
8	Composite index
9	Single-column index
10	Bounded range search on indexed columns
11	Unbounded range search on indexed columns
12	Sort-merge join
13	MAX OR MIN of indexed column
14	ORDER by on indexed column
15	Full table scan

 

由于 RBO 只是简单的去匹配 Rank ，所以它的执行计划有时并不是最佳的。 比如我们有一张数据分布非常不均匀的表。 90% 的数据内容是一样的，并且在这个字段上有索引。 如果我们的 SQL 谓词里有这个字段，那么 RBO 就会选择走索引。 这就会增加额外的开销。 因为 Oracle 要先访问索引数据块，在索引上找到相应的键值，然后按照键值上的 rowid 在去访问表中的相应数据。 在这种情况下，我们选择全表扫描是最优的，但是 RBO 不会这么选择。

 

 

二．        CBO 基于成本的优化器

从 8i 开始， Oracle 引入了 CBO （ Cost Based Optimizer ），它的思路是让 Oracle 获取所有执行计划相关的信息，通过对这些信息做计算分析，最后得出一个代价最小的执行计划作为最终的执行计划。

 

CBO 是一种比 RBO 更理性化的优化器。从 10g 开始， Oracle 已经彻底丢弃了 RBO 。 即使在表，索引没有被分析的时候， Oracle 依然会使用 CBO 。此时， Oracle 会使用一种叫做动态采样的技术，在分析 SQL 的时候，动态的收集表，索引上的一些数据块，使用这些数据块的信息及字典表中关于这些对象的信息来计算出执行计划的代价，从而挑出最优的执行计划。

 

当表没有做分析的时候， Oracle 会使用动态采样来收集统计信息，这个动作只有在 SQL 执行的第一次，即硬分析阶段使用，后续的软分析将不在使用动态采样，直接使用第一次 SQL 硬分析时生成的执行计划。

        

Oracle SQL 的硬解析和软解析

http://blog.csdn.net/tianlesoftware/archive/2010/04/08/5458896.aspx

 

 

在 Oracle 10g 中， CBO 可选的运行模式有 2 种：

（1）        FIRST_ROWS(n)

（2）        ALL_ROWS  -- 10g 中的默认值

 

查看 CBO 模式：

SQL> show parameter optimizer_mode

NAME                                 TYPE        VALUE

------------------------------------ ----------- -------------

optimizer_mode                       string      ALL_ROWS

 

修改 CBO 模式的三种方法：

（1）        SQL 语句：

Sessions 级别：

         SQL> alter session set optimizer_mode=all_rows;

         （ 2 ） 修改 pfile 参数：

OPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS

（3）        语句级别用 Hint （ /* +   ...   */ ）来设定

Select /*+ first_rows(10) */ name from table;

                      Select /*+ all_rows */ name from table;  

 

 

OPTIMIZER_INDEX_COST_ADJ 参数

参数 OPTIMIZER_INDEX_COST_ADJ 可以理解为 Oracle 执行多块（ MultiBlock ） I/O （比如全表扫描）的代价与执行单块（ Single-block ） I/O 代价的相对比例。 OPTIMIZER_INDEX_COST_ADJ 通过指明索引 I/O 代价与扫描全表 I/O 代价的相对比值来影响 CBO 的行为，取值越小， CBO 越倾向于使用索引，取值越大，越倾向于全表扫描。而缺省值 100 ，指明缺省下，二者的代价是相等。

 

官方文档（ Reference ）中对这个参数描述如下：

OPTIMIZER_INDEX_COST_ADJ

Property	Description
Parameter type	Integer
Default value	100
Modifiable	ALTER SESSION, ALTER SYSTEM
Range of values	1 to 10000

 

OPTIMIZER_INDEX_COST_ADJ lets you tune optimizer behavior for access path selection to be more or less index friendly—that is, to make the optimizer more or less prone to selecting an index access path over a full table scan.

The default for this parameter is 100 percent, at which the optimizer evaluates index access paths at the regular cost. Any other value makes the optimizer evaluate the access path at that percentage of the regular cost. For example, a setting of 50 makes the index access path look half as expensive as normal.

Note:

The adjustment does not apply to user-defined cost functions for domain indexes.

 

http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/initparams160.htm#REFRN10143

 

 

 

FIRST_ROWS （ n ） 模式说明

         当 CBO 的优化模式设置为 FIRST_ROWS （ n ）时， Oracle 在执行 SQL 时，优先考虑将结果集中的前 n 条记录以最快的速度反馈回来，而其他的结果并不需要同时返回。

这种需求在一些网站或者 BBS 的分页上经常看到，比如每次只显示查询信息的前 20 条或者 BBS 上的前 20 个帖子， 这时候设置 FIRST_ROWS （ 20 ）就非常合适，优化器并不需要同事将所有符合条件的结果返回，用户也不需要。这时， CBO 将考虑用一种最快的返回前 20 条记录的执行计划，这种执行计划对于 SQL 的整体执行时间也不不是最快的，但是在返回前 20 条记录的处理上，确实最快的。

如：

         Select /*+ first_rows(10) */b.x,b.y from

                   (

                   Select /*+ first_rows(10) */ a.*, rownum rnum from

(

         Select /*+ first_rows(20) */ * from t order by x

) a

Where rownum < 20

) b where rnum >=10;

在这个分页例子中，每次从结果集中取 10 条记录，记录按照 x 字段排序。

 

注意： 排序使用的字段 x 必须创建有索引，否则 CBO 会忽略 FIRST_ROWS(n) ，而使用 ALL_ROWS.

 

 

ALL_ROWS 模式说明

         当 CBO 模式设置为 ALL_ROWS 时， Oracle 会用最快的速度将 SQL 执行完毕，将结果集全部返回，它和 FIRST_ROWS （ n ）的区别在于， ALL_ROWS 强调以最快的速度将 SQL 执行完毕，并将所有的结果集反馈回来，而 FIRST_ROWS （ n ）则侧重于返回前 n 条记录的执行时间。

 

         ALL_ROWS 在 OLAP 系统中使用得比较多，它用最快的速度获得 SQL 执行的最后一条记录，而不是前 N 条记录。 和 FIRST_ROWS （ n ）正好相反。 ALL_ROWS 强调 SQL 整体的执行效率，而 FIRST_ROWS （ n ）强调用最快的速度返回前 N 行，而不管所有的结果返回的时长，可能最后一条要很长时间才能获得。