Re: query on ditionary objects

> My guess would be oposite. According to Oracle Docs, you should not
> collect stats for objects in SYS schema in 9i or the database will creep.

It's supported in Oracle 9i(but normally not recommended:)).

For more information MetaLink Note 245051.1:

<quote>
"Gathering statistics on the Data Dictionary in Oracle9i is a supported activity.
If performance (or other) problems are detected following statistics gathering then these should be raised up with Oracle Support for resolution.

In the majority of cases, gathering Data Dictionary statistics should not be necessary (and would not normally be recommended) since the dictionary has been optimized to cater for most common database setups. If, however, the performance of queries against the data dictionary becomes a issue (for example because the database is made up of an abnormally large number of application objects) then gathering dictionary statistics can be considered.

Data Dictionary Statistics should only be gathered using the DBMS_STATS package.
Typical commands for gathering and removing Data Dictionary statistics are:  execute dbms_stats.gather_schema_stats('SYS');

Data Dictionary statistics can be removed using:  execute dbms_stats.delete_schema_stats('SYS');"  </quote>

Cheers,
Dimitre Radoulov

--
http://www.freelists.org/webpage/oracle-l