How to Gather Dictionary and Fixed Object Statistics in Oracle Database 19c

Oracle uses optimizer statistics to choose execution plans. Alongside application table statistics, dictionary and fixed object statistics support queries against database metadata and internal structures.

This post explains how to gather dictionary statistics in a pluggable database (PDB) and fixed object statistics at the container database (CDB) level.

Replace APP_PDB with your PDB name.

1. Connect to the Database

Log in as the Oracle software owner, set the database environment, and connect using SQL*Plus:

sqlplus / as sysdba

Check the current container and available PDBs:

SHOW CON_NAME;
SHOW PDBS;

Example output:

CON_ID  CON_NAME    OPEN MODE   RESTRICTED
------  ---------- ----------- ----------
2       PDB$SEED    READ ONLY   NO
3       APP_PDB     READ WRITE  NO

2. Gather Dictionary Statistics in the PDB

Dictionary statistics cover objects owned by SYS, SYSTEM, and other Oracle component schemas. Use the dedicated GATHER_DICTIONARY_STATS procedure for this purpose.

Switch to the required PDB:

ALTER SESSION SET CONTAINER = APP_PDB;

SHOW CON_NAME;

SET TIMING ON;

Gather dictionary statistics:

BEGIN
  DBMS_STATS.GATHER_DICTIONARY_STATS;
END;
/

Expected output:

PL/SQL procedure successfully completed.

Gather Only Stale or Missing Dictionary Statistics

To limit collection to dictionary objects with stale or missing statistics:

BEGIN
  DBMS_STATS.GATHER_DICTIONARY_STATS(
    options => 'GATHER STALE'
  );
END;
/

Choose either the default collection or the stale-only collection according to your maintenance requirement.

Is a Separate SYS Schema Collection Required?

For general dictionary statistics maintenance, use GATHER_DICTIONARY_STATS. A separate call to GATHER_SCHEMA_STATS('SYS', ...) is normally unnecessary because dictionary collection already covers the relevant SYS objects.

3. Gather Fixed Object Statistics

Fixed objects are internal structures, including the underlying X$ tables used by many dynamic performance views.

Gather fixed object statistics when the database has a representative workload. Collecting them immediately after startup, before normal activity has populated the internal structures, can produce unrepresentative statistics.

For CDB-level maintenance, switch to the root container:

ALTER SESSION SET CONTAINER = CDB$ROOT;

SHOW CON_NAME;

SET TIMING ON;

Run the collection:

BEGIN
  DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
END;
/

Expected output:

PL/SQL procedure successfully completed.

Gathering dictionary statistics in one PDB does not replace dictionary maintenance in other containers. Plan collection separately for the containers that require it.

4. Review Statistics Collection History

Run the following query in the container where the collection was performed:

SET LINESIZE 200;
SET PAGESIZE 100;

COLUMN operation FORMAT A35;
COLUMN target FORMAT A30;

SELECT operation,
       target,
       start_time,
       end_time
FROM   dba_optstat_operations
WHERE  start_time > SYSTIMESTAMP - INTERVAL '1' DAY
ORDER BY start_time DESC;

Review the operation and its start and end times. Also check the SQL*Plus output for any reported errors.

Practical Considerations

• Dictionary statistics collection can consume CPU and I/O; schedule it according to the database workload.

• Gather fixed object statistics under representative runtime conditions.

• Execution time depends on database size, workload, and available resources.

• These procedures collect optimizer statistics; they do not purge application data or reclaim storage.

• For Oracle E-Business Suite application schemas, follow the applicable EBS statistics gathering procedures separately.

Suggested Blogspot labels: Oracle 19c, Oracle DBA, Multitenant, PDB, DBMS_STATS, Dictionary Statistics, Fixed Object Statistics