Oracle Cost Optimizer Statistics

on November 28, 2012

The Oracle cost-based optimizer (CBO) displays the cost number for a query, or an estimate based on statistics and calculations. Current statistics are very important to the CBO, so understanding how statistics are gathered can help improve your Oracle tuning efforts.

Collecting statistics

The Gather_Schema_Stats will collect statistics on all objects (tables and indexes) with a single command:

Execute DBMS_STATS.GATHER_SCHEMA_STATS (‘PROD’, Cascade => TRUE);

This command:

Does index statistics
Builds histograms
Is great for test/development environments
Note that moving statistics from production doesn’t produce same explain plans

There are also some more advanced options for collecting statistics, including:

Gather State / Gather Empty
Gather histograms
Flushing shared pool/auto invalidate (in Oracle 10g and higher)
Gather system statistics (do this when the system is busy)
Occasionally, you can also execute:

Exec_dbms_stats.ather_dictionary_stats;

Exec dbms_stats.gather_fixed_objects_stats;

TRUE STORY

When statistics are collected, I recommend using the ‘alter system flush shared_pool;’ command. I was at a shop running Oracle9r2 where the library cache was quite large and the SQL was not using current stats because it was sitting in the library cache all week. Oracle9 does not flush the library cache nor does it look to see if stats are current when arriving at explain plans. Flushing the shared pool will cause all SQL to reparse using the now current statistics.

For Oracle10g+, also note the following:

The optimizer mode is now set to First Rows so the CBO will be used on all SQL. The gather_dictionary_stats (v$ tables) and gather_fixed_objects_stats (x$ tables) will greatly help dictionary (recursive) SQL performance.
DBMS_STATS.AUTO_INVALIDATE forces cursors to reparse over a short period of time (max time is 5 hours).

Further notes on collecting statistics

SYSTEM_STATS should be collected occasionally when the system is rather busy. Things like buffer hit cache ratio, the speed of the disk subsystem (and more) are collected and used to influence explain plans.

MMON will monitor for changes in objects based on DML it notices in the library cache.

The ‘stale’ option will only collect statistics on objects with 10% changes or more. This is very helpful to cut down on DBMS_STATS run times.

NOTE

Oracle10g+ has this feature turned on automatically. Oracle10g runs something like the above DBMS_STATS command around 10pm to collect statistics on any object that does not have statistics or that have hit the stale requirements.

Notice the USER_TAB_MODIFICATIONS object is used to monitor the statistics as collected by MMON.

Hotka Page 81 Cost Opt Stats