In Oracle 11g, the DBMS_STATS procedure GATHER_SCHEMA_STATS takes a parameter ‘options’ which defines the scope of the objects processed by the procedure call, as well as the action. It can be either GATHER or LIST (gather the stats, or list out the objects to be touched, respectively), and AUTO, STALE or EMPTY (defining the object selection to process). GATHER on its own will gather stats on all objects in the schema GATHER EMPTY / LIST EMPTY is self-explanatory - objects with no statistics.

Continue Reading

There is a well-documented problem relating to DBMS_STATS.COPY_TABLE_STATS between partitions where high/low values of the partitioning key column were just copied verbatim from the source partition. This particular problem has now been patched (see 8318020.8). For background, see Doug Burns’ blog and his excellent paper which covers the whole topic of statistics on partitioned tables. This post Maintaining statistics on large partitioned tables on the Oracle Optimizer blog details what the dbms_stats.

Continue Reading

Chucking a stick in the spokes of your carefully-tested ETL/BI … My opinion is that automated stats gathering for non-system objects should be disabled on Oracle Data Warehouses across all environments. All it does it cover up poor design or implementation which has omitted to consider statistics management. Once you get into the realms of millions or billions of rows of data, the automated housekeeping may well not have time to stat all of your tables on each run.

Continue Reading