Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: SHOULD WE ANALYZE 9.2 SYS tables?


From: Lex de Haan <>
Date: Thu, 29 Jul 2004 22:05:34 +0200
Message-ID: <>

thanks Larry,
and just to take away one more misunderstanding, based on a quick private email chat with Tom Kyte, this is a verbatim quote from his reply:

8i -- don't, not necessary, not recommended.

9i -- you can, but as you would with any big change -- please test it first. don't just "do it" in production. it can be awesome, it can be horrible,
mostly it does "nothing" really

10g -- it happens for you, the cbo is the only game going really.

So Juan, I guess you misquoted or misunderstood Tom ...

Kind regards,

visit my website at

-----Original Message-----

[]On Behalf Of Wolfson Larry - lwolfs
Sent: Thursday, July 29, 2004 20:44
To: ''
Subject: RE: SHOULD WE ANALYZE 9.2 SYS tables?

 Well, that was rapid set of responses.

        Thanks everyone, I did read the note 35272.1 and that led me to 245051.1 below which is inline with what Wolfgang and Lex said. It also suggests to drop them while you're upgrading.

	Let me re-phrase my question.
	Who's had success analyzing and how did you do it?.
	All tables or some or?

	My earlier post said we did put RULE hint in V$LOCK and that did
improve performance dramatically for it.
	We run all kinds of apps and servers.  Oracle,SAP,LAWSON,PEOPLESOFT,
and scads more besides homegrown apps.

	Gathering Data Dictionary Statistics 245051.1

This article applies to Oracle9i and later releases.

It is possible to gather statistics on the Data Dictionary in earlier releases, however since, historically, there were some problems associated with this activity, statistics gathering has been discouraged.

See <Note:35272.1> Is ANALYZE on the Data Dictionary Supported, for details.

Gathering Statistics on the Data Dictionary

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');

Note: There is an issue with Patch Set Release u pgrades in Oracle 9.2 and existence of statistics in the SYS schema. The following warning appears in Release Notes for 9.2 Patch Sets:

Upgrade and SYS schema

There is a generic issue applicable to the upgrade mode, for example, alter database open migrate. During an upgrade from release to release, release, or release, the catpatch.sql script can take a long time if there are statistics for the SYS schema. The user should delete the statistics on all the objects in the SYS schema, and then re-collect the statistics after normal database open, if necessary.

To drop and recreate the statistics, run the following commands:



<Note:35272.1> Is ANALYZE on the Data Dictionary Supported.

The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited.
If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. Thank You.

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.

Archives are at FAQ is at

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.

Archives are at FAQ is at
Received on Thu Jul 29 2004 - 15:18:00 CDT

Original text of this message