techblog 

Our main production database is mostly OLTP but we have a set of crucial batch jobs as well. A good recommendation for OLTP systems is to set your optimizer_mode to be FIRST_ROWS_X where is X is a number, we have it currently set to 10. This is basically saying for any particular query a user is only interested in seeing the first few results and will not page through screen fulls of data. Therefore the optimizer works out the path to get the first few rows back quickest, even though overall execution time may be slower. On the other hand batch jobs tend to have to process all rows so setting the optimizer_mode to be ALL_ROWS may be of benefit. What I never realised was the different optimizer modes give differing values for the cost of a full table scan.

SQL> alter session set optimizer_mode='FIRST_ROWS_10';

SQL> explain plan for select * from users.test;

SQL>  @?/rdbms/admin/utlxpls 

 PLAN_TABLE_OUTPUT 

Plan hash value: 488834895
————————————————————————————-
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time      |
|   0 | SELECT STATEMENT   |          |10 |  1570 |     2   (0)| 00:00:01     |
|   1 |   TABLE ACCESS FULL| TEST  |    10 |  1570 |     2   (0)| 00:00:01     |
————————————————————————————-

SQL> alter session set optimizer_mode = ‘ALL_ROWS’;
SQL> select * from users.test;

SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
Plan hash value: 488834895
—————————————————————————–
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT  |         |  6034K|   903M| 25705   (2)| 00:05:09 |
|   1 |   TABLE ACCESS FULL| TEST |    6034K|   903M| 25705   (2)| 00:05:09 |
—————————————————————————–

It is a big difference in terms of cost but of course no matter what optimizer mode you are using you still have to perform the same amount of work if you are doing the same operation. To be clear, a full table scan of the same table will take the same amount of time (assuming the same level of caching) no matter what your optimizer is set to.

This can have the effect of favouring a full table scan when in fact index access would be cheaper.