Regathering Fixed Objects Statistics with DBMS_STATS

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.

Note:

To provide the most correct fixed object statistics for performance tuning, Oracle strongly recommends that you gather baseline statistics at a point when the system is running with a representative workload. For useful results, never run DBMS_STATS.GATHER_FIXED_OBJECTS_STATS immediately after the upgrade.

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 to 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.

Ensure that your database has run representative workloads, and then 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;