Why to choose an optimizer goal? (oracle)
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
- 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
- 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.