Oracle CloudWorld was hugely enjoyable this year. It was great to see the crowds again and most of my time was spent at the optimizer demo booth speaking to customers. I’d like to thank all of those that took the time to drop by for a chat. Feedback is always welcome, and this year was a rich source.

Strangely, multiple people came by asking about the same topic: “When should we gather fixed object and dictionary stats?” It happened so often I promised to write a blog post. So here it is, and there’s something on system statistics too, just for good measure.

My main message is that there is no need to over-think this. Our experience (in the Oracle Optimizer group) is that fixed object and dictionary stats are not a big concern, but you should at least have statistics in place. SQL Maria wrote about this here and  here. She makes the point that you should gather fixed object statistics if you make a significant change to the database. A similar approach works for gathering dictionary statistics. After all, if you create a new schema and you have tables with a few thousand partitions, you can expect the contents of the data dictionary to change a great deal. For the vast majority of systems, there’s no need to regularly gather these statistics.

Gather using the APIs Oracle provides (and not, for example, individual GATHER_TABLE_STATS calls).

These are the APIs:

  • DBMS_STATS.GATHER_FIXED_OBJECT_STATS
  • DBMS_STATS.GATHER_DICTIONARY_STATS

GATHER_DICTIONARTY_STATS will (by default) gather stale statistics for SYS and SYSTEM tables. GATHER_FIXED_OBJECT_STATS refreshes statistics on fixed tables (the X$ tables).

If you use automatic statistics gathering, it takes care of dictionmary stats for you. If you don’t want automatic stats to gather application schema statistics, you can set the global preference AUTOSTATS_TARGET to ORACLE and it will continue to take care of dictionary stats. See here.

Some of you might encounter war stories where a problem was fixed by some stats manipulation or other, but I will always argue that this is the exception, and not the rule.

As for system statistics, there’s a blog post on that topic here. The content can be summarized as, “use the defaults if you can.”

Note: if you are an Autonomous Database user, you don’t have to gather any of these statistics (the API calls raise an ‘insufficient privileges’ exception).