Statistics Collection ( Gather Statistics )

Gathering Statistics

Auditing Information are Stored in SYSTEM Tablespace . While Statistics information are stored in SYSAUX Tablespace.

 ü  For this GATHER_STATS_JOB is enabled.

 ü  For GATHER_STATS_JOB to work properly, you must be sure that  the STATISTICS_LEVEL parameter is set to  at least TYPICAL.

Gathering Statistics for a Table

sql> select num_rows from dba_tables  where owner=’APPS' and table_name='PER_ALL_PEOPLE_F';

sql> exec dbms_stats.gather_Table_stats('HR','PER_ALL_PEOPLE_F');

sql> select num_rows from dba_tables  where owner=’APPS' and table_name='PER_ALL_PEOPLE_F;

Gathering Statistics for a Database

begin

dbms_stats.gather_database_stats(

options=> 'GATHER AUTO');

end;

Gathering Statistics for a Schema

begin

dbms_stats.gather_schema_stats(

ownname=> '"APPS"' ,

options=> 'GATHER AUTO');

dbms_stats.lock_schema_stats( ownname=> '"APPS"' );

end;

/

Several statistics option

lock optimizer statistics to gurantee that the statistics for certain objects are never overwritten.

1) exec dbms_stats.lock_schema_stats('HR');      - to lock schema statistics

2) exec dbms_stats.unlock_schema_stats('HR');  - to unlock schema statistics

3) exec dbms_stats.delete_schema_stats('HR')    - to delete schema statistics

4) exec dbms_stats.restore_table_stats('SCOTT','DEPT'..)        - to restore table statistics to a particular time