Use of Dictionary and Fixed object Stats in Oracle

When to gather stats for dictionary and fixed object in Oracle.

Gather Dictionary Stats
Following are the cases when you need to regather dictionary stats:
• Gather Dictionary Stats if you make significant change.
• Gather Dictionary Stats before an upgrade
• Gather Dictionary Stats after an upgrade
• Gather Dictionary Stats after schema changes with application upgrade/ IMP or EXP jobs.
• Gather them yourself when your schemas is created or imported.


exec DBMS_STATS.GATHER_DICTIONARY_STATS


Gather Fixed Object Stats (X$ tables)
Following are the cases when you need to regather fixed object stats:
Note: From Oracle 12c gathers missing stats automatically at the end of the maintenance window if time is available.
• Gather stats if you make changes to instance structure e.g. Change SGA size or workload changes or added more CPU count etc.

exec DBMS_STATS.DELETE_FIXED_OBJECT_STATS;
exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;


You can check list of fixed table from following query:

SELECT * FROM dba_tab_statistics WHERE object_type = 'FIXED TABLE'