Oracle的优化器共有两种的优化方式，即基于规则的优化方式(Rule-Based Optimization，简称为RBO)和基于代价的优化方式(Cost-Based Optimization，简称为CBO)。

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

　　B、CBO方式：依词义可知，它是看语句的代价(Cost)了，这里的代价主要指Cpu和内存。优化器在判断是否用这种方式时，主要参照的是表及索引的统计信息。统计信息给出表的大小、有少行、每行的长度等信息。这些统计信息起初在库内是没有的，是你在做analyze后才出现的，很多的时侯过期统计信息会令优化器做出一个错误的执行计划，因些我们应及时更新这些信息。在Oracle8及以后的版本，Oracle列推荐用CBO的方式。

Optimizer_Mode

1. ALL_ROWS

1)Oracle 10g之后，ALL_ROWS成为 Optimizer_Mode的默认参数取值

2)ALL_ROWS模式下，查询优化器就会采用完全的CBO机制，借助数据表、索引的统计量，可以最大限度的获取到成本最低的执行计划.

3)如果相关数据表没有收集过统计量，又要使用CBO的机制，就会引起动态采样。

SQL> Create table bb as select * from dba_objects

SQL> create index bb_ind on bb(object_id);

SQL> exec dbms_stats.delete_table_stats('SYS','BB',cascade_parts => true,cascade_columns => true); ###删除统计信息

SQL> explain plan for select * from bb where object_ID=35;

SQL> select * from table(dbms_xplan.display(null,null,'advanced'));

Plan hash value: 225075763

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

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

| 0 | SELECT STATEMENT | | 1 | 207 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| BB | 1 | 207 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | BB_IND | 1 | | 1 (0)| 00:00:01 |

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

Outline Data

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

/*+

BEGIN_OUTLINE_DATA

INDEX_RS_ASC(@"SEL$1" "BB"@"SEL$1" ("BB"."OBJECT_ID"))

OUTLINE_LEAF(@"SEL$1")

ALL_ROWS

DB_VERSION('11.2.0.3')

OPTIMIZER_FEATURES_ENABLE('11.2.0.3')

IGNORE_OPTIM_EMBEDDED_HINTS

END_OUTLINE_DATA

*/

Note

-----

- dynamic sampling used for this statement (level=2) ##动态采集

51 rows selected.

其中dynamic sampling used for this statement (level=2) ：

动态采样（dynamic sampling）就是在生成执行计划时，以一个很小的采用率现进行统计量收集。由于采样率低，采样过程快但是不精确，而且采样结果不会进入到数据字典中

2. RULE

在CBO时代，虽然已经不提供明确的RBO优化器支持，但是RBO相关代码还是驻留在Query Optimizer中的

这种模式下是基于规则的方式，忽略CBO和统计数据并且完全基于基本数据字典信息生成执行计划

SQL> create table t as select * from dba_objects;

SQL> create index idx_t_owner on t(owner);

SQL> explain plan for select * from t where owner='SYS';

Explained

SQL> select * from table(dbms_xplan.display(null,null,'advanced'));

PLAN_TABLE_OUTPUT

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

Plan hash value: 1516787156

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

| Id | Operation | Name |

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

| 0 | SELECT STATEMENT | |

| 1 | TABLE ACCESS BY INDEX ROWID| T |

|* 2 | INDEX RANGE SCAN | IDX_T_OWNER |

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

Outline Data

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

/*+

BEGIN_OUTLINE_DATA

INDEX(@"SEL$1" "T"@"SEL$1" ("T"."OWNER"))

OUTLINE_LEAF(@"SEL$1")

RBO_OUTLINE

OPTIMIZER_FEATURES_ENABLE('10.2.0.1')

IGNORE_OPTIM_EMBEDDED_HINTS

END_OUTLINE_DATA

*/

Predicate Information (identified by operation id):

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

2 - access("OWNER"='SYS')

Note

-----

- rule based optimizer used (consider using cbo) ### 走RBO，并建议用CBO

49 rows selected

该T表的OWNER列其实重复性极其高，可以说大部分都为SYS用户，其实此时执行计划走全表效率会较高，可RBO直接就根据RULE走了索引

3. CHOOSE

在Oracle 9i一个时期，optimizer_mode的默认参数取值为CHOOSE。那个时期是query optimizer从RBO向CBO转换的时代，Oracle选择了渐变式的转换。由于那时没有专门的夜间统计量收集作业，很多时候数据表是没有统计量的。加之动态抽样技术的不成熟，所以CBO的应用存在一些障碍。

在这个时期，Oracle提出了CHOOSE作为优化器默认模式。 CHOOSE是介于自动选择切换RBO和CBO。如果SQL涉及的数据表中有一个有统计量，那么该SQL就是用CBO优化器。否则就是用RBO。动态采样技术是在CBO优化器前提下的技术方案。

SQL> exec dbms_stats.delete_table_stats(user,'T',cascade_columns => true,cascade_indexes => true); ####删除统计信息

PL/SQL procedure successfully completed

SQL> explain plan for select * from t where wner='SYS';

Explained

SQL> select * from table(dbms_xplan.display(null,null,'advanced'));

PLAN_TABLE_OUTPUT

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

Plan hash value: 1516787156

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

| Id | Operation | Name |

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

| 0 | SELECT STATEMENT | |

| 1 | TABLE ACCESS BY INDEX ROWID| T |

|* 2 | INDEX RANGE SCAN | IDX_T_OWNER |

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

Outline Data

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

/*+

BEGIN_OUTLINE_DATA

INDEX(@"SEL$1" "T"@"SEL$1" ("T"."OWNER"))

OUTLINE_LEAF(@"SEL$1")

RBO_OUTLINE

OPTIMIZER_FEATURES_ENABLE('10.2.0.1')

IGNORE_OPTIM_EMBEDDED_HINTS

END_OUTLINE_DATA

*/

Note

-----

- rule based optimizer used (consider using cbo)

49 rows selected

可见, 没有统计信息的情况下，数据库选择直接使用RBO方式。

下面看看当SQL涉及2个表，其中1个表有统计收集，1个表则没有的情况：

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

SQL> create table t_parts as select * from dba_tables;

Table created

SQL> explain plan for select * from t, t_parts where t.object_name=t_parts.table_name and t.owner=t_parts.table_name;

Explained

SQL> select * from table(dbms_xplan.display(null,null,'advanced'));

PLAN_TABLE_OUTPUT

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

Plan hash value: 2202291360

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

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

| 0 | SELECT STATEMENT | | 1598 | 903K| 171 (4)| 00:00:03 |

|* 1 | HASH JOIN | | 1598 | 903K| 171 (4)| 00:00:03 |

| 2 | TABLE ACCESS FULL| T_PARTS | 1598 | 759K| 12 (0)| 00:00:01 |

| 3 | TABLE ACCESS FULL| T | 50405 | 4528K| 157 (3)| 00:00:02 |

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

Note

-----

- dynamic sampling used for this statement (动态采集）

123 rows selected

可见，其中一个表有统计收集信息，另外一个表没有统计收集信息，在CHOOSE模式下，就选择了CBO优化器，进而对T_PARTS进行动态采样。

4. FIRST_ROWS系列（包FIRST_ROWS_N）模式

1）该模式是一种介于CBO与RBO之间的优化器模式，与CHOOSE的切换方式不同。FIRST_ROWS模式系列与CBO一个显著的差异就是追求最优执行计划的标准不同。

2）ALL_ROWS代表的CBO追求的是生成综合成本最低的SQL执行计划。而FIRST_ROWS追求的是执行计划最快返回结果集合，优先相应前几条的查询结果。所以，FIRST_ROWS与CBO的核心价值有一些差异。

3）FIRST_ROWS有其用途场景，最多的就是在于分页操作SQL。当结果集合很大的时候，用户或者应用往往不关心全部集合的情况，而是关注返回前几条记录的响应速度

下面将用曾经的一次优化案例作为参考：

原SQL执行计划：

SQL> SELECT timerID, firstTriggeTime FROM eos.WFTimer WHERE timerType='0' AND firstTriggeTime<='1336976519162' AND currentState='1' ORDER BY firstTriggeTime ASC;

?

no rows selected

?

Elapsed: 00:00:35.37

?

Execution Plan

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

Plan hash value: 1194487939

?

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

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

| 0 | SELECT STATEMENT | | 393K| 6919K| 1148K (1)| 03:49:40 |

|* 1 | TABLE ACCESS BY INDEX ROWID| WFTIMER | 393K| 6919K| 1148K (1)| 03:49:40 |

|* 2 | INDEX RANGE SCAN | TIMER_FSTTGR | 1180K| | 17826 (1)| 00:03:34 |

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

?

Predicate Information (identified by operation id):

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

?

1 - filter("CURRENTSTATE"='1' AND "TIMERTYPE"='0')

2 - access("FIRSTTRIGGETIME"<=1336976519162)

filter("FIRSTTRIGGETIME"<=1336976519162)

?

看执行计划就可以看见出现条件列出现 < 此类范围查询，而数据却采用了走索引的方法。此时就可以尝试查看优化模式，当时就发现改库走的是FIRST_ROWS模式，导致数据为了追求输出效率采用了走INDEX的做法错走了索引，后改了改SESSION的优化模式就解决了此问题

优化后：

SQL> SELECT /*+ALL_ROWS*/ timerID, firstTriggeTime FROM eos.WFTimer WHERE timerType='0' AND firstTriggeTime<='1336976519162' AND currentState='1' ORDER BY firstTriggeTime ASC;

?

no rows selected

?

Elapsed: 00:00:01.85

?

Execution Plan

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

Plan hash value: 3012435658

?

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

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

| 0 | SELECT STATEMENT | | 393K| 6919K| | 10544 (2)| 00:02:07 |

| 1 | SORT ORDER BY | | 393K| 6919K| 24M| 10544 (2)| 00:02:07 |

|* 2 | TABLE ACCESS FULL| WFTIMER | 393K| 6919K| | 8280 (2)| 00:01:40 |

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

?

Predicate Information (identified by operation id):

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

?

2 - filter("FIRSTTRIGGETIME"<=1336976519162 AND "CURRENTSTATE"='1' AND

"TIMERTYPE"='0')

?

效率提升约20倍

ITPub博客

SQL优化----实例讲解优化模式的区别

Optimizer_Mode

1. ALL_ROWS

2. RULE

3. CHOOSE

4. FIRST_ROWS系列（包FIRST_ROWS_N）模式