Corporation has invested millions of dollars in making
the cost-based SQL optimizer (CBO) one of the most
sophisticated tools ever created. The job of the CBO is
to always choose the most optimal execution plan for any
However, there are some things that the CBO cannot
detect, which is where the DBA comes in. The types of
SQL statements, the speed of the disks and the load on
the CPUs, all affect the "best" execution plan for a SQL
statement. For example, the best execution plan at 4:00
A.M. when 16 CPUs are idle may be quite different from
the same query at 3:00 P.M. when the system is 90
Despite the name "Oracle", the CBO is not psychic, and
Oracle can never know, a priori, the exact load
on the Oracle system. Hence the Oracle professional must
adjust the CBO behavior periodically. Most Oracle
professionals make these behavior adjustments using the
instance-wide CBO behavior parameters such as
However, Oracle does not recommend changing the default
values for many of these CBO settings because the
changes can affect the execution plans for thousands of
Here are some of the major adjustable parameters that
influence the behavior of the CBO:
Important Note: Prior to Oracle 10g, adjusting
these optimizer parameters was the only way to
compensate for sample size issues with dbms_stats.
As of 10g, the use of dbms_stats.gather_system_stats
and improved sampling within dbms_stats had made
adjustments to these parameters far less important.
Ceteris Parabus, always adjust CBO statistics before
adjusting optimizer parms. For more details on
optimizer parameters, see my latest book "Oracle
Tuning: The Definitive Reference".
- optimizer_index_cost_adj: This
parameter alters the costing algorithm for access
paths involving indexes. The smaller the value, the
cheaper the cost of index access.
- optimizer_index_caching: This is the
parameter that tells Oracle how much of your index is
likely to be in the RAM data buffer cache. The setting
for optimizer_index_caching affects the CBO's
decision to use an index for a table join (nested
loops), or to favor a full-table scan.
- optimizer_max_permutations: This
controls the maximum number of table join permutations
allowed before the CBO is forced to pick a table join
order. For a six-way table join, Oracle must evaluate
6-factorial, or 720, possible join orders for the
- db_file_multiblock_read_count: When
set to a high value, the CBO recognizes that scattered
(multi-block) reads may be less expensive than
sequential reads. This makes the CBO friendlier to
- parallel_automatic_tuning: When set
"on", full-table scans are parallelized. Because
parallel full-table scans are very fast, the CBO will
give a higher cost to index access, and be friendlier
to full-table scans.
- hash_area_size (if not using
pga_aggregate_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): The sort_area_size
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 pre-sorted
The parameter optimizer_index_cost_adj controls
the CBO's propensity to favor index scans over
full-table scans. As we will see, in a dynamic system,
the "ideal" value for optimizer_index_cost_adj
may change radically in just a few minutes, as the type
of SQL and load on the database changes.
The optimizer_index_cost_adj is the most
important parameter of all, and the default setting of
100 is incorrect for most Oracle systems. However, for
OLTP systems, resetting this parameter to a smaller
value (between 10 and 30) may result in huge performance
In Oracle 10g, you can achieve a similar result to
reducing the value of optimizer_index_cost_adj by
analyzing your workload statistics (dbms_stats.gather_system_stats).
Also note that utilizing CPU costing (_optimizer_cost_model)
may effect the efficiency of plans with lower values for
Is it possible to query the Oracle environment and
intelligently determine the optimal setting for
optimizer_index_cost_adj? Let's examine the issue.
The optimizer_index_cost_adj parameters default
to a value of 100, and can range in value from 1 to
10,000. A value of 100 means that equal weight is given
to index vs. multiblock reads. In other words,
optimizer_index_cost_adj can be thought of as a "how
much do I like full-table scans?" parameter.
With a value of 100, the CBO likes full-table scans and
index scans equally, and a number lower than 100 tells
the CBO that index scans are faster than full-table
scans. However, even with a super-low setting (optimizer_index_cost_adj=1),
the CBO will still choose full-table scans for
no-brainers, like tiny tables that reside on two blocks.
col c1 heading
'Average Waits for|Full Scan Read I/O'
col c2 heading
'Average Waits for|Index Read I/O'
col c3 heading
'Percent of| I/O Waits|for Full Scans'
col c4 heading
'Percent of| I/O Waits|for Index Scans'
col c5 heading 'Starting|Value|for|optimizer|index|cost|adj'
/(a.total_waits + b.total_waits) c3,
/(a.total_waits + b.total_waits) c4,
/ a.average_wait)*100 c5
a.event = 'db
file scattered read'
b.event = 'db
file sequential read'
Here is the output from the script in Listing A.
Percent of Percent of index
Average waits for Average waits for I/O waits I/O waits cost
full scan read I/O index read I/O for full scans for index scans adj
------------------ ------------------- ---------------- --------------- -------
1.473 .289 .02 .98 20
As you can see, the suggested starting value
for optimizer_index_cost_adj may be
too high because 98 percent of the data
waits are on index (sequential) block
access. How we can "weight" this starting
value for optimizer_index_cost_adj to
reflect the reality that this system has
only two percent waits on full-table scan
reads (a typical OLTP system with few
full-table scans). As a practical matter, we
never want an automated value for
optimizer_index_cost_adj to be less than
one or more than 100.
Also, these values change constantly, As the
I/O waits accumulate and access patterns
change, this same script may give a very
different result at a different time of the
The Oracle Cost-based SQL optimizer is one
of the world's most sophisticated software
achievements, but it is the job of the
Oracle professional to provide valid
statistics for the schema and understand how
the Oracle parameters affect the overall
performance of the SQL optimizer. Remember,
suboptimal SQL execution plans are a major
reason for poorly performing Oracle
databases, and because the CBO determines
the execution plans, it is a critical
component in Oracle optimization.
information, see Don Burleson's book
Oracle Tuning: The Definitive Reference
by Rampant TechPress. For SQL tuning
scripts, Mike Ault, one of the world's most widely-read
Oracle experts, has released his complete
collection of more than 450
Oracle scripts, covering every possible
area of Oracle administration and
This article shows you the dynamic nature
of an active database and demonstrates the
value of being able to dynamically change
important parameters as the processing load
on the system changes.