RE: 10g slowdown

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_at_proquest.com> wrote:

> From: Bobak, Mark <Mark.Bobak_at_proquest.com>
> Subject: RE: 10g slowdown
> To: "William.Blanchard_at_kohler.com" <William.Blanchard_at_kohler.com>, "oracle-l_at_freelists.org" <oracle-l_at_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_at_proquest.com<mailto:mark.bobak_at_il.proquest.com>
> www.proquest.com<http://www.proquest.com>
> www.csa.com<http://www.csa.com>
>
> ProQuest...Start here.
>
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of
> Blanchard William
> Sent: Thursday, December 11, 2008 6:18 PM
> To: oracle-l_at_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