Statistics on Oracle-defined schemas like sys, ctxsys, dbsnmp, mdsys, etc are called dictionary statistics.

Why to gather dictionary statistics

Missing or wrong dictionary statistics can lead to performance issues from Oracle 10g onwards. For Oracle version 9i, Oracle discourages to collect dictionary statistics.

Blog Update for Oracle 12c (26-NOV-2016): CDB and PDBs have a dictionary and therefore CDB and PDBs require to have dictionary statistics.

When to gather dictionary statistics

Dictionary statistics should be gathered
  • after installation or upgrade of Oracle components or
  • after an installation/upgrade of the user-application with heavy changes to the Oracle dictionary (e.g. new application tables/views/etc.)
The statistics should be gathered during a time with less DB activity.
Auto task “auto optimizer stats collection” in 11g onwards (gather stats job in 10g) collects dictionary statistics automatically.

How to manage dictionary statistics

The following procedures can be run to gather dictionary statistics
  • dbms_stats.gather_dictionary_stats
  • dbms_stats.gather_database_stats(gather_sys=>TRUE)
  • dbms_stats.gather_schema_stats(‘SYS’)
    The procedure gathers stats for SYS schema. It has to be called for each schema, e.g. dbms_stats.gather_schema_stats(‘CTXSYS’), etc.

Dictionary statistics can be restored from an earlier run:
  • dbms_stats.restore_dictionary_stats
Additionally, it is possible to use the procedures for single table statistics:
  • dbms_stats.gather_table_stats
  • dbms_stats.set_table_stats

Dictionarys statistics can be deleted by running:

  • dbms_stats.delete_dictionary_stats

How to check for missing dictionary statistics

The following statements can be used to check if dictionary statistics have been gathered (last_analyzed must not be NULL).
For a quick check of statistics in the SYS schema:
SELECT table_name
     , num_rows
     , last_analyzed
FROM   dba_tables
WHERE  owner=’SYS’
AND    table_name=’FET$’;
Or list last_analyzed date for dictionary tables
SELECT owner
     , table_name
     , num_rows
     , last_analyzed
FROM   dba_tables
WHERE  owner IN (SELECT distinct schema FROM dba_registry)
ORDER BY last_analyzed;
Alternatively use gather_schema_stats procedure with option “list auto” to get a list of objects that require statistics
set serveroutput on
DECLARE
l_statstab DBMS_STATS.OBJECTTAB;
CURSOR c_schemas
IS
SELECT distinct schema
FROM   dba_registry;
BEGIN
      FOR s in c_schemas LOOP
            DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>s.schema,
  OPTIONS=>’LIST AUTO’,
  OBJLIST=> l_statstab);
            FOR i in 1 .. l_statstab.count LOOP
                  dbms_output.put_line(l_statstab (i).ownname ||
   ‘.’ ||
   l_statstab (i).objname);
            END LOOP;
      END LOOP;
END;
/
Table DBA_OPTSTAT_OPERATIONS contains executions from dbms_stats package performed at the schema and database level. The history may have been purged in the meantime though (retention time can be retrieved by DBMS_STATS.GET_STATS_HISTORY_RETENTION).
SELECT operation
     , target
, start_time
FROM   dba_optstat_operations
WHERE  operation IN (‘gather_dictionary_stats’,
 ‘gather_database_stats’,
 ‘gather_schema_stats’)
ORDER BY start_time;

Sample session

The following sample session shows different execution plans if
  • dictionary statistics are missing (additional overhead by dynamic sampling)
  • dictionary statistics are gathered
  • wrong dictionary statistics (row_nums = 50) are set.
SQL> select * from v$version;
 
BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
PL/SQL Release 11.2.0.2.0 – Production
CORE    11.2.0.2.0      Production
TNS for 64-bit Windows: Version 11.2.0.2.0 – Production
NLSRTL Version 11.2.0.2.0 – Production
 
SQL> exec dbms_stats.delete_dictionary_stats();
 
PL/SQL procedure successfully completed.
 
SQL>
SQL> explain plan for
  2  select count(*) from DBA_AUDIT_TRAIL;
 
Explained.
 
SQL>
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
—————————————————————————————————-
Plan hash value: 3092287877
 
—————————————————————————————————
| Id  | Operation                 | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————————————
|   0 | SELECT STATEMENT          |                       |     1 |    91 |  1375   (2)| 00:00:17 |
|   1 |  SORT AGGREGATE           |                       |     1 |    91 |            |          |
|*  2 |   HASH JOIN RIGHT OUTER   |                       |   193K|    16M|  1375   (2)| 00:00:17 |
|   3 |    TABLE ACCESS FULL      | STMT_AUDIT_OPTION_MAP |   270 |  3510 |     2   (0)| 00:00:01 |
|*  4 |    HASH JOIN RIGHT OUTER  |                       |   193K|    14M|  1372   (1)| 00:00:17 |
|   5 |     TABLE ACCESS FULL     | SYSTEM_PRIVILEGE_MAP  |   208 |  2704 |     2   (0)| 00:00:01 |
|*  6 |     HASH JOIN RIGHT OUTER |                       |   193K|    11M|  1368   (1)| 00:00:17 |
|   7 |      TABLE ACCESS FULL    | SYSTEM_PRIVILEGE_MAP  |   208 |  2704 |     2   (0)| 00:00:01 |
|*  8 |      HASH JOIN RIGHT OUTER|                       |   193K|  9818K|  1365   (1)| 00:00:17 |
|   9 |       TABLE ACCESS FULL   | AUDIT_ACTIONS         |   180 |  2340 |     2   (0)| 00:00:01 |
|  10 |       TABLE ACCESS FULL   | AUD$                  |   193K|  7363K|  1361   (1)| 00:00:17 |
—————————————————————————————————
 
Predicate Information (identified by operation id):
—————————————————
 
   2 – access(“AUD”.”LOGOFF$DEAD”=”AOM”.”OPTION#”(+))
   4 – access(“SPX”.”PRIVILEGE”(+)=(-“AUD”.”PRIV$USED”))
   6 – access(“SPM”.”PRIVILEGE”(+)=(-“AUD”.”LOGOFF$DEAD”))
   8 – access(“AUD”.”ACTION#”=”ACT”.”ACTION”(+))
 
Note
—–
   – dynamic sampling used for this statement (level=2)
 
29 rows selected.
 
SQL> exec dbms_stats.gather_dictionary_stats();
 
PL/SQL procedure successfully completed.
 
SQL> explain plan for
  2  select count(*) from DBA_AUDIT_TRAIL;
 
Explained.
 
SQL>
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
—————————————————————————————————-
Plan hash value: 2157125692
 
—————————————————————————————————-
| Id  | Operation                 | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————————————-
|   0 | SELECT STATEMENT          |                         |     1 |    27 |  1372   (2)| 00:00:17 |
|   1 |  SORT AGGREGATE           |                         |     1 |    27 |            |          |
|*  2 |   HASH JOIN RIGHT OUTER   |                         |   184K|  4868K|  1372   (2)| 00:00:17 |
|   3 |    INDEX FAST FULL SCAN   | I_STMT_AUDIT_OPTION_MAP |   270 |  1080 |     2   (0)| 00:00:01 |
|*  4 |    HASH JOIN RIGHT OUTER  |                         |   184K|  4147K|  1368   (1)| 00:00:17 |
|   5 |     INDEX FULL SCAN       | I_SYSTEM_PRIVILEGE_MAP  |   208 |  1040 |     1   (0)| 00:00:01 |
|*  6 |     HASH JOIN RIGHT OUTER |                         |   184K|  3245K|  1366   (1)| 00:00:17 |
|   7 |      INDEX FULL SCAN      | I_SYSTEM_PRIVILEGE_MAP  |   208 |  1040 |     1   (0)| 00:00:01 |
|*  8 |      HASH JOIN RIGHT OUTER|                         |   184K|  2344K|  1364   (1)| 00:00:17 |
|   9 |       INDEX FULL SCAN     | I_AUDIT_ACTIONS         |   180 |   720 |     1   (0)| 00:00:01 |
|  10 |       TABLE ACCESS FULL   | AUD$                    |   184K|  1622K|  1361   (1)| 00:00:17 |
—————————————————————————————————-
 
Predicate Information (identified by operation id):
—————————————————
 
   2 – access(“AUD”.”LOGOFF$DEAD”=”AOM”.”OPTION#”(+))
   4 – access(“SPX”.”PRIVILEGE”(+)=(-“AUD”.”PRIV$USED”))
   6 – access(“SPM”.”PRIVILEGE”(+)=(-“AUD”.”LOGOFF$DEAD”))
   8 – access(“AUD”.”ACTION#”=”ACT”.”ACTION”(+))
 
25 rows selected.
 
SQL>
SQL> exec dbms_stats.set_table_stats(ownname=>’SYS’, tabname=>’AUD$’, numrows=>50, no_invalidate
=>false);
 
PL/SQL procedure successfully completed.
 
SQL> explain plan for
  2  select count(*) from DBA_AUDIT_TRAIL;
 
Explained.
 
SQL>
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
—————————————————————————————————-
Plan hash value: 31628185
 
————————————————————————————————–
| Id  | Operation              | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————————————–
|   0 | SELECT STATEMENT       |                         |     1 |    27 |  1360   (1)| 00:00:17 |
|   1 |  SORT AGGREGATE        |                         |     1 |    27 |            |          |
|   2 |   NESTED LOOPS OUTER   |                         |    51 |  1377 |  1360   (1)| 00:00:17 |
|*  3 |    HASH JOIN OUTER     |                         |    51 |  1173 |  1359   (1)| 00:00:17 |
|*  4 |     HASH JOIN OUTER    |                         |    50 |   950 |  1358   (1)| 00:00:17 |
|*  5 |      HASH JOIN OUTER   |                         |    50 |   700 |  1356   (1)| 00:00:17 |
|   6 |       TABLE ACCESS FULL| AUD$                    |    50 |   450 |  1355   (1)| 00:00:17 |
|   7 |       INDEX FULL SCAN  | I_SYSTEM_PRIVILEGE_MAP  |   208 |  1040 |     1   (0)| 00:00:01 |
|   8 |      INDEX FULL SCAN   | I_SYSTEM_PRIVILEGE_MAP  |   208 |  1040 |     1   (0)| 00:00:01 |
|   9 |     INDEX FULL SCAN    | I_AUDIT_ACTIONS         |   180 |   720 |     1   (0)| 00:00:01 |
|* 10 |    INDEX RANGE SCAN    | I_STMT_AUDIT_OPTION_MAP |     1 |     4 |     1   (0)| 00:00:01 |
————————————————————————————————–
 
Predicate Information (identified by operation id):
—————————————————
 
   3 – access(“AUD”.”ACTION#”=”ACT”.”ACTION”(+))
   4 – access(“SPX”.”PRIVILEGE”(+)=(-“AUD”.”PRIV$USED”))
   5 – access(“SPM”.”PRIVILEGE”(+)=(-“AUD”.”LOGOFF$DEAD”))
  10 – access(“AUD”.”LOGOFF$DEAD”=”AOM”.”OPTION#”(+))
 
25 rows selected.
 
SQL>

See also my blog post on Oracle Fixed Object Statistics.

Additional Sources

  • How to gather statistics on SYS objects and fixed_objects? [ID 457926.1]
  • Gathering Data Dictionary Statistics (Oracle 9i) [ID 245051.1]