Oracle cost-based optimizer
Updated January 6, 2015
Oracle's cost-based SQL
optimizer (cost based optimizer) is an extremely sophisticated
component of Oracle that governs the execution for every Oracle
query. The cost based optimizer has evolved into one of the world's
most sophisticated software components, and it has the challenging
job of evaluating any SQL statement and generating the "best"
execution plan for the statement.
Because the cost based optimizer determines the execution speed for
every Oracle query, the Oracle professional must understand how the
cost based optimizer is influenced by Oracle external issues,
internal statistics, and data distribution.
There are two types of factors that influence the cost-based
optimizer, those factors that are immutable (that we cannot change),
and those for which we have some control. The immutable factors
in a SQL statement include basic issues such as the number of rows
returned by the query. But far and away, variable factors
influence the costing decisions made by the optimizer:
- Disk I/O speed - The cost of disk I/O is the
single most important factor in SQL optimization. Disk I/O
is measured in thousandths of a second, an eternity for a
database, and something that needs to be avoided whenever
- Available RAM - The DBA configures the Oracle
instance RAM resources, and the optimizer will be severely limited
if there is not enough RAM for hash joins (via
pga_aggregate_target, sort_area_size and hash_area_size).
- Object metadata - The DBA controls the
quality of the metadata via the dbms_stats package.
This data includes the number of rows in a table, the distribution
of values within a column and other critical information about the
state of the tables and indexes.
- Server metadata - The DBA controls the
gathering of server-side metadata via
dbms_stats.gather_system_stats. This measures CPU
speed, single probe disk read speed (db file sequential reads) and
multi-block reads (db file scattered reads).
Disk speed matters most!
If you forget to analyze server statistics with
dbms_stats.gather_system_stats, Oracle uses a default disk I/O
speed of ten milliseconds. Analyzing server statistics is one of the
most important things that you can do to tune your SQL!
There are many other factors, but this gives you the general idea
of how you can influence the costing decisions of the optimizer.
histograms with the Oracle cost-based optimizer
- cost based optimizer
parameters. We will start by reviewing the basic optimizer
modes within the cost based optimizer and then drill down and
examine specific parameters that influence the behavior of the
cost based optimizer.
Using SQL Cost-based optimizer
- cost based optimizer
statistics. We will examine the importance of gathering
proper cost based optimizer statistics with
dbms_stats and review
techniques for ensuring that execution plans remain stable. We
will also look at techniques for migrating statistics between
systems and examine how developers can optimize their SQL in a
test environment and confidently migrate SQL into production
without fear of changing execution plans.
cost-based optimizer schema statistics
clustering and SQL tuning
the cost based optimizer