RE: SHOULD WE ANALYZE 9.2 SYS tables?
- From: "Juan Carlos Reyes Pacheco" <jreyes@xxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 29 Jul 2004 16:22:19 -0400
Content-Type: Text/Plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Thank for the upgrade Lex I was with 8i Tom advice. =0D
=0D
Juan Carlos Reyes Pacheco=0D
OCP=0D
-------Original Message-------=0D
=0D
From: oracle-l@xxxxxxxxxxxxx=0D
Date: 07/29/04 16:19:57=0D
To: oracle-l@xxxxxxxxxxxxx=0D
Subject: RE: SHOULD WE ANALYZE 9.2 SYS tables?=0D
=0D
thanks Larry,=0D
and just to take away one more misunderstanding,=0D
based on a quick private email chat with Tom Kyte,=0D
this is a verbatim quote from his reply:=0D
=0D
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=0D
8i -- don't, not necessary, not recommended.=0D
=0D
9i -- you can, but as you would with any big change --=0D
please test it first. don't just "do it" in production.=0D
it can be awesome, it can be horrible,=0D
mostly it does "nothing" really=0D
=0D
10g -- it happens for you, the cbo is the only game going really.=0D
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=0D
=0D
So Juan, I guess you misquoted or misunderstood Tom ...=0D
=0D
Kind regards,=0D
Lex.=0D
=0D
---------------------------------------------=0D
visit my website at http://www.naturaljoin.nl=0D
---------------------------------------------=0D
=0D
=0D
-----Original Message-----=0D
From: oracle-l-bounce@xxxxxxxxxxxxx=0D
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Wolfson Larry -=0D
lwolfs=0D
Sent: Thursday, July 29, 2004 20:44=0D
To: 'oracle-l@xxxxxxxxxxxxx'=0D
Subject: RE: SHOULD WE ANALYZE 9.2 SYS tables?=0D
=0D
=0D
Well, that was rapid set of responses.=0D
Thanks everyone, I did read the note 35272.1 and that led me to=0D
245051.1 below which is inline with what Wolfgang and Lex said. It also=0D
suggests to drop them while you're upgrading.=0D
=0D
=0D
=0D
Let me re-phrase my question.=0D
Who's had success analyzing 9.2.0.4 and how did you do it?.=0D
All tables or some or?=0D
=0D
My earlier post said we did put RULE hint in V$LOCK and that did=0D
improve performance dramatically for it.=0D
We run all kinds of apps and servers. Oracle,SAP,LAWSON,PEOPLESOFT,=0D
and scads more besides homegrown apps.=0D
=0D
Thanks=0D
Larry=0D
=0D
=0D
Gathering Data Dictionary Statistics 245051.1=0D
=0D
This article applies to Oracle9i and later releases.=0D
=0D
It is possible to gather statistics on the Data Dictionary in earlier=0D
releases, however since, historically, there were some problems associate=
d=0D
with this activity, statistics gathering has been discouraged.=0D
=0D
See <Note:35272.1> Is ANALYZE on the Data Dictionary Supported, for detai=
ls.=0D
=0D
Gathering Statistics on the Data Dictionary=0D
=0D
Gathering statistics on the Data Dictionary in Oracle9i is a supported=0D
activity.=0D
If performance (or other) problems are detected following statistics=0D
gathering then these should be raised up with Oracle Support for resoluti=
on.=0D
=0D
In the majority of cases, gathering Data Dictionary statistics should not=
be=0D
necessary (and would not normally be recommended) since the dictionary ha=
s=0D
been optimized to cater for most common database setups.=0D
If, however, the performance of queries against the data dictionary becom=
es=0D
a issue (for example because the database is made up of an abnormally lar=
ge=0D
number of application objects) then gathering dictionary statistics can b=
e=0D
considered.=0D
=0D
Data Dictionary Statistics should only be gathered using the DBMS_STATS=0D
package.=0D
Typical commands for gathering and removing Data Dictionary statistics ar=
e:=0D
=0D
execute dbms_stats.gather_schema_stats('SYS');=0D
=0D
Data Dictionary statistics can be removed using:=0D
=0D
execute dbms_stats.delete_schema_stats('SYS');=0D
=0D
Note: There is an issue with Patch Set Release u pgrades in Oracle 9.2 an=
d=0D
existence of statistics in the SYS schema.=0D
The following warning appears in Release Notes for 9.2 Patch Sets:=0D
=0D
Upgrade and SYS schema=0D
=0D
There is a generic issue applicable to the upgrade mode, for example, alt=
er=0D
database open migrate. During an upgrade from release 9.2.0.1 to release=0D
9.2.0.2, release 9.2.0.3, or release 9.2.0.4, the catpatch.sql script can=
=0D
take a long time if there are statistics for the SYS schema. The user sho=
uld=0D
delete the statistics on all the objects in the SYS schema, and then=0D
re-collect the statistics after normal database open, if necessary.=0D
=0D
To drop and recreate the statistics, run the following commands:=0D
=0D
dbms_stats.delete_schema_stats('SYS');=0D
=0D
dbms_stats.gather_schema_stats('SYS');=0D
=0D
RELATED DOCUMENTS=0D
<Note:35272.1> Is ANALYZE on the Data Dictionary Supported.=0D
=0D
=0D
**********************************************************************=0D
The information contained in this communication is=0D
confidential, is intended only for the use of the recipient=0D
named above, and may be legally privileged.=0D
If the reader of this message is not the intended=0D
recipient, you are hereby notified that any dissemination,=0D
distribution, or copying of this communication is strictly=0D
prohibited.=0D
If you have received this communication in error,=0D
please re-send this communication to the sender and=0D
delete the original message or any copy of it from your=0D
computer system. Thank You.=0D
=0D
----------------------------------------------------------------=0D
Please see the official ORACLE-L FAQ: http://www.orafaq.com=0D
----------------------------------------------------------------=0D
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx=0D
put 'unsubscribe' in the subject line.=0D
--=0D
Archives are at //www.freelists.org/archives/oracle-l/=0D
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html=0D
-----------------------------------------------------------------=0D
=0D
=0D
-- Binary/unsupported file stripped by Ecartis --=0D
-- Type: text/x-vcard=0D
-- File: Lex de Haan.vcf=0D
=0D
=0D
----------------------------------------------------------------=0D
Please see the official ORACLE-L FAQ: http://www.orafaq.com=0D
----------------------------------------------------------------=0D
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx=0D
put 'unsubscribe' in the subject line.=0D
--=0D
Archives are at //www.freelists.org/archives/oracle-l/=0D
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html=0D
-----------------------------------------------------------------
-- Binary/unsupported file stripped by Ecartis --
-- Type: Image/jpeg
-- File: 397220_backg_tile_new.jpg
----------------------------------------------------------------
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:
