Optimizer Modes   «Prev  Next»

Lesson 1

SQL Optimizer Modes for Tuning Oracle SQL

In the next two modules, we will explore the cost- and rule-based optimizer for Oracle SQL and learn about when they are appropriate for specific types of SQL statements. Before we get into detail about the specifics of each mode, we will start by exploring the Oracle defaults for SQL optimizers and understand why the defaults can have a tremendous impact on database performance.
By the time you finish this module, you should be able to:
  1. Describe each optimizer mode
  2. View and reset the default optimizer mode
  3. Override the default optimizer mode for a query

SQL Queries

Please note that in this module I will refer to SQL queries. I say queries because SQL that changes rows (INSERT, UPDATE, DELETE) is not significantly impacted by the choice of SQL optimizer mode.
We will start our discussion with an overview of the optimizer modes.

Rule-based Optimization (RBO) Obsolescence

RBO as a functionality is no longer supported. RBO still exists in Oracle 10g Release 1, but is an unsupported feature. No code changes have been made to RBO and no bug fixes are provided. Oracle supports only the query optimizer, and all applications running on Oracle Database 10g Release 1 (10.1) should use that optimizer.

OPTIMIZER_MODE Parameter Values

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.
CHOOSE This parameter value has been desupported.
RULE This parameter value has been desupported