From oracle-l-bounce@freelists.org Thu Jul 22 15:06:35 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i6MK69V28038 for ; Thu, 22 Jul 2004 15:06:19 -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 i6MK5w628000 for ; Thu, 22 Jul 2004 15:06:08 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 5926872E2C6; Thu, 22 Jul 2004 14:45:05 -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 09579-34; Thu, 22 Jul 2004 14:45:05 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 95AE272D48A; Thu, 22 Jul 2004 14:45:04 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 22 Jul 2004 14:43:36 -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 2B6F072CFD9 for ; Thu, 22 Jul 2004 14:43:36 -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 08650-46 for ; Thu, 22 Jul 2004 14:43:36 -0500 (EST) Received: from rbgcon04.fnb.co.za (rbgcon03.fnb.co.za [196.10.116.13]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D5CEC72CE49 for ; Thu, 22 Jul 2004 14:43:34 -0500 (EST) Received: from rbgcon01.fnb.co.za (Not Verified[127.0.0.1]) by rbgcon04.fnb.co.za id ; Thu, 22 Jul 2004 21:56:58 +0200 Received: from rbgcon01.fnb.co.za ([127.0.0.1]) by rbgcon01.fnb.co.za with InterScan Messaging Security Suite; Thu, 22 Jul 2004 22:09:48 +0200 Message-ID: <31779D7666D8D11181BC0000F6B2EF7A0B929E0C@KRKMSG01> From: "Leonard, George" To: "'oracle-l@freelists.org'" Cc: "Desplace, Laura" Subject: Index thrashing still after Alter table move and new building of indexes. Date: Thu, 22 Jul 2004 22:09:41 +0200 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2656.59) Content-type: text/plain; charset=iso-8859-1 X-Virus-Scanned: by amavisd-new at freelists.org Content-Transfer-Encoding: 8bit X-archive-position: 5795 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: GLeonard@wesbank.co.za Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org Hi all quick question: Does an alter table xxx move tablespace do a block for block move or does it repack the table. we are having a problem on physical to logical IO on a specific 2 tables and specific 2 indexes on the tables for a specific update statement. It seems to be just thrashing/spinning its wheels when using these indexes. We rebuild the FK indexes on Tuesday morning and this resolved the problem where the update returned to under 10 min, but this morning it said expected to complete time of 7 hours, we just went thought a process of dropping all constraint and related indexes, moving the 2 tables to new tablespaces and building completely new indexes and it seems this did not fix the problem. We are wondering if the problem is not maybe actual data related since the analyzes did not return/report any errors. Oracle version is 9.2.0.4 on Sparc 64, Solaris 2.8 aka 8 Ideas, comments please George ________________________________________________ George Leonard Oracle Database Administrator New Dawn Technologies @ Wesbank E-mail:gleonard@wesbank.co.za You Have The Obligation to Inform One Honestly of the risk, And As a Person You Are Committed to Educate Yourself to the Total Risk In Any Activity! Once Informed & Totally Aware of the Risk, Every Fool Has the Right to Kill or Injure Themselves as They See Fit! -----Original Message----- From: Niall Litchfield [mailto:niall.litchfield@gmail.com] Sent: Thursday, July 22, 2004 21:56 PM To: oracle-l@freelists.org Subject: Re: Creating Histograms Comments as always On Thu, 22 Jul 2004 10:37:21 -0400, Freeman, Donald wrote: > OK, I understand your point about gathering on schedule. I'm moving into = > taking over a turn-key contractor developed system. We are doing = > stats/computed every day. We only add, at most, a few thousand records = > a day. This is much, much less than 10%. We converted a few million = > records, about five years worth of records, from four or five other = > public health databases but our daily accrual is relatively small. I = > probably wouldn't have to run stats once in a month. take note of how many records you add to relatively *small* tables. 13 rows added to one of our tables caused hell until we gathered stats again (and it took ages for anyone to admit that anything ahd changed). That would be 13 rows in the sense of another financial year to add to the 2 existing ones - so hardly significant at all :). I guess I'm saying different objects might have different stats needs. >We also don't = > collect system stats. I'm hoping to get enough information here to 'have = > a meeting' and get all of that changed, the method and rate of = > collection. Test system stats carefully (I'm probably too cautious on this), but system stats are likely to make quite a noticeable difference to execution times. It is, I'm increasingly convinced, the *right* thing to do. It doesn't mean that you may not have adverse effects. Overall system stats have been positive for our test financial environment- enough so that they get introduced with the next software upgrade that is running there - but there has been the odd hiccup. -- Niall Litchfield Oracle DBA http://www.niall.litchfield.dial.pipex.com ---------------------------------------------------------------- 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 ----------------------------------------------------------------- ___________________________________________________________________________________________________ The views expressed in this email are, unless otherwise stated, those of the author and not those of the FirstRand Banking Group or its management. The information in this e-mail is confidential and is intended solely for the addressee. Access to this e-mail by anyone else is unauthorised. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted in reliance on this, is prohibited and may be unlawful. Whilst all reasonable steps are taken to ensure the accuracy and integrity of information and data transmitted electronically and to preserve the confidentiality thereof, no liability or responsibility whatsoever is accepted if information or data is, for whatever reason, corrupted or does not reach its intended destination. ________________________________ ---------------------------------------------------------------- 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 -----------------------------------------------------------------