Optir_mode parameter

_optimizer_mode is an optimizer parameter of oracle 11g. It can affect the behavior of the optimizer in some cases, and it is a detail parameter that can not be ignored.

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

A brief description of this parameter is given below.

_oracle optimizer looks at the value of this parameter before parsing sql. The parameter optimizer_mode decides how many rows to take out and then returns the first batch of data to the user. When the value is all_rows, all data is retrieved before returning to the user.
_It seems that there is little relationship between feeling and execution plan, but oracle optimizer takes this parameter into account when choosing execution plan, which is mainly embodied in the characteristics of btree index.

Let's make a test

Let's do a test. Execute an sql statement to extract the object_id data listed in 1-3000 from the t1 table, and the results are sorted by the value of the object_id column. The test results are as follows.

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

_We can see that the current optimizer_mode value is all_rows, and the optimizer selected the full table scan TABLE ACCESS FULL. So we generally know that when the total amount of data is small or the total amount of queries is large, the optimizer thinks that the performance of full table scanning is better than that of index retrieval.

Next, change the optimizer_mode value of the current session to first_rows_10

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

Then test the SQL statement again.

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

Well, we found that after adjusting this parameter, the optimizer immediately chose the index.

So, how to explain it?
_Here we need to consider the characteristics of btree index, because the index is ordered, that is, the data obtained is ordered according to the index column.
_Therefore, when scanning a full table, because the column values are cluttered, it is necessary to wait until all the data have been obtained before sorting, and then start returning the first batch of data; and the data returned through the index are orderly, and can be returned directly to the user when the tenth row is taken.
Owing to this small feature of index and the influence of optimizer_mode parameter on data operation, the selection behavior of optimizer is indirectly affected.