How to quickly verify whether data dictionary statistics has been collected
(Doc ID 333175.1)
Last updated on AUGUST 04, 2018
Applies to:Oracle Server - Enterprise Edition - Version: 184.108.40.206 to 220.127.116.11
Information in this document applies to any platform.
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.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document