Why to choose an optimizer goal?

Optimizer is a software or an algorithmic code which has the intelligence to decide the path to access the data in an efficient way in oracle database.

We have options to choose optimizer mode to prioritize the utilization of resources to serve their own purpose.

There are two options in cost based optimizer mode

  1. ALL_ROWS –> Better throughput –>Used for batch jobs –>Primary purpose is to complete batch of multiple sql statement to get the final report in shortest possible time –> Return all the rows mode
  2. FIRST_ROWS_N –> Better response time –>Used for interactive applications –> Primary purpose is to get first n row of result set of sql statement (i.e) n =1,10,1000 … can be positive whole number–>Optimizer estimates the number of rows that will be returned by completely analyzing the 1st join order –> Minimize the resources required to return fraction of total dataset the query is supposed to fetch.

Example:

SQL> show parameter optimizer_mode

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode                       string      ALL_ROWS

By default, ALL_ROWS is used in every databases.But we can set the optimizer mode at session level with first_rows_1000, first_rows_100, first_rows_10 and first_rows_1

FIRST_ROWS,RULE and CHOOSE mode are deprecated in earlier releases and no longer used

SQL> alter session set optimizer_mode = first_rows_100;

Session altered.

Elapsed: 00:00:00.00
SQL> alter session set optimizer_mode = first_rows_10000;
ERROR:
ORA-00096: invalid value FIRST_ROWS_10000 for parameter optimizer_mode, must be
from among first_rows_1000, first_rows_100, first_rows_10, first_rows_1,
first_rows, all_rows, choose, rule

FIRST_ROWS behaviour in a select query :

I provide a hint FIRST_ROWS(1000) to force optimizer to give preference and retrieve the first 1000 rows as fast as possible and the preceding rows are retrieved later!

The execution plan says that the optimizer used index range scan + table access by rowid batched to satisfy this condition

SQL> exec dbms_stats.gather_table_stats('','t');

PL/SQL procedure successfully completed.

SQL> select /*+ FIRST_ROWS(1000) */ name from t where id < 100000;

99999 rows selected.

Elapsed: 00:00:00.25

Execution Plan
----------------------------------------------------------
Plan hash value: 3571126032

--------------------------------------------------------------------------------
--------------------

| Id  | Operation                           | Name         | Rows  | Bytes | Cos
t (%CPU)| Time     |

--------------------------------------------------------------------------------
--------------------

|   0 | SELECT STATEMENT                    |              |  1000 |  8000 |
 6   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T            |  1000 |  8000 |
 6   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN                  | SYS_C0010315 |       |       |
 3   (0)| 00:00:01 |

--------------------------------------------------------------------------------
--------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"<100000)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      13754  consistent gets
          0  physical reads
          0  redo size
    3172665  bytes sent via SQL*Net to client
      73877  bytes received via SQL*Net from client
       6668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      99999  rows processed

ALL_ROWS hint behaviour in a select query! Here the optimizer went for a full table scan to retrieve complete set of rows as a whole and not giving preference to first n rows.

SQL> select /*+ ALL_ROWS */ name from t where id < 100000;

99999 rows selected.

Elapsed: 00:00:00.29

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 10001 | 80008 |     9   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    | 10001 | 80008 |     9   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID"<100000)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      11321  consistent gets
       2638  physical reads
          0  redo size
    1773824  bytes sent via SQL*Net to client
      73877  bytes received via SQL*Net from client
       6668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      99999  rows processed

From above two behaviour of optimizer,we observe different Plan hash value for the same query which is syntactically and semantically same. But the end goals are different for optimizer modes which change the execution plan behaviour of optimizer!

We do see the execution time difference between FIRST_ROWS(Elapsed: 00:00:00.27) and ALL_ROWS(Elapsed: 00:00:00.29) because of an index usage in FIRST_ROWS which fetched first 1000 rows faster.

Published by kishan

You will know about me.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: