The Oracle SQL Optimizers
Oracle Tips by Burleson Consulting
One of the first things the Oracle Remote DBA looks at
is the default optimizer mode for their database. There are two
classes of optimizer modes: the rule-based optimizer (RBO) and the
cost-based optimizer (CBO). The Oracle init.ora parameters
offer four values for the optimizer_mode parameter.
optimizer_mode = RULE
The first, and oldest optimizer mode is RULE.
Under the rule-based optimizer, Oracle uses heuristics from the data
dictionary in order to determine the most effective way to service an
Oracle query and translate the declarative SQL command into an actual
navigation plan to extract the data. In many pre-Oracle8i systems,
rule-based optimization is faster than cost-based. In fact, Oracle
Applications used rule-based optimization until release 11i.
optimizer_mode = FIRST_ROWS
This is a cost-based optimizer mode that will
return rows as soon as possible, even if the overall query runs longer
or consumes more resources. The FIRST_ROWS optimizer mode usually
involves choosing a full index scan over a parallel full table scan.
Because the FIRST_ROWS mode favors index scans over full table scans,
the FIRST_ROWS mode is most appropriate for inline systems where an
end user wants to see some results as quickly as possible.
optimizer_mode = ALL_ROWS
This is a cost-based optimizer mode that
ensures that the overall query time is minimized, even if it takes
longer to receive the first row. This usually involves choosing a
parallel full table scan over a full index scan. Because the ALL_ROWS
mode favors full table scans, the ALL_ROWS mode is best suited for
batch-oriented queries where intermediate rows are not required for
To illustrate the difference between ALL_ROWS
and FIRST_ROWS with an oversimplistic example, consider the following
This query can be serviced in two ways:
full table scan in parallel and sort the rows in the TEMP
tablespace. For the sake of illustration, let's assume that this
execution plan produces the fastest overall execution time and
minimal use of resources, as shown in Figure 11-1.
Figure 11-104: Perform a full table scan and a
Figure 11-105:Perform an index retrieval (FIRST_ROWS)
optimizer_mode = CHOOSE
Oracle's default optimizer mode is called CHOOSE.
In the CHOOSE optimizer mode, Oracle will execute the rule-based
optimizer if there are no statistics present for the table, or execute
the cost-based optimizer if statistics are present. The danger with
using the CHOOSE optimizer mode is in cases where one Oracle table in
a complex query has statistics and the other tables do not have
statistics. When only some tables contain statistics, Oracle will use
the cost-based optimization and estimate statistics (by sampling 5,000
rows) for the other tables in the query at runtime. This can cause
significant slowdown in the performance of the individual query. Be
careful when using the CHOOSE option. When you give Oracle the ability
to choose the optimizer mode, Oracle will favor the cost-based
approach if any of the tables in the query have statistics.
(Statistics are created with the analyze table command.) For
example, if a three-table JOIN is specified in CHOOSE mode and
statistics exist for one of the three tables, Oracle will decide to
use the cost-based optimizer. When this happens, the Oracle CBO will
inspect the num_rows column of the Remote DBA_tables view. If num_rows is
null, Oracle still estimates statistics based on a 5,000-row sample.
If the num_rows column is zero, Oracle will not perform a runtime
table analysis. In short, if Oracle analyzes the table at runtime,
this action will dramatically slow down the query.
Also, if a cost-based query is issued (i.e.,
with a hint) and there are no statistics of any tables or indexes, the
CBO will choose a driving table—just like the RBO. However, the
driving table for the CBO is the first table in the FROM clause, the
exact opposite of the RBO.
Disadvantages of Optimizer Modes
Now, let's talk about each of these default
modes and observe their relative advantages and disadvantages.
There are major shortcomings in both major
optimization modes. The existence of hints indicates that the Oracle
optimizers do not always make the most effective execution decision.
CAUTION: The use of any SQL hint except the RULE
hint forces the cost-based optimizer to be invoked. Hence, make sure
you have analyzed your tables and indexes prior to using any hints.
Rule-based shortcomings Often chooses
the wrong index to retrieve rows
Cost-based shortcomings Often performs
unnecessary full tables scans, especially when more than three
tables are being joined
The tuning of rule-based SQL is quite different
from tuning cost-based SQL. With the rule-based optimizer, the
indexing of tables and order of clauses within the SQL statement
control the access path in rule-based optimization. The cost-based
optimizer automatically determines the most efficient execution path,
and the programmer is given hints that can be added to the query to
alter the access path.
Remember, the foremost tenet of Oracle SQL
tuning is avoiding an unnecessary large-table full table scan. One of
the hallmarks of an inefficient SQL statement is the failure of the
SQL statement to use all of the indexes that are present within the
Oracle database in order to speed up the query.
Of course, there are times when a full table
scan is appropriate for a query, such as when you are doing aggregate
operations such as a sum or an average, and the majority of the rows
(greater than 40 percent for row-sequenced tables and greater than 7
percent for unsequenced tables) within the Oracle table must be read
to get the query results. The task of the SQL tuning expert is to
evaluate each full table scan and see if the performance can be
improved by adding an index.
Most Oracle SQL queries will only be retrieving
a small subset of the rows within the table and full table scans are
only appropriate when more than 40 percent of the table rows are
required. The Oracle optimizers are programmed to check for indexes,
and to use indexes whenever possible to avoid excessive I/O. However,
if the formulation of a query is inefficient, the cost-based optimizer
becomes confused about the best access path to the data, and the
cost-based optimizer will sometimes choose to do a full table scan
against the table. Again, the general rule is for the Oracle database
administrator to interrogate the SQL, and always look for full table
Determining the Threshold for a Full Table Scan
When making the decision to change a full table
scan to an index range scan, the primary concern is the speed of the
query. In some cases, the full table scan may have more physical disk
I/Os, but the full table scan will be faster because of a high degree
In other cases, you need to consider the number
of rows retrieved as a function of the clustering of the rows in the
table. For example, if your table is clustered or you have manually
resequenced the rows in primary-key order, a great many adjacent rows
can be read in a single I/O and an index range scan will be faster
than a full table scan for queries that retrieve more than 20-40
percent of the table rows. On the other hand, if your table is totally
unordered, a request for 10 percent of the table rows may cause the
majority of the table data blocks to be read. Of course, you also need
to consider the degree of parallelism on the table and the setting for
the db_file_multi_ block_read_count init.ora parameter.
Hence, the general guideline for replacing an index range scan is:
row-sequenced tables Queries that retrieve less than 40 percent
of the table rows should use an index range scan. Conversely,
queries that read more than 40 percent of the rows should use a full
For unordered tables Queries that
retrieve less than 7 percent of the table should use an index range
scan. Conversely, queries that read more than 7 percent of the table
rows will probably be faster with a full table scan.
Your mileage may vary, so it is always a good
idea to test the execution speed in SQL*Plus by issuing the set
timing on command.
This is an excerpt from "Oracle9i
High Performance tuning with STATSPACK" by Oracle Press.
If you like Oracle tuning, you may enjoy the new book "Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today.