BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation










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.

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.



Remote DBA Service

Oracle Tuning Book


Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software







BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.

Hit Counter