Gather Fixed Objects Stats in PDBs as well?

Yesterday I received an interesting customer question: “Do we have to gather fixed objects stats inside a PDB as well or only in the CDB$ROOT?” The customer told me he couldn’t find anything in the documentation. Neither could I. And I did check also the usual suspects this morning “Best Practices for Gathering Statistics” – but it does not say anything about PDBs and CDBs. Therefore I did a short test: Create a PDB in my Oracle 12.1.0.2 CDB: SQL> create pluggable database PDB3 admin user adm identified by adm file_name_convert=( ‘/u02/oradata/CDB1/pdbseed’, ‘/u02/oradata/CDB1/pdb3’); Open the PDB: SQL> alter pluggable database…

Will gathering fixed object stats reduce recompilation time post upgrade?

Interesting question, isn’t it? Will the time to recompile invalid objects post upgrade decreased once fixed object stats have been gathered? First of all fixed object stats on X$-tables won’t be gathered by default [This will change with Oracle 12c where it is part of the Auto Stats Gathering job]. X$ structures are undocumented. V$ views are build on top of them and should only be used even though it might be sometimes useful to access X$ tables such as X$BH (buffer headers – contains information describing the current contents of a piece of the buffer cache) sometimes directly. Anyway,…