How to check dictionary and fixed objects statistics (Doc ID 1474937.1)

Last updated on SEPTEMBER 19, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 to 12.1.0.2 [Release 10.2 to 12.1]
Information in this document applies to any platform.
Since version 10 it is recommended to gather statistics on the dictionary and the fixed objects.

Goal

This article describes how to check dictionary statistics including statistics on fixed objects. Since version 10g, statistics on the data dictionary are mandatory for the cost-based optimizer to work properly. Dictionary statistics include the statistics on the tables and indexes owned by SYS (and other internal RDBMS schemas like SYSTEM) and the statistics on the fixed objects. Fixed objects are the internal X$ tables and the so called dynamic performance views or V$ views which are based upon them. These are not real tables and indexes, but rather memory structures. The statistics for the fixed objects need to be gathered manually; they are not updated by the automatic statistics gathering.

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms