Oracle优化器的optimizer_mode参数

optimizer_mode参数

  optimizer_mode是oracle 11g的一个优化器参数，在某些时候可以影响优化器的行为，是个不可忽视的细节参数。

---

SQL> show parameter optimizer;
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 11.2.0.4
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
optimizer_use_invisible_indexes boolean FALSE
optimizer_use_pending_statistics boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE

此参数的简单介绍如下，

  oracle优化器在解析sql前先查看这个参数的值。参数optimizer_mode 决定取出多少行后开始向用户返回第一批数据，当值为all_rows时，获取完所有的数据后再开始向用户返回数据。
  这么看来，貌似感觉和执行计划没多大关系，但是oracle优化器在选择执行计划的时候却是会考虑这个参数的，而这里主要体现在btree索引的特性。

Let's make a test

  我们来做一个测试。执行一个sql语句，从t1表中取出object_id列为1-3000的数据，结果以object_id列的值排序。测试情况如下。

SQL> show parameter optimizer_mode;
optimizer_mode string ALL_ROWS
SQL> select * from t1 where object_id between 1 and 3000 order by object_id;
2998 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2148421099
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2959 | 283K| | 404 (1)| 00:00:05 |
| 1 | SORT ORDER BY | | 2959 | 283K| 400K| 404 (1)| 00:00:05 |
|* 2 | TABLE ACCESS FULL| T1 | 2959 | 283K| | 336 (1)| 00:00:05 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("OBJECT_ID"<=3000 AND "OBJECT_ID">=1)
Statistics
----------------------------------------------------------
          1 recursive calls
          0 db block gets
       1235 consistent gets
          0 physical reads
          0 redo size
     120248 bytes sent via SQL*Net to client
        523 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          1 sorts (memory)
          0 sorts (disk)
       2998 rows processed

  我们可以看到，当前optimizer_mode的值为all_rows，优化器选择了全表扫描TABLE ACCESS FULL。那么我们一般的认识是，数据量总量较小或者查询量的总量占比较大的情况下，优化器认为全表扫描的性能较索引检索性能更优。

接下来修改当前会话的optimizer_mode的值为first_rows_10

SQL> alter session set optimizer_mode='first_rows_10';
Session altered.

然后再次测试刚才的SQL语句

SQL> select * from t1 where object_id between 1 and 3000 order by object_id;
2998 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1057374866
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 1176 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 12 | 1176 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T1_1 | 2959 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID">=1 AND "OBJECT_ID"<=3000)
Statistics
----------------------------------------------------------
          1 recursive calls
          0 db block gets
         53 consistent gets
          7 physical reads
          0 redo size
     276790 bytes sent via SQL*Net to client
        523 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
       2998 rows processed

呃，，我们发现，调整完这个参数后，优化器立马就选择了索引。

那么，怎么去解释呢？
  这里就需要考虑到btree索引的特性了，因为索引的有序性，也就是获取到的数据是根据索引列排好序的。
  因此，当进行全表扫描时，由于列值杂乱，所以必须等到所有的数据全部获取完毕后才能进行排序，再开始返回第一批数据；而通过索引返回的数据都是有序的，取到第十行时就可以直接返回给用户。
  由于索引的这个小特性，加上optimizer_mode参数对数据操作的影响，间接地影响了优化器的选择行为。