| Lesson 2 | Overview of the Optimizer Modes |
| Objective | Describe 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.
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.
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.
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.
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
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