From oracle-l-bounce@freelists.org Thu Jul 29 15:18:00 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i6TKHjt15105 for ; Thu, 29 Jul 2004 15:17:55 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i6TKHZ615062 for ; Thu, 29 Jul 2004 15:17:45 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id ADBCB72EBC4; Thu, 29 Jul 2004 14:51:04 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 22544-55; Thu, 29 Jul 2004 14:51:04 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 0328872EBC5; Thu, 29 Jul 2004 14:47:40 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 29 Jul 2004 14:45:42 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D169172CC48 for ; Thu, 29 Jul 2004 14:43:30 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 20973-67 for ; Thu, 29 Jul 2004 14:43:30 -0500 (EST) Received: from ha-smtp1.tiscali.nl (smtp-b2c.tiscali.nl [195.241.80.19]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 86C6C72EAC9 for ; Thu, 29 Jul 2004 14:38:26 -0500 (EST) Received: from LAPTOPLEX (82-168-217-228-bbxl.xdsl.tiscali.nl [82.168.217.228]) by ha-smtp1.tiscali.nl (Postfix) with SMTP id 67CF37D8887 for ; Thu, 29 Jul 2004 22:05:44 +0200 (CEST) From: "Lex de Haan" To: Subject: RE: SHOULD WE ANALYZE 9.2 SYS tables? Date: Thu, 29 Jul 2004 22:05:34 +0200 Message-ID: MIME-Version: 1.0 Content-type: text/plain X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.6604 (9.0.2911.0) X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 In-Reply-To: <433A07749711884D8032B6A0AB115262C2BCD0@conmsx07.corp.acxiom.net> Importance: Normal X-Virus-Scanned: by amavisd-new at freelists.org Content-Transfer-Encoding: 8bit X-archive-position: 6462 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: lex.de.haan@naturaljoin.nl Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org 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@freelists.org [mailto:oracle-l-bounce@freelists.org]On Behalf Of Wolfson Larry - lwolfs Sent: Thursday, July 29, 2004 20:44 To: 'oracle-l@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 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 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@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 ----------------------------------------------------------------- -- 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@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 -----------------------------------------------------------------