Basic Gathering of Stats

Basic Stats collection for a whole schema

Execute DBMS_STATS.GATHER_SCHEMA_STATS
(OWNNAME =>'SCHEMA_NAME',
ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,
CASCADE => TRUE);

Basic Stats collection for a table. Note the use of no_invalidate => false.
This will invalidate any queries running against the table being analyzed.

begin
DBMS_STATS.GATHER_TABLE_STATS
(OWNNAME =>'SCHEMA_NAME',
TABNAME=>'NAME_OF_TABLE',
ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,
CASCADE => TRUE,
NO_INVALIDATE => FALSE);
end;

Basic Stats collection for a specific table partition.

begin
DBMS_STATS.GATHER_TABLE_STATS
(OWNNAME =>'SCHEMA_NAME',
TABNAME=>'NAME_OF_TABLE',
PARTNAME => 'NAME_OF_NEW_PARTITION',
GRANULARITY => 'PARTITION',
ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,
CASCADE => TRUE,
NO_INVALIDATE => FALSE);
end;

******************************************
keywords: gather stats
******************************************
rdbms version: 10g
******************************************