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行，而不管所有的结果返回的时长，可能最后一条要很长时间才能获得。