Oracle Statistics

Implementation of automated statistics collection for application schemas in Oracle 9iR2.

dbms_stats is a supplied package, and is the recommended method of gathering statistics. dbms_utility and analyze are no longer recommended for production systems.

Table monitoring is a feature that counts the approximate number of changes to a table. dbms_stats with "gather stale" will analyze tables that have had more than 10% of their rows changed.

Overview
Determine application schemas to analyze.
Begin table monitoring for the schemas.
Schedule job to analyze stale for the schemas.

Details
Determine application schemas to analyze.

SQL> select username from dba_users order by 1;

Begin table monitoring for the schemas.

SQL> exec dbms_stats.alter_schema_tab_monitoring('SCHEMA_NAME', TRUE)
PL/SQL procedure successfully completed.

Schedule dbms_stats without or with histograms.

exec dbms_stats.gather_schema_stats( -
ownname => 'SCHEMA_NAME', -
options => 'GATHER AUTO' -
);

exec dbms_stats.gather_schema_stats( -
ownname => 'SCHEMA_NAME', -
options => 'GATHER AUTO', -
method_opt => 'FOR ALL INDEXED COLUMNS SIZE 254' -
);