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

5 thoughts on “Gather Fixed Objects Stats in PDBs as well?

  1. No, unfortunately not correct. Correct would be:

    fixed obj – both cdb/pdb,
    dict obj – both cdb/pdb,
    sys stats – only cbd

    Reason:
    A PDB has a dictionary – and the auto gather stats job will refresh the dict stats as well.

    Cheers
    Mike

  2. For me your example shows that gathering fixed object stats in a PDB doesn’t gather stats in the root container.
    That’s behaviour that I would expect.

    But could it be that gathering fixed object stats in the root container also gathers fixed object stats in PDBs?
    That’s behaviour that I would _not_ expect, but maybe Oracle implemented some kind of "unexpected intelligence" here 😉

    So with this in mind it would be more interesting to see the
    real last_analyzed date here instead of just counting where last_analyzed is not null.

  3. Hi Mr Pausch,

    gathering stats in CDB$ROOT and PDBs is completely independent from each other, regardless of dictionary or fixed objects stats.

    No AI implemented yet as far as I know 😉

    Cheers
    Mike Dietrich

Leave a Reply

Your email address will not be published. Required fields are marked *