DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;  s=s1024; d=yahoo.co.uk;  h=X-YMail-OSG:Received:X-Mailer:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID;  b=UnVaM8h97Ti8yhD9GviSyV/9OFJvWR0rjrjAp+h8iaT9+gYW0iWpj2THBacI6dwY1L0uxGVxuo6oajWRYAjt+VXQDLmnakIwEVHGkpkMsdYr8N1vHrWXxbfIuaCfl2qM0ShcOUqMGaIA8eNxaXhNu6YDDe34jQG899Gh0o8njaY=;
Date: Fri, 12 Dec 2008 09:37:54 +0000 (GMT)
From: hrishy <hrishys@yahoo.co.uk>
Subject: RE: 10g slowdown
To: "William.Blanchard@kohler.com" <William.Blanchard@kohler.com>,  "oracle-l@freelists.org" <oracle-l@freelists.org>, Mark.Bobak@proquest.com
In-Reply-To: <6AFC12B9BFCDEA45B7274C534738067F0913DF10@AAPQMAILBX02V.proque.st>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 8bit
Message-ID: <18192.86087.qm@web27406.mail.ukl.yahoo.com>
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-to: hrishys@yahoo.co.uk

Hi Mark

Any ideas on how a index would blow up.

Few i can think of are

1)BitMap indexes
2)Index on monotonically increasing sequences columns and later on the data is purged from the table

any others ?

regards
Hrishy


--- On Thu, 11/12/08, Bobak, Mark <Mark.Bobak@proquest.com> wrote:

> From: Bobak, Mark <Mark.Bobak@proquest.com>
> Subject: RE: 10g slowdown
> To: "William.Blanchard@kohler.com" <William.Blanchard@kohler.com>, "oracle-l@freelists.org" <oracle-l@freelists.org>
> Date: Thursday, 11 December, 2008, 11:49 PM
> Hi William,
> 
> In no particular order:
> 
> I assume you specify the range as inputs in the where
> clause, that limits the range scan?  Has that range of
> values gotten much larger recently?
> 
> How big is the index, relative to the table?  I recently
> ran into a case where an index was blowing up to over 4GB in
> size, even though it only indexed one column, and the entire
> table was only 136MB.  Perhaps some unfortunate DML has
> caused the index to blow up?  If this is the case, you could
> try a coalesce or even a rebuild, to see if it helps. 
> However, if that fixes it, I'd caution you against
> simply using periodic coalesces or rebuilds as a solution. 
> Getting to the root cause of the index blowing up in size
> would probably be beneficial.
> 
> What about the table itself?  If your execution plan
> indicates that the INDEX RANGE SCAN feeds a TABLE ACCESS BY
> ROWID step, you may need to look at data clustering in the
> table.  Has the data in the table been reorganized lately? 
> If so, perhaps you previously had data organization that
> benefitted this particular index order, and now the data
> clustering has been lost?  What is the clustering factor on
> the index?  How many blocks in the table?  How many rows in
> the table?
> 
> Just some stuff to think about, off the top of my head...
> 
> Hope that helps,
> 
> -Mark
> 
> --
> Mark J. Bobak
> Senior Database Administrator, System & Product
> Technologies
> ProQuest
> 789 E. Eisenhower, Parkway, P.O. Box 1346
> Ann Arbor MI 48106-1346
> +1.734.997.4059  or +1.800.521.0600 x 4059
> mark.bobak@proquest.com<mailto:mark.bobak@il.proquest.com>
> www.proquest.com<http://www.proquest.com>
> www.csa.com<http://www.csa.com>
> 
> ProQuest...Start here.
> 
> From: oracle-l-bounce@freelists.org
> [mailto:oracle-l-bounce@freelists.org] On Behalf Of
> Blanchard William
> Sent: Thursday, December 11, 2008 6:18 PM
> To: oracle-l@freelists.org
> Subject: 10g slowdown
> 
> 
> We have a query that began taking a long time about a week
> ago.  The program, in SAP, ran for 10 - 15 minutes but is
> now taking about 6 hours.  The table has 5.1 million rows. 
> The explain plan shows a simple index range scan.  We just
> reran statistics on the table and all indexes but no luck We
> are concentrating on the one query that took about 4.75
> hours.
> 
> Does anyone see something glaring or know of a simple test
> to locate the problem?
> 
> Let me know if you need any other info.
> 
> 
> Thank you,
> 
> William B.


--
http://www.freelists.org/webpage/oracle-l