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 PDB3 open;

• Check for missing stats on X$ tables:

  SQL> select count(*) from sys.dba_tab_statistics
       where owner='SYS' and table_name like 'X$%' and  last_analyzed is not null;
  
  Result in CDB$ROOT: 0
  Result in PDB3:     0

• Gather Fixed Objects Stats only in PDB3:

  SQL> exec dbms_stats.gather_fixed_objects_stats;

• Check the results with:

  SQL> select count(*) from sys.dba_tab_statistics
       where owner='SYS' and table_name like 'X$%' and  last_analyzed is not null;
  Result in CDB$ROOT:    0
  Result in PDB3:     1025

  Gather Fixed Objects Stats in CDB$ROOT:

   SQL> exec dbms_stats.gather_fixed_objects_stats;

• Check the results again with:

  SQL> select count(*) from sys.dba_tab_statistics
       where owner='SYS' and table_name like 'X$%' and  last_analyzed is not null;
  
  Result in CDB$ROOT: 1027
  Result in PDB3:     1025

Conclusion

Yes, you’ll have to gather fixed objects stats in PDBs independently from the CDB$ROOT.
But generally in Oracle Database 12c the Automatic Statistics Gathering job will take care on Fixed Objects Stats as well (see here). In a Multitenant environment you just may have to take care to unfold your default maintenance windows as otherwise this will happen in all PDBs at the same time generating plenty of noise on your system.

Oracle Database automatically gathers fixed object statistics as part of automated statistics gathering if they have not been previously collected (see “Controlling Automatic Optimizer Statistics Collection“).

–Mike