Manual Statistics Gathering

1. To Gather Statistics on System Schemas (SYS, SYSTEM, etc)

SQL> Exec DBMS_STATS.GATHER_DICTIONARY_STATS
SQL> Exec DBMS_STATS.DELETE_DICTIONARY_STATS

2. To Gather Statistics on all database objects including System schemas.

SQL> Exec DBMS_STATS.GATHER_DATABASE_STATS
SQL> Exec DBMS_STATS.DELETE_ DATABASE _STATS

3. To Gather Statistics on schemas other than System schemas

SQL> Exec DBMS_STATS.GATHER_SCHEMA_STATS(‘Ownername’);
SQL> Exec DBMS_STATS.DELETE_SCHEMA_STATS(‘Ownername’);

4. To Gather Statistics on individual tables

SQL> Exec DBMS_STATS.GATHER_TABLE_STATS(‘Ownername’,’Tablename’);
SQL> Exec DBMS_STATS.DELETE_TABLE_STATS(‘Ownername’,’Tablename’);

5. To Gather Statistics on indexes

SQL> Exec DBMS_STATS.GATHER_INDEX_STATS(‘Ownername’,’Indexname’);
SQL> Exec DBMS_STATS.DELETE_INDEX_STATS(‘Ownername’, ’Indexname’);

6. To Gather Statistics on individual columns of tables

SQL> Exec DBMS_STATS.GET_COLUMN_STATS(‘Ownername’,’Tablename’,’Column’);
SQL> Exec DBMS_STATS.DELETE_ COLUMN _STATS(‘Owner’,’Tablename’,’column’);

Note: Oracle invalidates the parsed SQL statements when new statistics are updated.

Thanks