Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

RE: SHOULD WE ANALYZE 9.2 SYS tables?

From: Juan Carlos Reyes Pacheco <jreyes_at_dazasoftware.com>
Date: Thu, 29 Jul 2004 16:22:19 -0400
Message-Id: <41095C7B.000013.01220@CACHITOSS>


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_at_freelists.org=0D
Date: 07/29/04 16:19:57=0D
To: oracle-l_at_freelists.org=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_at_freelists.org=0D
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Wolfson Larry -=0D lwolfs=0D
Sent: Thursday, July 29, 2004 20:44=0D
To: 'oracle-l_at_freelists.org'=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_at_freelists.org=0D put 'unsubscribe' in the subject line.=0D --=0D
Archives are at http://www.freelists.org/archives/oracle-l/=0D FAQ is at http://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_at_freelists.org=0D put 'unsubscribe' in the subject line.=0D --=0D
Archives are at http://www.freelists.org/archives/oracle-l/=0D FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html=0D

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Thu Jul 29 2004 - 15:30:26 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US