After an upgrade, or after other database configuration changes, Oracle strongly recommends that you regather fixed object statistics after you have run representative workloads on Oracle Database.

Fixed objects are the X$ tables and their indexes. V$ performance views are defined through X$ tables. Gathering fixed object statistics is valuable for database performance, because these statistics help the optimizer generate good execution plans, which can improve database performance. Failing to obtain representative statistics can lead to suboptimal execution plans, which can cause significant performance problems.

Gather fixed objects statistics by using the DBMS_STATS.GATHER_FIXED_OBJECTS_STATS PL/SQL procedure. DBMS_STATS.GATHER_FIXED_OBJECTS_STATS also displays recommendations for removing all hidden or underscore parameters and events from the INIT.ORA or SPFILE.

Because of the transient nature of X$ tables, you must gather fixed objects statistics when there is a representative workload on the system. If you cannot gather fixed objects statistics during peak load, then Oracle recommends that you do it after the system is in a runtime state, and the most important types of fixed object tables are populated.

To gather statistics for fixed objects, run the following PL/SQL procedure:

SQL> execute dbms_stats.gather_fixed_objects_stats;