讨论ALL_ROWS模式和FIRST_ROWS模式

    在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 sysdba
2 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 Plan
13 ----------------------------------------------------------
14 Plan hash value: 1357081020
15 
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: 1357081020
10 
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 Plan
16 ----------------------------------------------------------
17 Plan hash value: 3856466897
18 
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 Plan
20 ----------------------------------------------------------
21 Plan hash value: 3856466897
22 
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 Plan
43 ----------------------------------------------------------
44 Plan hash value: 1357081020
45 
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默认的优化模式并不一定是我们想要的，必须根据自己的系统特定细心的定制。