Oracle CBO 与 RBO

转：http://blog.csdn.net/tianlesoftware/article/details/5709784

Oracle的优化器有两种优化方式： 

基于规则的优化方式：Rule-Based Optimization（RBO）

基于成本或者统计信息的优化方式(Cost-Based Optimization：CBO) 

    RBO方式：优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则。比如我们常见的，当一个where子句中的一列有索引时去走索引。

    CBO方式：CBO是在ORACLE7 引入，但到ORACLE8i 中才成熟。ORACLE 已经声明在ORACLE9i之后的版本中，RBO将不再支持。它是看语句的代价(Cost),这里的代价主要指Cpu和内存。CPU Costing的计算方式现在默认为CPU+I/O两者之和.可通过DBMS_XPLAN.DISPLAY_CURSOR观察更为详细的执行计划。优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。统计信息给出表的大小、有少行、每行的长度等信息。这些统计信息起初在库内是没有的，是做analyze后才出现的，很多的时侯过期统计信息会令优化器做出一个错误的执行计划,因些应及时更新这些信息。按理，CBO应该自动收集，实际却不然，有时候在CBO情况下，还必须定期对大表进行分析。这个可以用定时的Job来实现，具体参考blog： 

Oracle 10g Statistic数据统计

http://blog.csdn.net/tianlesoftware/archive/2009/10/15/4668723.aspx  

注意：走索引不一定就是优的，比如一个表只有两行数据，一次IO就可以完成全表的检索,而此时走索引时则需要两次IO,这时全表扫描(full table scan)是最好。 

Oracle使用Optimizer_mode参数来控制优化器的偏好，9i常用的几个参数有：first_rows,all_rows,first_rows_N，rule,choose等。而10g少了rule和choose。

    Rule：基于规则的方式。

    Choolse：指的是当一个表或或索引有统计信息，则走CBO的方式，如果表或索引没统计信息，表又不是特别的小，而且相应的列有索引时，那么就走索引，走RBO的方式。

    First Rows：它与Choose方式是类似的，所不同的是当一个表有统计信息时，它将是以最快的方式返回查询的最先的几行，从总体上减少了响应时间。

All Rows: 10g中的默认值，也就是我们所说的Cost的方式，当一个表有统计信息时，它将以最快的方式返回表的所有的行，从总体上提高查询的吞吐量。没有统计信息则走RBO的方式。

 

设定优化器模式：

（1）Instance级别我们可以通过在initSID.ora文件中设定

OPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS

SQL> show parameter optimizer_mode

NAME                                 TYPE        VALUE

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

optimizer_mode                       string      ALL_ROWS

（2） Sessions级别通过ALTER SESSION SET OPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS来设定。

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

常见Oracle HINT的用法

http://blog.csdn.net/tianlesoftware/archive/2009/12/13/4969702.aspx

 

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

 

 在CBO下写SQL语句的注意事项：

 

1、RBO自ORACLE 6版以来被采用，有着一套严格的使用规则，只要你按照它去写SQL语句，无论数据表中的内容怎样，也不会影响到你的“执行计划”，也就是说对数据不“敏感”；CBO计算各种可能“执行计划”的“代价”，即cost，从中选用cost最低的方案，作为实际运行方案。各“执行计划”的cost的计算根据，依赖于数据表中数据的统计分布，ORACLE数据库本身对该统计分布并不清楚，必须要分析表和相关的索引（使用ANALYZE 命令），才能搜集到CBO所需的数据。

 

2、使用CBO 时，编写SQL语句时，不必考虑"FROM" 子句后面的表或视图的顺序和"WHERE" 子句后面的条件顺序；ORACLE自7版以来采用的许多新技术都是基于CBO的，如星型连接排列查询，哈希连接查询，函数索引，和并行查询等。

 

3、一般而言，CBO所选择的“执行计划”都不会比RBO的“执行计划”差，而且相对而言，CBO对程序员的要求没有RBO那么苛刻，节省了程序员为了从多个可能的“执行计划”中选择一个最优的方案而花费的调试时间，但在某些场合下也会存在问题。较典型的问题有：有时，表明明建有索引，但查询过程显然没有用到相关的索引，导致查询过程耗时漫长，占用资源巨大，这时就需要仔细分析执行计划，找出原因。例如，可以看连接顺序是否允许使用相关索引。假设表emp的deptno列上有索引，表dept的列deptno上无索引，WHERE语句有emp.deptno=dept.deptno条件。在做NL连接时，emp做为外表，先被访问，由于连接机制原因，外表的数据访问方式是全表扫描，emp.deptno上的索引显然是用不上，最多在其上做索引全扫描或索引快速全扫描。

 

4、如果一个语句使用 RBO的执行计划确实比CBO 好，则可以通过加 " rule" 提示，强制使用RBO。

 

5、使用CBO 时，SQL语句 "FROM" 子句后面的表，必须全部使用ANALYZE 命令分析过，如果"FROM" 子句后面的是视图，则此视图的基础表，也必须全部使用ANALYZE 命令分析过；否则，ORACLE 会在执行此SQL语句之前，自动进行ANALYZE 命令分析，这会极大导致SQL语句执行极其缓慢。

 

6、使用CBO 时，SQL语句 "FROM" 子句后面的表的个数不宜太多，因为CBO在选择表连接顺序时，会对"FROM" 子句后面的表进行阶乘运算，选择最好的一个连接顺序。假如"FROM" 子句后有6个表，则其可选择的连接顺序就是6*5*4*3*2*1 = 720 种，CBO 选择其中一种，而如果"FROM" 子句后有12个表，则其可选择的连接顺序就是12*11*10*9*8*7*6*5*4*3*2*1= 479001600 种，可以想象从中选择一种，会消耗多少CPU 时间？如果实在是要访问很多表，则最好使用 ORDER 提示，强制使用"FROM" 子句表固定的访问顺序。

 

7、使用CBO 时，SQL语句中不能引用系统数据字典表或视图，因为系统数据字典表都未被分析过，可能导致极差的“执行计划”。但是不要擅自对数据字典表做分析，否则可能导致死锁，或系统性能严重下降。

 

8、使用CBO 时，要注意看采用了哪种类型的表连接方式。ORACLE的共有Sort Merge Join（SMJ）、Hash Join（HJ）和Nested Loop Join（NL）。CBO有时会偏重于SMJ 和 HJ，但在OLTP 系统中，NL 一般会更好，因为它高效的使用了索引。在两张表连接，且内表的目标列上建有索引时，只有Nested Loop才能有效地利用到该索引。SMJ即使相关列上建有索引，最多只能因索引的存在，避免数据排序过程。HJ由于须做HASH运算，索引的存在对数据查询速度几乎没有影响。

 

9、使用CBO 时，必须保证为表和相关的索引搜集足够的统计数据。对数据经常有增、删、改的表最好定期对表和索引进行分析，可用SQL语句“analyze table xxx compute statistics for all indexes;"ORACLE掌握了充分反映实际的统计数据，才有可能做出正确的选择。

 

10、使用CBO 时，要注意被索引的字段的值的数据分布，会影响SQL语句的执行计划。例如：表emp，共有一百万行数据，但其中的emp.deptno列，数据只有4种不同的值，如10、20、30、40。虽然emp数据行有很多，ORACLE缺省认定表中列的值是在所有数据行均匀分布的，也就是说每种deptno值各有25万数据行与之对应。假设SQL搜索条件DEPTNO=10，利用deptno列上的索引进行数据搜索效率，往往不比全表扫描的高，ORACLE理所当然对索引“视而不见”，认为该索引的选择性不高。

 

我们考虑另一种情况，如果一百万数据行实际不是在4种deptno值间平均分配，其中有99万行对应着值10，5000行对应值20，3000行对应值30，2000行对应值40。在这种数据分布图案中对除值为10外的其它deptno值搜索时，毫无疑问，如果索引能被应用，那么效率会高出很多。我们可以采用对该索引列进行单独分析，或用analyze语句对该列建立直方图，对该列搜集足够的统计数据，使ORACLE在搜索选择性较高的值能用上索引。

 

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