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

Last updated on NOVEMBER 07, 2023

Applies to:

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

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.