Optimizer Modes(CHOOSE,ALL_ROWS,FIRST_ROWS,FIRST_ROWS(n),RULE)

What are Optimizer Modes available in ORACLE?

Within two Optimizers RBO and CBO, Oracle provides several optimizer modes. An optimizer mode may be set for the entire database, or it may be set for a specific Oracle session.

The optimizer mode under which the database operates is set via the initialization parameter OPTIMIZER_MODE. The possible optimizer modes are as follows:

Value	Description
CHOOSE	The optimizer chooses between a cost-based approach and a rule-based approach, depending on whether statistics are available. This is the default value.
ALL_ROWS	Minimum resource use to complete the entire statement. ALL_ROWS attempt to optimize the query to get the very last row as fast as possible. We can use ALL_ROWS when user want to process all the rows before we see the output. Mostly used in OLAP. ALL_ROWS use less resource when compared to FIRST_ROWS.
FIRST_ROWS_n	The optimizer uses a cost-based approach, regardless of the presence of statistics, and optimizes with a goal of best response time to return the first n number of rows; n can equal 1, 10, 100, or 1000.
FIRST_ROWS	The optimizer uses a mix of cost and heuristics to find a best plan for fast delivery of the first few rows. FIRST_ROWS attempt to optimize the query to get the very first row back to the client as fast as possible.  We can use FIRST_ROWS when user want to see the first few rows immediately. It is mostly used in OLTP, some reporting environment.
RULE	The optimizer chooses a rule-based approach for all SQL statements regardless of the presence of statistics.

• The following statement in an initialization parameter file changes the goal of the CBO for all sessions of the instance to best response time:

OPTIMIZER_MODE = FIRST_ROWS_1

• The following SQL statement changes the goal of the CBO for the current session to best response time:

ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_1;

Optimizer SQL Hints

To specify the goal of the CBO for an individual SQL statement, use one of the hints in the following list. Any of these hints in an individual SQL statement can override the OPTIMIZER_MODE initialization parameter for that SQL statement.

• ALL_ROWS
• FIRST_ROWS
• FIRST_ROWS(n), where n equals any positive integer
• CHOOSE
• RULE

****ALL_ROWS****

The ALL_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best throughput (that is, minimum total resource consumption).

Syntax: /*+ ALL_ROWS */ 

SQL> SELECT /*+ ALL_ROWS */ EMP_NO, ENAME, DEPTNO

From EMPLOYEES Where DEPTNO=10;

****FIRST_ROWS****

The FIRST_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best response time (minimum resource usage to return first row).

Syntax: /*+ FIRST_ROWS */ 

SQL> SELECT /*+ FIRST_ROWS */ EMP_NO, ENAME, DEPTNO

 From EMPLOYEES Where DEPTNO=10;

****FIRST_ROWS(n)****

You use the FIRST_ROWS(n) hint in cases where you want the first number (n) of rows in the shortest possible time. 

Use the FIRST_ROWS(n) hint when you need only the first few hits of a query. When you need the entire result set, do not use this hint as it might result in poor performance

Syntax: /*+ FIRST_ROWS(n) */

SQL> SELECT /*+ FIRST_ROWS (5) */ EMP_NO, ENAME, DEPTNO

From EMPLOYEES;

****CHOOSE****

The CHOOSE hint causes the optimizer to choose between the rule-based approach and the cost-based approach for a SQL statement based on the presence of statistics for the tables accessed by the statement. If the data dictionary contains statistics for at least one of these tables, the optimizer uses the cost-based approach and optimizes with the goal of best throughput. If the data dictionary contains no statistics for any of these tables, the optimizer uses the rule-based approach.

Syntax: /*+ CHOOSE */

SQL> SELECT /*+ CHOOSE */ EMP_NO, ENAME, DEPTNO

From EMPLOYEES Where DEPTNO=10;

****RULE****

The RULE hint explicitly chooses rule-based optimization for a statement block. It also makes the optimizer ignore any other hints specified for the statement block.

Syntax: --+ RULE 

SQL> SELECT --+ RULE EMP_NO, ENAME, DEPTNONO

From EMPLOYEES Where DEPTNO=10;

The RULE hint, along with the rule-based approach, may not be supported in future versions of Oracle.

Let’s understand ALL_ROWS and FIRST_ROWS with the help of below example:

SQL> CREATE TABLE TEST_OBJECT AS Select * From ALL_OBJECTS;
Table created.

SQL> CREATE Index IND_Type ON TEST_OBJECT(OBJECT_TYPE);
Index created.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('MYUSER','TEST_OBJECT');
PL/SQL procedure successfully completed.
 
SQL> SET AUTOTRACE TRACEONLY EXP;
SQL> SELECT * FROM TEST_OBJECT Where OBJECT_TYPE='JAVA CLASS';
Execution Plan
----------------------------------------------------------
Plan hash value: 2051129056

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1001 | 94094 | 10 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST_OBJECT | 1001 | 94094 | 10 (0)| 00:00:01 |
--------------------------------------------------------------------------

As you see above, optimizer has not used Index we created on this table rather it has used Full Table Scan(ALL_ROWS).

Now use FIRST_ROWS hint:

SQL> SELECT /*+ FIRST_ROWS*/ * FROM TEST_OBJECT Where OBJECT_TYPE='JAVA CLASS';
Execution Plan
----------------------------------------------------------
Plan hash value: 2051129156
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14662 | 1345K| 536 (1)| 00:00:07 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_OBJECT | 14662 | 1345K| 536 (1)| 00:00:07 |
|* 2 | INDEX RANGE SCAN | IND_TYPE | 14662 | | 43 (3)| 00:00:01 |
---------------------------------------------------------------------------------------

When used with FIRST_ROWS, optimizer has used an index. Optimizer use the Index scan when we use the FIRST_ROWS hint. Because, it uses the index scan and reads first few rows to display on the screen, then it reads the rest of the data. So, Oracle delivered first few rows quickly using index and later delivering the rest.

So FIRST_ROWS hint looks faster, but it is really not. In this example index scan(FIRST_ROWS), the cost is high when we compared to ALL_ROWS (full table scan).

The difference is in cost, although the response time (partial) of second query was faster but resource consumption was high.

In most of OLTP systems, where users want to see data fast on their screen, this mode of optimizer is used. So, Cost of the query is not the only criteria for choosing the execution plan. It chooses plan which helps in fetching first rows fast.

ALL_ROWS

In simple terms, it means better throughput. In above example of FIRST_ROWS, we have already seen efficiency of ALL_ROWS. While FIRST_ROWS may be good in returning first few rows, ALL_ROWS ensure the optimum resource consumption and throughput of the query.

ALL_ROWS consider both index scan and full scan and based on their contribution to the overall query, it uses them. If Selectivity of a column is low, optimizer may use index to fetch the data (for example 'where EMPNO=202377'), but if selectivity of column is quite high ('where DEPTNO=20'), optimizer may consider doing Full table scan. ALL_ROWS (FULL TABLE SCAN) is Good for OLAP system, where work happens in batches/procedures.

Get involved and leave your Comments in the Box Below. The more people get involved, the more we all benefit. So, leave your thoughts before you leave the page.