Automatic Collection of Fixed Objects Statistics in 12c

In my previous life as the Optimizer Lady, I wrote a blog on the importance of gathering fixed object statistics since they were not collected initially as part of the automatic statistics gathering task.

Starting with Oracle Database 12c Release 1, Oracle will automatically gather fixed object statistics as part of the automated statistics gathering task if they have not been previously collected. Does that mean we are off the hook then?

The answer (as always) is it depends!

Let me begin by explaining what we mean by I the term “fixed objects”.

Fixed objects are the x$ tables and their indexes, upon which the v$ performance views in Oracle are defined (for example, V$SQL and V$IM_SEGMENTS etc.).

Now that we are on the same page on these statistics, you may wonder why Oracle hasn’t always automatically gathered these statistics?

Initially, we thought it wouldn’t be good to gather the fixed object statistics given the transient nature of the x$ tables. We worried that we might not have a representative workload on the system during the maintenance window, and the statistics gathered wouldn’t be good enough.

However, having worked on several customer escalations where there were performance problems with queries against the V$ views, I quickly realised that having any fixed objects statistics was better than none.

You see, unlike other database tables, dynamic sampling is not used for SQL statements involving X$ tables when statistics are missing. When fixed object statistics are missing, the Optimizer uses predefined default values for the statistics. These defaults may not be representative and can potentially lead to a suboptimal execution plan, which could cause severe performance problems in your system. For this reason, we strongly recommend you gather fixed objects statistics.

So, having no fixed object statistics is bad, but since I’m on 12c and the automatic statistics gathering task has collected fixed object statistics for me, then I’m off the hook, right?

No, not necessarily.

Although the automatic statistics gathering task collects fixed object statistics initially, they are not maintained like other statistics. Therefore, you should manually re-gather fixed object statistics if you do a significant database or application upgrade, implement a new module, or make changes to the database configuration.

You should also be aware that if you are in a Multitenant environment and manually gather fixed object statistics, you will need to gather fixed object stats in each PDBs independently from the CDB$ROOT. You can find more information on this on Mike Dietrich’s upgrade blog.

You can collect statistics on fixed objects using the DBMS_STATS.GATHER_FIXED_OBJECTS_STATS procedure.

BEGIN
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
END;

The DBMS_STATS.GATHER_FIXED_OBJECTS_STATS procedure gathers the same statistics as DBMS_STATS.GATHER_TABLE_STATS except for the number of blocks. The BLOCKS statistic is always set to 0 since the x$ tables are in memory only structures and are not stored on disk. You must have the ANALYZE ANY DICTIONARY or SYSDBA privilege or the DBA role to update fixed object statistics.