Let Oracle 11g's enhanced dbms_stats do the work
 

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

Read more about smart ways to improve CBO statistics:

http://www.dba-oracle.com/oracle11g/oracle_11g_dbms_stats_enhancements.htm

*****************************************

Need a Health Check?

Oracle is the worlds most complex and robust database and there are hundreds of sub-optimal setting that can cripple your database performance.

Burleson Consulting has a great Oracle health check where we identify all database bottlenecks to ensure that your mission-critical system is running at optimal speeds.

Just call 800-766-1884 to schedule your health check.

*****************************************

Need Oracle Training?

The very best Oracle training comes from Burleson Consulting, where you get an on-site visit by an experienced Oracle expert and author.  Whether it's one-on-one mentoring or getting a customized on-site Oracle training class, there is no substitute for BC Oracle training.  Just call 800-766-1884 for details, and check-out our on-site Oracle training catalog at the following link:

http://www.dba-oracle.com/bc-catalog.pdf