Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Oracle 11g dbms_stats enhancements


Oracle11g Tips by Burleson Consulting
October 13, 2007

These are work in progress excerpts from the book "Oracle 11g New Features" authored by John Garmany, with Oracle ACE's Steve Karam, Lutz Hartmann, V.J. Jain and Brian Carr.


Oracle 11g has the challenge of making their wonderful cost-based optimizer (CBO) always generate the "best" execution plan for any SQL, a formidable challenge.

To achieve this goal, Oracle understands that the quality of their metadata is critical.  Only by knowing the distribution of values with the tables can the CBO make the "best" decision regarding execution plans. 

Prior to Oracle 10g, adjusting powerful optimizer parameters (optimizer_index_cost_adj) was the only way to compensate for sample size issues with dbms_stats

But as of Oracle 10g, improvements in system statistics collection using dbms_stats.gather_system_stats (to measure sequential vs. scattered disk I/O speed) plus improved sampling within dbms_stats had made adjustments to the optimizer parameters a "worst practice"  exercise in most cases.  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". 

 

Improving CBO statistics the smart way

Oracle performance guru Greg Rahn performed a representative test of the Oracle11g enhancements to the dbms_stats package, correctly noting that "skewed" data distributions were problematic in 10g because the dbms_stats package did not manage skew and out of range values in an optimal fashion.  Rahn also demonstrated several exciting improvements to dbms_stats:

  • Faster collection - The dbms_stats package is ~= 2x faster when collecting statistics.  Of course, the recommended procedure with dbms_stats is to collect a single, deep sample and save your statistics, but this performance feature is welcome, especially for very large shops.
     

  • Refined automatic sample size - Rahn notes that the 11g dbms_stats auto_sample_size now collects a statistically significant sample, even for highly skewed data distributions.

Overall, Oracle 11g has taken great strides into improving the automated collection of CBO statistics, ensuring that SQL will be optimized with much less manual intervention.

Traditionally, a skewed distribution within column values required manual intervention by adding column histograms.  In Oracle 11g we now see 11g extended optimizer statistics, an alternative to dynamic_sampling for estimating result set sizes.



Oracle guru Guy Harrison also offers this advice for 11g statistics collection.  

In 11g, I think there are two other ways to get statistics collected for indexed expressions:

1) Collect extended statistics directly on the expression. So for instance, if we had a function SALES_CATEGORY, we might do this:

DBMS_STATS.gather_table_stats
   (ownname => USER,

    tabname => ?SALES?,
    method_opt => ?FOR ALL COLUMNS FOR COLUMNS
   (sale_category(amount_sold))? );

2) Create a virtual column on the expression, then index that column. So for the same example as above we might create the following virtual column, then index the column and collect stats as usual:

ALTER TABLE
   SALES
ADD
   sales_category
GENERATED
ALWAYS AS
   (sale_category(amount_sold));



Arup Nanda has a great article on extended statistics with dbms_stats, specialty histogram analysis using function-based columnar data:

Next, re-gather statistics on the table and collect the extended statistics on the expression upper(cust_name).

begin
  dbms_stats.gather_table_stats (
     ownname    => 'ARUP',
     tabname    => 'CUSTOMERS',
     method_opt => 'for all columns size skewonly for columns (upper(cust_name))'
  );
end;
 

Alternatively you can define the column group as part of the gather statistics command.

You do that by placing these columns in the method_opt parameter of the gather_table_stats procedure in dbms_stats as shown below:
 

begin
   dbms_stats.gather_table_stats (
      ownname         => 'ARUP',
      tabname         => 'BOOKINGS',
      estimate_percent=> 100,
      method_opt  => 'FOR ALL COLUMNS SIZE SKEWONLY FOR COLUMNS(HOTEL_ID,RATE_CATEGORY)',
       cascade         => true

See my notes here on tuning with histograms, which may be obsolete as of Oracle 11g with their improvements to the dbms_stats collection mechanism:

 

If you like Oracle tuning, you may enjoy my new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational