To determine when the statistics have gathered for a certain table:
Note: to speed things up, you’d might consider:
ALTER SESSION SET sort_area_size = 262144000 /
To gather the statistics for a table:
EXEC DBMS_STATS.GATHER_TABLE_STATS( 'OWNER', 'TABLE_NAME', estimate_percent=> 15) /
To gather schema statistics:
EXEC DBMS_STATS.GATHER_SCHEMA_STATS( 'OWNER', cascade=> true, estimate_percent=> 15) /
The CASCADE=> true bit in the command above makes sure that the statistics of the indexes are refreshed again as well.
In some cases, usually when the database (or sometimes even the application) hasn’t been upgraded for quite some time, gathering the statistics just dont quite cut it. You can try the ‘ole analyze…
Remember! this isn’t supported any longer.
ANALYZE TABLE 'OWNER'.'TABLE_NAME' estimate statistics /