Optimizer Modes   «Prev  Next»

Lesson 2 Overview of the Optimizer Modes
ObjectiveDescribe each Optimizer Mode

Most Effective Access Path

Overview of Oracle Optimizer Modes

Since the Oracle database was first developed, its design has provided SQL developers with methods to always ensure the most effective access path to data. The solution is the optimzer, or optimizer modes, which determine the best way to execute each SQL statement. Since the basic structure of SQL provides many different ways to accomplish the same task, the optimizer’s role is to examine the different options for execution and formulate an execution plan for getting the task done most efficiently. Here is a review of the different types of optimization used within Oracle.

Rule-based optimization

Rule-based optimization relies on information in the Oracle data dictionary and a set of general rules (called heuristics)[1] to determine the access path to the data.

Rule-based optimization combined with data dictionary and rules gives you the best access path
Rule-based optimization combined with data dictionary and rules gives you the best access path

Cost-based optimization

Cost-based optimization relies on statistics that are created with the SQL ANALYZE command to examine the characteristics of the table and index data to determine the access path to the data.

Cost-based optimization creates statistics using SQL Analyze commands to obtain best access path
Cost-based optimization creates statistics using SQL Analyze commands to obtain best access path

Finally, there is the “choose” optimizer mode. This is a third default optimizer mode for Oracle. When the optimizer_mode=choose parameter is set in the init.ora file, Oracle will make a decision about which optimizer mode to use, based on the presence of Oracle statistics. If none of the tables in the query have been analyzed to generate statistics, Oracle will invoke the rule-based optimizer. If any of the tables in a query contains statistics, Oracle will invoke the cost-based optimizer. The problem arises when only some of the tables in a query contain statistics.

Choose optimization determines which optimization mode to use based on Oracle statistics
Choose optimization determines which optimization mode to use based on Oracle statistics

With choose, if Oracle detects statistics in any table in a multi-table query, Oracle will estimate statistics for the other tables at execution time, causing the SQL to run very slowly.

Choosing the correct optimizer

When Oracle only used the rule-based optimizer, ensuring quick access to data was relatively easy.
However, a problem developed when Oracle introduced the cost-based optimizer. As you may remember from prior modules, some SQL will run faster with a rule-based execution plan while other SQL will run faster with cost-based execution plans. In many cases, the rule-based optimizer may perform faster execution than the newer, cost-based optimizer. The DBA should always EXPLAIN every SQL statement before moving it into a production environment to see which is the fastest optimizer to invoke for any given query. We will now take a close look at how we can set and alter these optimizer modes.

SQL Optimizer Modes

Before you go ahead, click the link below to test your knowledge of optimization modes.
SQL Optimizer Modes

[1] heuristic: In computer science, artificial intelligence, and mathematical optimization, a heuristic is a technique designed for solving a problem more quickly when classic methods are too slow, or for finding an approximate solution when classic methods fail to find any exact solution. s