Gather Optimizer Statistics For Sys

Gathering statistics for sys objects

• It is recommended to run gather statistics reqularly, specifically if you are using Oracle APPS and also after upgrades or running catalog scripts
• To gather the dictionary stats run One of the following
  SQL> exec DBMS_STATS.GATHER_DICTIONARY_STATS;
  SQL> exec DBMS_STATS.GATHER_SCHEMA_STATS ('SYS');
  SQL> exec DBMS_STATS.GATHER_DATABASE_STATS (gather_sys=>TRUE);

Gathering statistics for X$ tables (v$ views )

• Gather_fixed_objects_stats would gather statistics for dynamic tables e.g. the X$ tables which loaded in SGA during the startup. Gathering statistics for fixed objects would normally if we have poor performance in querying the dynamic views e.g. V$ views.
• Fixed objects record current database activity; statistics gathering should be done when database has representative activity.
• To gather the fixed objects stats
  EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

See also :
Gathering System Statistics