Oracle Training Oracle Support Development Oracle Apps

 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
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

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

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Linda Webb

Got breaking Oracle news?    Burleson Consulting Oracle News

Click here for more Oracle News Headlines


Speed-up Oracle statistics collection

Jeff Maresh, one of the top Oracle data warehouse guruís, just published this great tip for speeding-up Oracle dbms_stats on large tables in Oracle9i:

Try setting  ALTER SESSION SET EVENTS '38019 trace name context forever, level 1'; before running your stats collection jobs.  This sets the index stats collection method back to 8.1.7.  The 9i version is pathetically slow.  My stats job went from 50 minutes to 26 hours before setting this event.  (See MOSC bugs 2919423, 2980656) 

 Some other things to help improve performance dramatically if you aren't doing so now: 

1) increase the sort area and sort area retained sizes,

2) Tune temp space so it's fast,

3)  Configure and tune PX if you've got a bunch of CPUs and parallelize the stats collection processes.   

 I worked with Oracle support for about 7 months on a number of oracle 9 issues before migrating a dimensional data warehouse 9 months ago. Dbms_stats went through a major rewrite for 9i. It's nothing like the 8i version internally.  I verified this with 10046 traces running on 8i and 9i  sessions.  It's a brand new piece of software and it should be treated that way.  There are still many unresolved problems that are supposed to be resolved in 10.2.  Just do a search on MOSC to see the long laundry list and which ones might affect your database.  There  were several minor problems solved in (I believe). 

 When qualifying 9.2 for production, I tried a variety of things to get good execution plans using a regression set of around 270 queries.   

A few observations:  

1) Stats from analyze are definitely different than stats generated by dbms_stats. 

2) A bad set of stats from dbms_stats is far worse than a good set of stats collected with analyze (eg. worse exec plans, hence worse performance)

3) A good set of stats from dbms_stats is better than a good set of stats collected with analyze

 4) Even after playing around with various dbms_stats options, its still 20-30% slower to collect the same stats as were collected in,

5) There is definitely a difference in stats generated, hence different execution plans, when collecting 5%..25% estimates.  For my DB, stats between 5 & 10% produced very poor execution plans (see point #2).  I settled on 20%. 

6) Histograms made a big difference in performance on indexed columns with even a small amount of data skew. 

7) There were many changes to the CBO going to 9i also that I'm sure affect the execution plans so it's also a significant part of the equation.  --- and once again the disclaimer, this was done on a dimensional data warehouse.  You may have different observations and get different results.


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