Oracle Query Optimizer

 

Optimizer

The Optimizer is one of the most fascinating components of the Oracle Database, since it is essential to the processing of every SQL statement. 

The Optimizer determines the most efficient execution plan for each SQL statement based on the structure of the query, the available statistical information about the underlying objects, and all the relevant optimizer and execution features.

Adaptive Query Optimization

Adaptive Query Optimization is a set of capabilities that enable the optimizer to make run-time adjustments to execution plans and discover additional information that can lead to better statistics. 

This approach is extremely helpful when existing statistics are not sufficient to generate an optimal plan. 

There are two distinct aspects in adaptive query optimization; adaptive plans, which focuses on improving the initial execution of a query and adaptive statistics, which provide additional information to improve subsequent executions.

SQL Plan Management

SQL plan management is a mechanism that enables the optimizer to automatically manage execution plans, ensuring that the database uses only known or verified plans.

In SQL plan management, the optimizer has the following main objectives:

• Identify repeatable SQL statements
• 
  

In SQL plan management, the optimizer has the following main objectives:

• Maintain plan history, and possibly SQL plan baselines, for a set of SQL statements
• Detect plans that are not in plan history
• Detect potential better plans that are not in SQL plan pipeline