Gather Schema Statistics How to Use it !!!!

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 :

  1. GATHER_INDEX_STATS, Index statistics
  2. GATHER_TABLE_STATS, Table, column, and index statistics
  3. GATHER_SCHEMA_STATS,Statistics for all objects in a schema
  4. GATHER_DICTIONARY_STATS,Statistics for all dictionary objects
  5. 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 
 
 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.