How to gather dictionary and fixed object statistics in Oracle

Refresh statistics on the data dictionary and on fixed objects (the X$ tables behind the V$ views), recommended after big changes or an upgrade.

These change plans for recursive and dictionary queries. Run them in a quiet window; gather fixed-object stats when the system is at a representative, steady workload.

-- Data dictionary stats
BEGIN
  DBMS_STATS.GATHER_DICTIONARY_STATS;
END;
/

-- Fixed object stats (X$ / V$)
BEGIN
  DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
END;
/

-- Verify (read-only): when these were last gathered
SELECT operation, target,
       TO_CHAR(start_time,'YYYY-MM-DD HH24:MI') AS start_time
FROM   dba_optstat_operations
WHERE  operation IN ('gather_dictionary_stats','gather_fixed_objects_stats')
ORDER  BY start_time DESC;