Starting in Oracle 10g we see a new package
gather_fixed_stats for analyzing the dictionary fixed
structures (the X$ tables). We now have three types of
stats to analyze for the SQL optimizer:
-
Table/object/schema stats: Via
dbms_stats.gather_table_stats. Or
gather_schema_stats. Traditional metadata from data
tables.
-
System stats: Via
dbms_stats.gather_system_stats: OS statistics (disk, CPU
timings).
-
Dictionary objects: Used to make dictionary
queries more efficient. The gather_fixed_objects_stats
collects the same metadata as gather_table_stats, excepts for the number
of blocks. This is because the x$ structures and the v$ views only
exists in the RAM of the SGA.
The docs note "Analyze fixed
objects only once, unless the workload footprint changes. You
must be connected a SYS (or a user with SYSDBA) to invoke
dbms_stats.gather_fixed_objects_stats.
Just like the workload statistics, Oracle recommends that you
analyze the x$ tables only once, and during a typical database
workload.
Oracle recommends a single analyze of data dictionary and x$
fixed structures for the cost-based optimizer, but it is not clear
when it is necessary to re-analyze the v$ views and x$ structures.
If it ain't broke, don't fix it. However, Oracle recommends that
major parameter changes (db_cache_size, shared_pool_size. sga_target, &c
) may be followed-up with a re-analyze using
dbms_stats.gather_fixed_stats.
