一、OPTIMIZER_MODE参数简介
Oracle11g r2官方文档上说到OPTIMIZER_MOD的设置用于为实例选择默认优化方法的行为。文档中只介绍了三个参数值： 1、first_rows_n 优化器使用基于成本的方法并以最佳响应时间为目标进行优化以返回第一个n行(其中n = 1, 10, 100, 1000). 2、first_rows 优化器使用成本和试探的方式相结合的方法来为前几行的快速交付找到最佳方案。 3、all_rows 优化器对会话中的所有SQL语句使用基于成本的方法，并以最佳吞吐量(完成整个语句所需的资源最少)为目标进行优化。 查看其它资料还有另外两个参数 4、Choose 这个在Oracle10g之前为默认值。采用这个值时，Oracle即可以采用基于规则RBO，也可以采用基于代价的CBO，到底使用那个值，取决于当前SQL的被访问的表中是不是有可以使用的统计信息。 如果有多个被访问的表，其中有一个或多个有统计信息，那么Oralce会对没有统计信息的表进行采样统计（即不全部采样），统计完成后，使用基于代价的优化方法CBO。如果所有被访问的表都没有统计信息，Oracle就会采用基于规则的优化方法RBO。 5、rule 这个参数正好和ALL_ROWS相反，不管是不是统计信息，全部采用基于规则的优化方法。 从oracle10g开始optimizer_mode参数的默认值就是all_rows，也就是使用基于成本的优化器（rbo）。更改optimizer_mode参数的值有两种办法，一种是会话级的修改alter session set optimizer_mode=你想设置的参数值。另一种是数据库级的修改 alter system set optimitate_mode=你想设置的参数值。还有一种不用修改参数的方式那就是直接在SQL语句中加入提示。
/*+ all_rows */、/*+ rule */、/*+ choose */、/*+ first_rows(n)*/、/*+first_rows*/
二、OPTIMIZER_MODE参数测试
简单了解了OPTIMIZER_MODE参数有哪些值，下面对各个值进行测试。
一、OPTIMIZER_MODE=FIRST_ROWS_N
1、测试查询相同sql的执行计划与默认的ALL_ROWS执行计划有什么不同。
1、先查看当前数据库级的optimizer_mode参数值为多少。
SQL> show parameter optimizer_mode
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string ALL_ROWS
2、可以看到参数值为默认的all_rows然后创建一个测试表。
SQL> create table test1 as select * from dba_objects;
Table created.
3、在object_d列上创建一个普通索引。
SQL> create index ind_test1_obj_id on test1(object_id)
Index created.
4、然后在owner列上创建一个普通索引。
SQL> create index idx_t_owner on test1(owner);
Index created.
5、收集表和索引的统计信息。
SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'TEST1',estimate_percent=>100,cascade=>true,method_opt=>'for all columns size auto',no_invalidate=>false);
PL/SQL procedure successfully completed.
6、在all_rows参数下执行查看执行计划
SQL> select * from test1 where object_id<60000;
59120 rows selected.
Elapsed: 00:00:00.93
Execution Plan
----------------------------------------------------------
Plan hash value: 4122059633
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 58305 | 5523K| 289 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TEST1 | 58305 | 5523K| 289 (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"<60000)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4922 consistent gets
0 physical reads
0 redo size
3026175 bytes sent via SQL*Net to client
43875 bytes received via SQL*Net from client
3943 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
59120 rows processed
7、然后把参数设置为OPTIMIZER_MODE=FIRST_ROWS_10查看执行计划
SQL> alter session set optimizer_mode=first_rows_10;
Session altered.
SQL> select * from test1 where object_id<60000;
59120 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1096053794
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 1067 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST1 | 11 | 1067 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_TEST1_OBJ_ID | | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"<60000)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
8820 consistent gets
0 physical reads
0 redo size
6773172 bytes sent via SQL*Net to client
43875 bytes received via SQL*Net from client
3943 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
59120 rows processed
从执行计划上看两个执行计划中都出现了cost成本。说明两个参数值模式下都是使用的cbo。但执行计划步骤存在差异，在all_rows时执行计划中使用的是全表扫描，在参数为first_rows_10时执行计划却走了索引。我们看到两次执行返回的数据都是59120行，那么看一下test1表的数据量。
SQL> select count(*)from test1;
COUNT(*)
----------
72468
从查询结果看到test1这张表有72468条数据，上面测试的SQL返回的结果就有近6万条占了总数的百分之九十以上。这个时候oracle认为走全表扫描的成本低于走索引，所以在all_rows下执行计划走的是全表扫描。在first_rows_10的时候使用索引范围扫的原因是在该参数值模式下侧重点是cbo计算SQL的各路径成本值时以最快的响应速度返回前10行数据，这个参数值中的数值包含了1、10、100、1000设置相应的数值就以最快的响应速度返回前多少行。 这时候cbo选择的执行计划很有可能并不是成本值最低的执行计划，但是为了不违背cbo选择成本值最低的执行计划的原则，oracle会把能返回开头前n行记录的执行计划的cost设置得很低，会远远低于该执行计划的真实cost，这样既不违背cbo选取执行计划的原则，也实现了first_rows_n的含义。下面看一下该SQL在使用object_id列上的索引的实际cost为多少： 在all_rows参数值模式下对原SQL添加提示让其走索引查看cost。
SQL> select /*+ index(test1 ind_test1_obj_id) */* from test1 where object_id<60000;
59120 rows selected.
Elapsed: 00:00:01.07
Execution Plan
----------------------------------------------------------
Plan hash value: 1096053794
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 58305 | 5523K| 1016 (1)| 00:00:13 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST1 | 58305 | 5523K| 1016 (1)| 00:00:13 |
|* 2 | INDEX RANGE SCAN | IND_TEST1_OBJ_ID | 58305 | | 131 (0)| 00:00:02 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"<60000)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8820 consistent gets
0 physical reads
0 redo size
6773172 bytes sent via SQL*Net to client
43875 bytes received via SQL*Net from client
3943 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
59120 rows processed
这时候可以从执行计划中看到索引范围扫描的cost为131，加上回表的cost为1016。而在前面first_rows_10参数值模式下的执行计划索引范围扫的cost值为2，而加上回表cost也才到3。这是因为在first_rows_10下的执行计划是返回前10行，执行计划中的rows列的预估返回行数为11，所以预估出的cost会这么低。那么查看真实的执行计划呢。
上面是在first_rows_10下的真实执行计划，这时候可以看到在回表步骤的返回行数为59120，这与开始看到的预估的11行差距明显，所以在回表步骤的cost为3这是远远低于实际的cost的，那么他这个执行计划是怎么来的呢。我试着把原SQL改写成分页查询的方式显示前10行在all_rows下执行，发现执行计划和first_rows_10参数值模式下的原SQL执行计划有很多相似的地方。
两个执行计划都是使用索引范围扫描的方式然后回表的数据都是预估11行，而且这两步的cost值也都是相同的，我猜测在first_rows_10参数值模式下的查询与分页查询原理差不多，分页查询是在查到分页条数的时候停止查询，而first_rows_10模式下查是感觉就像是用分页查询出前n行数据再union all n行之后的数据。
二、OPTIMIZER_MODE=FIRST_ROWS
1、在test1表的owner列上创建一个全文索引。
SQL> CREATE INDEX IDx_T_owner2 ON Test1 (owner) INDEXTYPE IS CTXSYS.CONTEXT;
Index created.
2、在test1表的object_type列上创建一个普通索引。
SQL> create index ind_t_object_typ on test1(object_type);
Index created.
3、收集统计信息
SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'TEST1',estimate_percent=>100,cascade=>true,method_opt=>'for all columns size auto',no_invalidate=>false);
PL/SQL procedure successfully completed.
4、在first_rows下查看执行计划
SQL> select * from test1 where CONTAINS(OWNER, 'SYS', 1) > 0 AND OBJECT_tyPe='RULE' ORDER BY SCORE(1);
Elapsed: 00:00:00.06
Execution Plan
----------------------------------------------------------
Plan hash value: 419818458
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 219 | 13 (8)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| TEST1 | 1 | 219 | 13 (8)| 00:00:01 |
|* 2 | DOMAIN INDEX | IDX_T_OWNER2 | 1 | | 4 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_TYPE"='RULE')
2 - access("CTXSYS"."CONTAINS"("OWNER",'SYS',1)>0)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
11 recursive calls
0 db block gets
4037 consistent gets
0 physical reads
0 redo size
1610 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
5、在SQL文本中加入提示使sql在first_rows下走普通索引
SQL> select /*+ index(test1 ind_t_object_typ)*/* from test1 where CONTAINS(OWNER, 'SYS', 1) > 0 AND OBJECT_tyPe='RULE' ORDER BY SCORE(1);
Execution Plan
----------------------------------------------------------
Plan hash value: 2324842076
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 97 | 6 (17)| 00:00:01 |
| 1 | SORT ORDER BY | | 1 | 97 | 6 (17)| 00:00:01 |
|* 2 | TABLE ACCESS BY INDEX ROWID| TEST1 | 1 | 97 | 5 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IND_T_OBJECT_TYP | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("CTXSYS"."CONTAINS"("OWNER",'SYS',1)>0)
3 - access("OBJECT_TYPE"='RULE')
Statistics
----------------------------------------------------------
9 recursive calls
0 db block gets
21 consistent gets
0 physical reads
0 redo size
1610 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
6、在first_rows_n下执行计划
SQL> select * from test1 where CONTAINS(OWNER, 'SYS', 1) > 0 AND OBJECT_tyPe='RULE' ORDER BY SCORE(1);
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2324842076
-------------------------------------------------------------------------------------------------