What are the fixed objects:
Fixed objects are the x$ tables and their indexes.
Why we must gather statistics on fixed objects:
If the statistics are not gathered on fixed objects, the Optimizer will use predefined default values for the
statistics. These defaults may lead to inaccurate execution plans.
Does Oracle gather statistics on fixed objects:
Statistics on fixed objects are not being gathered automatically nor within gathering database stats procedure. Starting with 12.1 fixed object statistics will be gathered for the tables that don't have statistics, but still, this will not produce accurate statistics and hence Oracle recommends the DBA do this job himself as he/she knows better the peak time of his/her DB.
When we should gather statistics on fixed objects:
-After a major database or application upgrade.
-After implementing a new module.
-After changing the database configuration. e.g. changing the size of memory pools (sga,pga,..).
-Poor performance/Hang encountered while querying dynamic views e.g. V$ views.
-Features that are heavily dependant on fixed objects by design are performing slow, like RMAN, EM and Grid Control.
-This task should be done only a few times per year.
Notes to consider before starting gathering fixed object statistics:
-It's recommended to Gather the fixed object statistics during peak hours (while system is busy) or after the peak hours but the sessions are still connected (even if they idle), to guarantee that the fixed object tables have been populated and the statistics are well representing the DB workload.
-Performance degradation may be experienced during gathering fixed object statistics.
-Having no statistics is better than having a non-representative statistics.
How to gather stats on fixed objects:
Firstly Check the last analyzed date:
select OWNER, TABLE_NAME, LAST_ANALYZED from dba_tab_statistics where table_name='X$KGLDP';
OWNER TABLE_NAME LAST_ANAL
--------- ------------ ---------
SYS X$KGLDP 20-MAR-12
Secondly Export the current fixed stats in a table: (in case you need to revert back)
Thirdly Gather fixed objects stats:
In case of reverting back to the old statistics:
In case you experianced a bad performance on fixed tables after gathering the new statistics:
exec DBMS_STATS.import_fixed_objects_stats(stattab =>’STATS_TABLE_NAME’,STATOWN =>'OWNER');
Oracle white paper: Best Practices for Gathering Optimizer Statistics