11g Inside Extended Optimizer Statistics Oracle 11g New Features Tips by Burleson Consulting 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 user-defined function.

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 join order.

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:

A 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 joins:

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:

• dbms_stats.create_extended_stats

• dbms_stats.show_extended_stats_name

• dbms_stats.drop_extended_stats

Extended histograms can be seen in the user_tab_col_statistics view.

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 decisions.

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.