RE: SHOULD WE ANALYZE 9.2 SYS tables?
- From: "Lex de Haan" <lex.de.haan@xxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 29 Jul 2004 22:05:34 +0200
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,
Lex.
---------------------------------------------
visit my website at http://www.naturaljoin.nl
---------------------------------------------
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Wolfson Larry -
lwolfs
Sent: Thursday, July 29, 2004 20:44
To: 'oracle-l@xxxxxxxxxxxxx'
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.
**********************************************************************
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: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
-- Binary/unsupported file stripped by Ecartis --
-- Type: text/x-vcard
-- File: Lex de Haan.vcf
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
