Gathering Dictionary Statistics After Upgrading
To help to assure good performance, use this procedure to gather dictionary statistics after completing your upgrade.
Oracle recommends that you gather dictionary statistics both before and after upgrading the database, because Data Dictionary tables are modified and created during the upgrade. With Oracle Database 12c release 2 (12.2) and later releases, you gather statistics as a manual procedure after the upgrade, when you bring the database up in normal mode.
-
Non-CDB Oracle Database: Oracle recommends that you use the
DBMS_STATS.GATHER_DICTIONARY_STATSprocedure to gather these statistics. For example, enter the following SQL statement:SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
-
CDB: Oracle recommends that you use
catconto gather Data Dictionary statistics across the entire multitenant architectureTo gather dictionary statistics for all PDBs in a container database, use the following syntax
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -l /tmp -b gatherstats -- --x"exec dbms_stats.gather_dictionary_stats"To gather dictionary statistics on a particular PDB, use syntax similar to the following:
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -l /tmp -c 'SALES1' -b gatherstats -- --x"exec dbms_stats.gather_dictionary_stats"In the preceding example the
-c SALES1option specifies a PDB inclusion list for the command that you run, specifying the database namedSALES1. The option-b gatherstatsspecifies the base name for the logs. The option--xspecifies the SQL command that you want to execute. The SQL command itself is inside the quotation marks.