The Oracle Query 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
• 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

Optimizer Statistics

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
• 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

Query Optimizer Documentation

The job of the Optimizer is to determine the best execution plan for each SQL statement. An overview of the Optimizer, uses of the Optimizer, Optimizer components, access paths, Optimizer statistics and Optimizer hints can be found in Database Concepts. More documentation specific to query Optimizer fundamentals, Optimizer statistics and Optimizer controls can be found in the SQL Tuning Guide. Documentation specific to the Optimizer is also available in the Database Performance Tuning Guide.

SQL Tuning Guide

Database Performance Tuning Guide

Learn more about Oracle Query Optimization by exploring data sheets, presentations, online tutorials, demos, and more!