在CBO的优化模式下，我们可以使用optimizer_mode参数控制优化模式。主要有两种模式，一种是ALL_ROWS模式，另外一种是FIRST_ROWS模式。

    ALL_ROWS模式适用场景：希望优化程序给出一种尽快得到全部记录的执行计划，目标是增加系统的吞吐量。

    FIRST_ROWS模式适用场景：希望优化程序给出一种可以迅速的得到第一行的执行计划，目标是减少系统的响应时间。

    两种模式需要具体场景具体分析，比如常见的Web应用，很少有一次性得到全部记录的情况，都是分多页交互的响应操作者，因此默认的ALL_ROWS模式就不太合适了，应该考虑使用FIRST_ROWS模式进行优化。又如，我们想要生成全部数据的报表，那么默认的ALL_ROWS模式就比较的合适。

    下面通过实验来比较all_rows和first_rows对执行计划的影响：

  1.实验环境：

  操作系统：rhel 5.4 x32

  数据库：oracle 11.2.0.1.0

  2.首先我们创建一个具有dba权限的用户jack_lin，default_tablespace使用默认的users。

1 SQL> conn /as sysdba2 Connected.3 SQL> create user jack_lin identified by jack;4 User created.5 SQL> grant dba to jack_lin;6 Grant succeeded.

  3.创建该实验需要用到的一张表。

1 SQL> conn jack_lin/jack; 2 Connected. 3 SQL> create table test(id number,name varchar2(10)); 4 Table created. 5 SQL> insert into test values(100,'aaaa'); 6 1 row created. 7 SQL> insert into test values(200,'bbbb'); 8 1 row created. 9 SQL> insert into test values(300,'cccc');10 1 row created.11 SQL> insert into test values(400,'dddd');12 1 row created.13 SQL> commit;14 Commit complete.

  4.在没有索引的情况比较：

  首先来看FIRST_ROWS的效果，为了保证CBO执行计划的准确，我们需要analyze一下表。

1 SQL> alter session set optimizer_mode=first_rows; 2  3 Session altered. 4  5 SQL> analyze table test compute statistics; 6  7 Table analyzed. 8  9 SQL> set autotrace trace exp;10 SQL> select * from test where name='aaaa';11 12 Execution Plan13 ----------------------------------------------------------14 Plan hash value: 135708102015 16 --------------------------------------------------------------------------17 | Id  | Operation      | Name | Rows  | Bytes | Cost (%CPU)| Time     |18 --------------------------------------------------------------------------19 |   0 | SELECT STATEMENT  |     |     1 |     6 |     3   (0)| 00:00:01 |20 |*  1 |  TABLE ACCESS FULL| TEST |     1 |     6 |     3   (0)| 00:00:01 |21 --------------------------------------------------------------------------22 23 Predicate Information (identified by operation id):24 ---------------------------------------------------25 26    1 - filter("NAME"='aaaa')

  由于表上没有索引，所以只有一种选择，全表扫描。

  现在再看一下ALL_ROWS的情况：

1 SQL> alter session set optimizer_mode=all_rows; 2  3 Session altered. 4  5 SQL> select * from test where name='aaaa'; 6  7 Execution Plan 8 ---------------------------------------------------------- 9 Plan hash value: 135708102010 11 --------------------------------------------------------------------------12 | Id  | Operation      | Name | Rows  | Bytes | Cost (%CPU)| Time     |13 --------------------------------------------------------------------------14 |   0 | SELECT STATEMENT  |     |     1 |     6 |     3   (0)| 00:00:01 |15 |*  1 |  TABLE ACCESS FULL| TEST |     1 |     6 |     3   (0)| 00:00:01 |16 --------------------------------------------------------------------------17 18 Predicate Information (identified by operation id):19 ---------------------------------------------------20 21    1 - filter("NAME"='aaaa')

  通过上面的简单举例比较，可以看到在表上没有索引，当数据量很少，并且值唯一的情况下，两种模式的效果是一样的。

  5.在有索引的情况下比较：

  创建索引，并执行在FIRST_ROWS的操作

1 SQL> create index ind_test on test(name); 2  3 Index created. 4  5 SQL> analyze index ind_test compute statistics; 6  7 Index analyzed. 8  9 SQL> analyze table test compute statistics;10 11 Table analyzed.12 13 SQL>  select /*+ first_rows */* from test where name='aaaa';14 15 Execution Plan16 ----------------------------------------------------------17 Plan hash value: 385646689718 19 ----------------------------------------------------------------------------------------20 | Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |21 ----------------------------------------------------------------------------------------22 |   0 | SELECT STATEMENT        |           |     1 |     6 |     2     (0)| 00:00:01 |23 |   1 |  TABLE ACCESS BY INDEX ROWID| TEST     |     1 |     6 |     2     (0)| 00:00:01 |24 |*  2 |   INDEX RANGE SCAN        | IND_TEST |     1 |       |     1     (0)| 00:00:01 |25 ----------------------------------------------------------------------------------------26 27 Predicate Information (identified by operation id):28 ---------------------------------------------------29 30    2 - access("NAME"='aaaa')

 

  设置成ALL_ROWS的情况：

1 SQL> select /*+ all_rows */ * from test where name='aaaa'; 2  3 Execution Plan 4 ---------------------------------------------------------- 5 Plan hash value: 3856466897 6  7 ---------------------------------------------------------------------------------------- 8 | Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     | 9 ----------------------------------------------------------------------------------------10 |   0 | SELECT STATEMENT        |           |     1 |     6 |     2     (0)| 00:00:01 |11 |   1 |  TABLE ACCESS BY INDEX ROWID| TEST     |     1 |     6 |     2     (0)| 00:00:01 |12 |*  2 |   INDEX RANGE SCAN        | IND_TEST |     1 |       |     1     (0)| 00:00:01 |13 ----------------------------------------------------------------------------------------14 15 Predicate Information (identified by operation id):16 ---------------------------------------------------17 18    2 - access("NAME"='aaaa')

   通过上面的演示可以看到两种模式都走了索引，目前来看一切正常。

  6.现在通过insert into test select * from test;往test表中反复插入记录，注意记录大部分是重复的，其实只有四条，各占1/4。

1 set autotrace off; 2 SQL> insert into test select * from test; 3  4 16384 rows created. 5 SQL> analyze table test compute statistics; 6  7 Table analyzed. 8 SQL> analyze index ind_test compute statistics; 9 10 Index analyzed.11 12 SQL> alter session set optimizer_mode=first_rows;13 14 Session altered.15 16 SQL> set autotrace trace exp;17 SQL> select * from test where name='aaaa';18 19 Execution Plan20 ----------------------------------------------------------21 Plan hash value: 385646689722 23 ----------------------------------------------------------------------------------------24 | Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |25 ----------------------------------------------------------------------------------------26 |   0 | SELECT STATEMENT        |           |  8192 | 49152 |    87     (0)| 00:00:02 |27 |   1 |  TABLE ACCESS BY INDEX ROWID| TEST     |  8192 | 49152 |    87     (0)| 00:00:02 |28 |*  2 |   INDEX RANGE SCAN        | IND_TEST |  8192 |       |    28     (0)| 00:00:01 |29 ----------------------------------------------------------------------------------------30 31 Predicate Information (identified by operation id):32 ---------------------------------------------------33 34    2 - access("NAME"='aaaa')35 36 SQL> alter session set optimizer_mode=all_rows;37 38 Session altered.39 40 SQL> select * from test where name='aaaa';41 42 Execution Plan43 ----------------------------------------------------------44 Plan hash value: 135708102045 46 --------------------------------------------------------------------------47 | Id  | Operation      | Name | Rows  | Bytes | Cost (%CPU)| Time     |48 --------------------------------------------------------------------------49 |   0 | SELECT STATEMENT  |     |  8192 | 49152 |    19   (0)| 00:00:01 |50 |*  1 |  TABLE ACCESS FULL| TEST |  8192 | 49152 |    19   (0)| 00:00:01 |51 --------------------------------------------------------------------------52 53 Predicate Information (identified by operation id):54 ---------------------------------------------------55 56    1 - filter("NAME"='aaaa')

  这时我们看到FIRST_ROWS走了索引，就本例而言，这显然不是一种理想的结果，而ALL_ROWS走了全表扫描，我们可以看到成本明显更低。

  参考一下Oracle 10g官方文档关于optimizer_mode参数的描述

OPTIMIZER_MODE

Property	Description
Parameter type	String
Syntax	OPTIMIZER_MODE = { first_rows_[1 | 10 | 100 | 1000] | first_rows | all_rows }
Default value	all_rows
Modifiable	ALTER SESSION, ALTER SYSTEM

OPTIMIZER_MODE establishes the default behavior for choosing an optimization approach for the instance.

Values:

• first_rows_n

  The optimizer uses a cost-based approach and optimizes with a goal of best response time to return the first n rows (where n = 1, 10, 100, 1000).

• first_rows

  The optimizer uses a mix of costs and heuristics to find a best plan for fast delivery of the first few rows.

• all_rows

  The optimizer uses a cost-based approach for all SQL statements in the session and optimizes with a goal of best throughput (minimum resource use to complete the entire statement).

  总结：

  Oracle默认的优化模式并不一定是我们想要的，必须根据自己的系统特定细心的定制。