How to quickly verify whether data dictionary statistics has been collected
Last updated on SEPTEMBER 19, 2016
Applies to:Oracle Server - Enterprise Edition - Version: 126.96.36.199 to 188.8.131.52
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.
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