Normally data dictionary statistics in 9i is not required unless performance issues are detected. Metalink note where data dictionary gathering is discouraged (245051.1) dates back from 16-APR-2004, almost four years ago. In 10g data dictionary statistics are collected just the same as any other schema object. There are official documents that suggest the statistics gathering in 9i (216550.1). It is commonly suggested to gather data dictionary statistics in the Oracle Applications environments. In fact, when performing a 9i database upgrade in an applications database, it is listed as a post upgrade step.
In 10g this is an official recommendation ML (457926.1).
Known issues when data dictionary statistics are present in sys schema.It is suggested to gather data dictionary statistics in 9i under direct oracle support advice when performance problems are detected.
In catpach for 9.2.0.2, 9.2.0.3 and 9.2.0.4 there was an note documenting the issue that the catpatch process took too much time due to the existing of statistics
This document doesn't state that data dictionary statistics musn't be collected, it only suggest data dictionary statistics must be deleted prior to the upgrade process and once it finishes statistics can be collected once more.
In fact the official suggestion states this:
dbms_stats.delete_schema_stats('SYS'); -- prior to perform the upgrade process
dbms_stats.gather_schema_stats('SYS'); -- once the process has finished
Ref. Gathering Data Dictionary Statistics
Doc ID: Note:245051.1
There is anohter documented issue, listed back on 14-JAN-2005, which states that querying dba_free_space hangs if data dictionary statistics are collected, but the metalink note never states that statistics are not suggested, but it rather suggests a procedure to solve this issue.
Another documented issue dates back to 20-Aug-2003, and reported issues when working with portal 3.0.9.8.1 if data dictionary were present.
Identify if data dictionary statistics have been collected.If there are performance problems due to large data dictionary access, it is advisable to check if data dictionary statistics have been collected. It can be easily displayed by querying data dictionary tables belonging to sys.
select table_name, last_analyzed
from dba_tables where owner='SYS'
and table_name='FET$';
This other query is useful to know how many sys tables have statistics:
select distinct trunc(last_analyzed), count(*)
from dba_tables
where owner='SYS'
group by trunc(last_analyzed)
Upgrade guide.If collecting data dictionary statistics during the upgrade process is a time consuming task, it is advisable to collect them prior to perform the upgrade task. Both in Oracle 8i and 9i, the procedure that must be run is DBMS_STATS.GATHER_SCHEMA_STATS, in fact the procedure suggested is by means of EXEC DBMS_STATS.GATHER_DICTIONARY_STATS, which was added starting DB10gR1.
References and documentation.Complete Checklist for Manual Upgrades to 10gR2
Doc ID: Note:316889.1
Oracle Applications Release 11i with Oracle9i Release 2 (9.2.0)
Doc ID: Note:216550.1
Select COUNT(*) from DBA_FREE_SPACE Hangs
Doc ID: Note:121729.1
Initial Portal Page Request Takes A Lot of Time
Doc ID: Note:198471.1
Gather Optimizer Statistics For Sys And System
Doc ID: Note:457926.1
Is ANALYZE on the Data Dictionary Supported (TABLES OWNED BY SYS)?
Doc ID: Note:35272.1
Ref. How to quickly verify whether data dictionary statistics has been collected
Doc ID: Note:333175.1
