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
Really interesting. Thank you very much for the explanation.
so my understanding are
fixed obj – both cdb/pdb,
dict obj – only cdb ,
sys stats – only cbd
correct me if i am wrong.
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
For the sys stats, there can be different values for the CDB as PDB after import. It looks like the sys stats should be run twice as well, or copied if you like. There is no way to ‘delete’ the PDB sys stats it seems.
Hi Ian,
I guess this topic (CDB/PDB vs non-CDB) is not documented anywhere very well.
Cheers,
Mike
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.
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