In my previous life as the Optimizer Lady, I wrote a blog on the importance of gathering fixed object statistics, since they were not originally gathered as part of the automatic statistics gather task.
Starting with Oracle Database 12c Release 1, Oracle will automatically gather fixed object statistics as part of 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 what these statistics are, you may be wondering why Oracle hasn’t always automatically gather these statistics?
Originally, we thought it wouldn’t be a good idea to gather the fixed object statistics given the transient nature of the x$ tables. We worried that the during the maintenance window we may not have representative workload on the system and the statistics gathered wouldn’t be good enough.
However, having worked on a number of customer escalations where there were performance problems with queries against the V$ views I quickly released having any fixed objects statistics were better than none.
You see, unlike other database tables, dynamic sampling is not used for SQL statement 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. It is for this reason that we strongly recommend you gather fixed objects statistics.
So, have 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 continuously updated like other statistics, so you should manually re-gather fixed object statistics if you do a major 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 are manually gathering fixed object statistics you will need to gather fixed objects 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 statistics is always set to 0 since the x$ tables are in memory structures only 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.