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

Share this: