Oracle Optimizer - Some Interesting Insight

 This is Short Post about 

1. what is Oracle Optimizer .
2. How it works 
3. How we can manipulate optimizer for session or Individual Query 

A SQL statement can be executed in many different ways, such as

1. full table scans,
2. index scans,
3. nested loops, and
4. hash joins.

 What is Query optimizer - The query optimizer determines the most efficient way to execute a SQL statement after considering many factors related to the objects referenced and the conditions specified in the query.  

Note:The optimizer might not make the same decisions from one version of Oracle to the next. In recent versions, the optimizer might make different decisions, because better information is available.

The output from the optimizer is a plan that describes an optimum method of execution. The Oracle server provides query optimization.

For any SQL statement processed by Oracle, the optimizer performs the operations listed in below table

Operation	Description
Evaluation of expressions and conditions	The optimizer first evaluates expressions and conditions containing constants as fully as possible.
Statement transformation	For complex statements involving, for example, correlated subqueries or views, the optimizer might transform the original statement into an equivalent join statement
Choice of optimizer goals	The optimizer determines the goal of optimization.
Choice of access paths	For each table accessed by the statement, the optimizer chooses one or more of the available access paths to obtain table data
Choice of join orders	For a join statement that joins more than two tables, the optimizer chooses which pair of tables is joined first, and then which table is joined to the result, and so on.

You can influence the optimizer's choices by

setting the optimizer goal, and

gathering representative statistics for the query optimizer.

The optimizer goal is either throughput or response time

The application designer can use hints in SQL statements to instruct the optimizer about how a statement should be executed.

What should be Optimizer Goal -  By default, the goal of the query optimizer is the best throughput

Throughput - This goal chooses the least amount of resources necessary to process all rows by statement.

Response Time - This Goal uses the least amount of resources necessary to process the first row accessed by a SQL statement.

We can choose the optimizer Goal based on our need -

For applications performed in batch, such as Oracle Reports applications throughput is best option 

For interactive applications, such as Oracle Forms applications or SQL*Plus queries, response time is best option.

How to Set the Goal of Optimizer 

OPTIMIZER_MODE initialization parameter establishes the default behavior for choosing an optimization approach for the instance

Value	Description
ALL_ROWS	The optimizer uses a cost-based approach for all SQL statements in the session regardless of the presence of statistics and optimizes with a goal of best throughput (minimum resource use to complete the entire statement). This is the default value.
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. Note: Using heuristics sometimes leads the query optimizer to generate a plan with a cost that is significantly larger than the cost of a plan without applying the heuristic. FIRST_ROWS is available for backward compatibility and plan stability; use FIRST_ROWS_n instead.

Command to Set the MODE for Session

ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_1;

ALTER SESSION SET OPTIMIZER_MODE =ALL_ROWS;

To specify the goal of the query optimizer for an individual SQL statement, we can use  hints  Any of these hints in an individual SQL statement can override the OPTIMIZER_MODE initialization parameter for that SQL statement

Hints let you make decisions usually made by the optimizer