How to quickly verify whether data dictionary statistics has been collected (Doc ID 333175.1)

Last updated on SEPTEMBER 19, 2016

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 9.2.0.8
Information in this document applies to any platform.

Goal

During the course of investigation of database performance problems  it is  useful to know whether data dictionary statistics  are present and  when they were last collected.  When asked by Oracle Support,  DBAs often do not know the answer to this question. So to find out  any relevance of  presence or absence of the data dictionary statistics  to an existing problem  they  are suggested  to  delete statistics on the data dictionary  with dbms_stats.delete_schema_stats('SYS')  command,  verify effect to the problem,  then gather fresh  statistics with dbms_stats.gather_schema_stats('SYS') command. Then verify effect to the problem. 

This approach, however,  is not always  the best one.  It  requires significant  database resources,  and on a heavily loaded and  fairly big database it  may actually  aggrevate the problem.

One simple way to answer the question of presence/absence of the data dictionary statistics is based on the fact that  the data dictionary tables constitute   the SYS schema. 
Information about these tables is present in the DBA_TABLES view in the same manner as for all other tables  in the database. 

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