| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: SHOULD WE ANALYZE 9.2 SYS tables?
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:
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,
Lex.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Wolfson Larry -
lwolfs
Sent: Thursday, July 29, 2004 20:44
To: 'oracle-l_at_freelists.org'
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 9.2.0.4 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.
Thanks Larry 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 9.2.0.1 to release 9.2.0.2, release 9.2.0.3, or release 9.2.0.4, 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:
dbms_stats.delete_schema_stats('SYS');
dbms_stats.gather_schema_stats('SYS');
RELATED DOCUMENTS
<Note:35272.1> Is ANALYZE on the Data Dictionary Supported.
 |
 |