Control: Inside the Oracle Optimizer
By Donald K. Burleson
This article has the following sections:
So let's start by examining the CBO
optimizer modes and the Oracle parameters that influence the CBO.
PART 1 - CBO Parameters
The CBO is influenced by many configuration
settings. Your settings for important CBO parameters can have a
dramatic impact of CBO performance, and this is the place to start
when exploring the CBO. Let's start by choosing your CBO
optimizer_mode and then examine other
important CBO parameters.
The CBO and optimizer modes.
In Oracle9i Database there are four optimizer modes, all
determined by the value of the
parameter: rule, choose, all_rows,
and first_rows. The
modes reflect the obsolete rule-based optimizer, so we will focus on
the CBO modes here.
The all_rows optimizer mode is designed to minimize computing
resources and it favors full-table scans. Index access
(first_rows) adds additional I/O overhead, but they return rows
faster, back to the originating query.
The optimizer mode can be set at the
system-wide level, for an individual session, or for a specific SQL statement:
alter system set optimizer_mode=first_rows_10;
select /*+ first_rows(100) */ from student;
We need to start by defining what is the
"best" execution plan for a SQL statement. Is the best execution plan
the one that begins to return rows the fastest, or is the best
execution plan the one that executes with the smallest amount of
computing resources? Of course, the answer depends on the processing
needs of your database.
For testing, you can quickly test the effect of
another optimizer parameter value at the query level without using an
“alter session” command, using the new
opt_param SQL hint:
select /*+ opt_param('optimizer_mode','first_rows_10')
*/ col1, col2 . . .
select /*+ opt_param('optimizer_index_cost_adj',20)
*/ col1, col2 . .
The all_rows optimizer mode is designed to minimize computing
resources and it favors full-table scans. Indexes add
additional I/O overhead, but they return rows faster, back to the
Oracle full-table scan
Let's take a simple example. Assume the
region = 'south'
If the best execution plan is the one that
starts to return rows the fastest, a concatenated index on
could be used to immediately start delivering table rows in their
proper order, even though excess I/O will be required to read the
nonadjacent data blocks.
Let's assume that this execution plan starts
delivering results in .0001 seconds and requires 10,000
But what if your goal is to minimize computing resources? If this SQL
is inside a batch program, then it is not important to start
returning rows quickly, and a different execution plan would take
fewer resources. In this example, a parallel full-table scan followed
by a back-end sort will require less
machine resources and less I/O because blocks do not have to be reread
to pull the data in sorted order
In this example, we expect the result to take longer to deliver (no
rows until the sort is complete), but we will see far less I/O because
blocks will not have to be re-accessed to deliver the rows in presorted order.
Let's assume that this execution plan delivers the result in 10
seconds with 5,000 db_block_gets.
Oracle offers several optimizer modes that
allow you to choose your definition of the "best" execution plan for
This CBO mode will return rows as soon
as possible, even if the overall query runs longer or consumes more
computing resources than other plans. The
optimizer_mode usually involves
choosing an index scan over a full-table scan because index access
will return rows quickly. Because the
mode favors index scans over full-table scans, the
first_rows mode is more
appropriate for OLTP systems where the end user needs to see small result sets as quickly as possible.
This CBO mode ensures that the overall
computing resources are minimized, even if no rows are available
until the entire query has completed. The
access method often favors a parallel full-table scan over a
full-index scan, and sorting over presorted retrieval via an index.
Because the all_rows
mode favors full-table scans, it is best suited for data warehouses,
decision-support systems, and batch-oriented databases where
intermediate rows are not required for real-time viewing.
This Oracle9i Database optimizer mode enhancement optimizes
queries for a small, expected return set. The values are
first_rows_1, first_rows_10, first_rows_100,
The CBO uses the n in first_rows_n
as an important driver in determining cardinalities for query result
sets. By telling the CBO, a priori, that we only expect a certain
number of rows back from the query, the CBO will be able to make a
better decision about whether to use an index to access the table
The rule-based optimizer (RBO) is the archaic optimizer mode from
the earliest releases of Oracle Database. The rule-based optimizer
has not been updated in nearly a decade and is not recommended for
production use because the RBO does not support any new features of Oracle since 1994 (such as bitmap indexes, table partitions, and
While the optimizer_mode
is the single most important factor in invoking the cost-based
optimizer, there are other parameters that influence the CBO behavior.
Let's take a quick look at these parameters.
Oracle parameters that influence the
CBO. While the optimizer_mode
parameter governs the global
behavior of the CBO, there are many other
Oracle parameters that have a great impact on CBO behavior. Because of
the power of the CBO, Oracle provides several system-level parameters
that can adjust the overall behavior of the CBO. These adjustment
parameters generally involve the choice of using an index versus doing
a full-table scan, and the CBO's choice of table join methods.
However, Oracle does not recommend changing
the default values for many of these CBO setting because the changes
can affect the execution plans for thousands of SQL statements. Here
are the major optimizer parameters:
This parameter alters the costing
algorithm for access paths involving indexes. The smaller the value,
the lower the cost of index access.
This parameter tells Oracle how much of your index is likely to be
in the RAM data buffer cache. The setting for
the CBO's decision to use an index for a table join (nested loops)
or to favor a full-table scan.
When this parameter is set to a high value, the
CBO recognizes that scattered (multiblock) reads may be
less expensive than sequential reads. This makes the CBO friendlier
to full-table scans. (deprecated in 11g and beyond)
- hash_area_size (if
not using pga_aggregate_target,
sga_target or memory_target) _
The setting for hash_area_size
parameter governs the propensity of the CBO to favor hash joins over
nested loop and sort merge table joins.
- sort_area_size (if
not using pga_aggregate_target) _
influences the CBO when deciding whether to perform an index access
or a sort of the result set. The higher the value for
sort_area_size, the more likely
that a sort will be performed in RAM, and the more likely that the CBO will favor a sort over presorted index retrieval.
The idea optimizer settings depend on your
environment and are heavily influenced by your system's costs for
scattered disk reads versus sequential disk reads.
Listing 1 contains a great script
you can use to measure these I/O costs on your database.
LISTING 1: optimizer_index_cost_adj.sql
col c1 heading 'Average Waits|forFull| Scan Read I/O' format 9999.999
col c2 heading 'Average Waits|for Index|Read I/O' format 9999.999
col c3 heading 'Percent of| I/O Waits|for Full Scans' format 9.99
col c4 heading 'Percent of| I/O Waits|for Index Scans' format 9.99
col c5 heading 'Starting|Value|for|optimizer|index|cost|adj' format 999
a.total_waits /(a.total_waits + b.total_waits) c3,
b.total_waits /(a.total_waits + b.total_waits) c4,
(b.average_wait / a.average_wait)*100 c5
a.event = 'db file scattered read'
b.event = 'db file sequential read'
Now that we understand the CBO parameters,
let's look at how we can help the CBO make good execution-plan
decisions by providing the CBO with information about our schema.
The Ion tool is
the easiest way to analyze Oracle performance and Ion
allows you to spot hidden performance trends.
Get the Complete
Oracle SQL Tuning Information
The landmark book
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
for 30% off directly from the publisher.