How to quickly verify whether data dictionary statistics has been collected
(Doc ID 333175.1)
Last updated on NOVEMBER 07, 2023
Applies to:
Oracle Database - Enterprise Edition - Version 9.2.0.1 to 9.2.0.8 [Release 9.2]Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
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
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
Goal |
Solution |
References |