Dear Readers,

Trust you are doing well.

As mentioned in last post, In this post, I will be focussing on commands that we use with respect to in dictionary/fixed object stats gathering. This post can be considered as a one stop reference for dictionary & fixed object statistics gathering stuff.

To gather dictionary stats run one of following commands

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS (‘SYS’);
SQL> exec DBMS_STATS.GATHER_DATABASE_STATS (gather_sys=>TRUE);
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

To gather fixed object stats run following command

EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

Ways to improve dictionary statistics gathering

Run in parallel

EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS(Degree => 64);

Run gather statistics only on stale dictionary objects:

EXEC DBMS_STATS.GATHER_DICTIONARY_STATS ( OPTIONS=>’GATHER STALE’ )

You may choose to lock/unlock stats for WRI$/AUD tables before/after dictionary stats gather:

exec dbms_stats.lock_table_stats(‘SYS’, ‘WRI$_OPTSTAT_SYNOPSIS$’);
exec dbms_stats.lock_table_stats(‘SYS’, ‘WRI$_OPTSTAT_OPR’);
exec dbms_stats.lock_table_stats(‘SYS’, ‘WRI$_OPTSTAT_IND_HISTORY’);
exec dbms_stats.lock_table_stats(‘SYS’, ‘WRI$_OPTSTAT_HISTGRM_HISTORY’);
exec dbms_stats.lock_table_stats(‘SYS’, ‘X$UNIFIED_AUDIT_TRAIL’);

EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS(Degree => 64);

exec dbms_stats.unlock_table_stats(‘SYS’, ‘WRI$_OPTSTAT_SYNOPSIS$’);
exec dbms_stats.unlock_table_stats(‘SYS’, ‘WRI$_OPTSTAT_OPR’);
exec dbms_stats.unlock_table_stats(‘SYS’, ‘WRI$_OPTSTAT_IND_HISTORY’);
exec dbms_stats.unlock_table_stats(‘SYS’, ‘WRI$_OPTSTAT_HISTGRM_HISTORY’);
exec dbms_stats.unlock_table_stats(‘SYS’, ‘X$UNIFIED_AUDIT_TRAIL’);

Export/import of dictionary statistics

exec dbms_stats.create_stat_table(‘SYSTEM’,’DICT_STATS’);
exec dbms_stats.export_dictionary_stats(‘DICT_STATS’,’SYSTEM’);
exec dbms_stats.import_dictionary_stats(‘DICT_STATS’,’SYSTEM’)

Export/import of fixed object statistics

exec dbms_stats.create_stat_table(‘SYSTEM’,’FIXED_OBJ_STATS’);
exec dbms_stats.export_fixed_objects_stats(‘FIXED_OBJ_STATS’,’SYSTEM’);
exec dbms_stats.import_fixed_objects_stats(‘FIXED_OBJ_STATS’,’SYSTEM’);

Delete dictionary/fixed object statistics

exec dbms_stats.delete_dictionary_stats;
exec dbms_stats.delete_fixed_objects_stats();

Hope so u will find this post very useful:-)

You can refer my post on non-dictionary objects statistics gathering here.

Regards,
Adityanath.