11g Inside Extended Optimizer Statistics
Oracle 11g New Features Tips by Burleson
July 13, 2008
Oracle 11g SQL New Features Tips
Another exciting feature of Oracle 11g is additional features
built into the dbms_stats package, specifically the ability
to aid complex queries by providing extended statistics to the
cost-based optimizer (CBO).
The 11g extended optimizer statistics are
intended to improve the optimizer's guesses for the cardinality of
combined columns and columns that are modified by a built-in or
In Oracle 10g we see that dynamic sampling can
be used to provide inter-table cardinality estimates, but dynamic
sampling has important limitations. However, the 11g extended
statistics in dbms_stats relieves much of the problem of
sub-optimal table join orders.
In the absence of column histograms and
extended statistics, the Oracle cost-based optimizer must be able to
"guess" the size of complex result sets information, and it
sometimes gets it wrong. This is one reason why the ORDERED and
LEADING hints are two of the most popular SQL tuning hints; using
the ORDERED hint allows you to specify that the tables be joined
together in the same order that they appear in the FROM clause. The
LEADING hint specifies the first table to use when deciding upon a
In this example, the four-way table join only
returns 18 rows, but the query carries 9,000 rows in intermediate
result sets, slowing-down the SQL execution speed:
suboptimal table join order
If we were able to predict the sizes of the
intermediate results, we can re-sequence the table-join order to
carry less "intermediate baggage" during the four-way table join, in
this example carrying only 3,000 intermediate rows between the table
11g extended statistics help the CBO predict
inter-table join result set sizes
Let's take a closer look and understand how the
11g extended dbms_stats data helps the optimizer make better
guesses of result set sizes.
Inside extended optimizer statistics
The purpose behind histograms is to allow
Oracle's optimizer to make intelligent decisions regarding data that
is skewed; that is, when one set of data is not necessarily
representative of the rest of the data in the table. Histograms
provide a remedy by allowing Oracle to capture metadata based upon
the distribution of data in a particular column of a table; however,
histogram gathering to this point has been guesswork at best unless
a Remote DBA has taken the time to perform proper data profiling.
Some data profiling is still necessary.
Gathering histograms on all columns, including unnecessary ones, is
a lengthly and time-consuming process. In Oracle 11g, we are given
the opportunity to gather 'better' histograms when we know that
extended statistics are required.
The new 11g dbms_stats package has
several new procedures to aid in supplementing histogram data:
Extended histograms can be seen in the
Gathering extended statistics allows histograms
not only on one column, but multiple columns at a time. This is
importable for both table joins as mentioned in the last section,
also for multi-column WHERE criteria. Columns generally are not
used independently, and better decision-making is imperative when
queries are run with multiple criteria.
You can also generate extended statistics on a
function of a column; for instance, on upper (column_name). In
proper data profiling we must account for possible changes in data
such as case changes as queries may always have different
requirements for the presentation of data.
Oracle must also account for these changes,
except Oracle does not have the same decision making capabilities of
a human sitting in front of a computer. Without information about
different presentations of data, or multiple column choices, Oracle
cannot be expected to tune all queries as a human being could. As
such, these extended statistics help Oracle make more intelligent
This is an
excerpt from the new book
Oracle 11g New Features: Expert Guide to the Important
New Features by John Garmany, Steve Karam, Lutz Hartmann, V. J.
Jain, Brian Carr.
You can buy it direct from the publisher
for 30% off.