4. Run on a regular basis (weekly at a minimum) and anytime after application of patch, conversion, etc.
- GATHER_INDEX_STATS, Index statistics
- GATHER_TABLE_STATS, Table, column, and index statistics
- GATHER_SCHEMA_STATS,Statistics for all objects in a schema
- GATHER_DICTIONARY_STATS,Statistics for all dictionary objects
- GATHER_DATABASE_STATS,Statistics for all objects in a database
(ownname => 'Scott',
estimate_percent => dbms_stats.auto_sample_size E "sample_size",
options => 'GATHER EMPTY'
The above call starts the gather_schema_stats for a schema called SCOTT with estimate_percent at default sample size and options gather empty which gathers statistics on objects which currently have no statistics.
You can disable automated statistics collection job using the code below :
To re-enable the job:
To check if it job running or not
SELECT * FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'GATHER_STATS_JOB';