GATHER Schema, Table, Index STATS

PL/SQL Packages DBMS_STATS Gather, View, Modify or Delete optimizer statistics for database objects. From Oracle 8i the DBMS_STATS package is the preferred method Oracle list a number of benefits to using it including parallel execution, long term storage of statistics and transfer of statistics between servers.

Oracle Cost Based Optimizer (CBO) requires statistics about data storage and distribution to generate accurate execution plans for queries. These statistics are generated using the DBMS_STATS package. Also, while using the CBO, histograms are used to store detailed information about data distributions which are non-uniform. This information helps the optimizer better estimate the selectivity of predicates which will result in more efficient execution plans. It is useful to create histograms when the application uses queries having:

Procedure Name	Description
GATHER_TABLE_STATS	Gathers statistics for a table and its indexes
GATHER_INDEX_STATS	Gathers statistics for an index
GATHER_SCHEMA_STATS	Gathers statistics for all objects in a schema
GATHER_DATABASE_STATS	Gather statistics for all objects in the database

Syntax:

exec DBMS_STATS.GATHER_SCHEMA_STATS(

        ownname,
        estimate_percent, 
        block_sample , 
        method_opt,
        degree,
        granularity,
        cascade,stattab, 
        statid,options,
        statown ,
        no_invalidate, 
        gather_temp,
        gather_fixed); 
 
 
Code examples:
EXEC DBMS_STATS.gather_schema_stats('SCOTT',DBMS_STATS.AUTO_SAMPLE_SIZE);
EXEC DBMS_STATS.gather_schema_stats(ownname =>'SCOTT',
               estimate_percent  =>DBMS_STATS.AUTO_SAMPLE_SIZE);
EXEC DBMS_STATS.gather_schema_stats(ownname =>'SCOTT',
               estimate_percent => 25);

To delete the schema stat information of Scott schema run following query

EXEC DBMS_STATS.delete_schema_stats('SCOTT');
 

To get Table Stats run the following query.

EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMP');
select table_name,column_name,num_distinct,last_analyzed,num_buckets
from user_tab_cols
where table_name like ('EMP');
 
[N.B: you can also take information from user_tables data dictionary table]

To get Index Stats run the following query

EXEC DBMS_STATS.gather_index_stats('SCOTT', 'PK_EMP');
select table_name,index_name,num_rows,last_analyzed
from user_indexes
where table_name like ('EMP');