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;
Related Topics