Gather Schema Statistics program generates statistics that quantify the data distribution and storage characteristics of tables, columns, indexes, and partitions.
As a general rule, run Gather Schema Statistics under the following circumstances:
1. After there has been a significant change in data in either content or volume.
2. After importing data.
3. Any time end-users notice deterioration in performance in routine day-to-day business transactions or when running concurrent programs.
4. Run on a regular basis (weekly at a minimum) and anytime after application of patch, conversion, etc.
Type :
- 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
Example :
begin
dbms_stats.gather_schema_stats
(ownname => 'Scott',
estimate_percent => dbms_stats.auto_sample_size E "sample_size",
options => 'GATHER EMPTY'
);
end;
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 :
EXEC dbms_scheduler.disable(’GATHER_STATS_JOB’);
To re-enable the job:
EXEC dbms_scheduler.enable(’GATHER_STATS_JOB’);
To check if it job running or not
SELECT * FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'GATHER_STATS_JOB';
Thank you
Osama mustafa
Technology Geek 