Dictionary, Fixed Object and System Statistics

Summary

Here I’ll go over the main situations where it makes sense to gather dictionary, system, and fixed object statistics with DBMS_STATS. The idea is not to run these every day like schema stats, but rather in specific scenarios. I’ll also drop some quick PL/SQL snippets you can use straight away.

When to gather Dictionary Stats?

Dictionary stats don’t need to be refreshed all the time. Honestly, scheduling them daily doesn’t make much sense. But there are a few good moments where gathering them will help the optimizer a lot:

• After upgrades or patching: new Oracle versions or patches may change dictionary structures.
• Big schema changes: adding/dropping many objects, indexes, etc.
• Before new apps go live: especially if they hit metadata queries heavily.
• Environments with tons of DDL: if you’re constantly creating/dropping stuff.
• After dictionary reorganizations: bulk imports or large DDL waves.
• Custom app requirements: if your application depends on metadata queries being optimized.

How to gather Dictionary Stats?

BEGIN
  DBMS_STATS.GATHER_DICTIONARY_STATS;
END;
/

When to gather System Stats?

For system stats, Oracle’s default behavior usually works fine.

As per Oracle documentation (link), unless there is a good reason to gather manually, Oracle recommends using the defaults for system statistics.

"When to Consider Gathering System Statistics Manually?

If you are using Oracle Exadata, and if the database is running a pure data warehouse load, then gathering system statistics using the EXADATA option can help performance in some cases because table scans are more strongly favored. However, even on Exadata, the defaults are best for most workloads.

If you are not using Oracle Exadata, and if you choose to gather system statistics manually, then Oracle recommends the following:

Gather system statistics when a physical change occurs in your environment, for example, the server gets faster CPUs, more memory, or different disk storage. Oracle recommends that you gather noworkload statistics after you create new tablespaces on storage that is not used by any other tablespace."

How to gather System Stats?

BEGIN
  DBMS_STATS.GATHER_SYSTEM_STATS('NOWORKLOAD');
END;
/

When to just stick with defaults?

In most cases, the default “noworkload” stats are just fine. If you want to reset everything back to defaults, you can do:

BEGIN
  DBMS_STATS.DELETE_SYSTEM_STATS;
END;
/

After that, restart the database and you’ll be back to the safe defaults.

When to gather Fixed Object Stats?

Fixed objects (like the famous V$ views) are another story. If you have the automatic maintenance jobs running, Oracle already gathers them periodically. But you might still want to run it manually in a few cases:

• After upgrades or patches: to make sure the optimizer has the right info for new structures.
• Right after database creation or big imports: to establish a proper baseline.
• If queries on V$ views feel slow: updated stats can improve monitoring and troubleshooting queries.

How to gather Fixed Object Stats?

BEGIN
  DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
END;
/

References:

• Oracle Whitepaper: Best Practices for Gathering Stats
• Official Documentation