Gather Statistics

 

Oracle Gather Stat 

Gather Table, Index and Schema Statistics

 

DBMS_STATS.GATHER_TABLE_STATS is used to gather stats for a single table

EXEC DBMS_STATS.gather_table_stats('HR','EMPLOYEES');

EXEC DBMS_STATS.gather_table_stats('HR','EMPLOYEES',cascade=>TRUE);

( Note: Cascade gathers Index stats associated with the table )

DBMS_STATS.GATHER_INDEX_STATS is used to gather index stats

EXEC DBMS_STATS.gather_index_stats('HR','EMPLOYEES_PK');

DBMS_STATS.GATHER_SCHEMA_STATS package is used to gather entire schema stats

EXEC DBMS_STATS.gather_schema_stats('SCOTT');

Gather Other Database Objects Statistics

DBMS_STATS.GATHER_DATABASE_STATS package is used to gather entire database stats

EXEC DBMS_STATS.gather_database_stats;

DBMS_STATS.GATHER_DICTIONARY_STATS package will gather dictionary statistics

EXEC DBMS_STATS.gather_dictionary_stats;

Optional:

After any Major DB version Upgrade or any system migration or any system Hardware change system and fixed_object stats might be required.

EXEC DBMS_STATS.gather_system_stats;

EXEC DBMS_STATS.gather_fixed_objects_stats;

 

Check Stale Statistics

 

Query on DBA_TAB_STATISTICS view to check stale statistics on a specific table:

SELECT owner, table_name, last_analyzed, stale_stats

FROM dba_tab_statistics

WHERE table_name='EMPLOYEES'

and owner='HR';

Query on DBA_IND_STATISTICS view to check stale statistics on specific index:

SELECT owner, table_name, index_name last_analyzed, stale_stats FROM dba_ind_statistics 

WHERE table_name='EMPLOYEES'

and owner = 'HR';

Reference:

https://docs.oracle.com/cd/A97630_01/server.920/a96533/stats.htm

Thanks for reading!!! Feel free to leave a comment or any suggestions/recommendations to improve the articles.