RE: 10g slowdown

From: Bobak, Mark <>
Date: Fri, 12 Dec 2008 09:55:58 -0500
Message-ID: <>

Those are the two things that come to my mind, as well, Hrishy.

I'm actually fighting a similar situation. If I ever get it resolved, I'll post the solution. I suppose it could also be an Oracle bug.


From: hrishy []
Sent: Friday, December 12, 2008 4:37 AM
To:;; Bobak, Mark Subject: 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 ?


  • On Thu, 11/12/08, Bobak, Mark <> wrote:

> From: Bobak, Mark <>
> Subject: RE: 10g slowdown
> To: "" <>, "" <>
> 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
> ProQuest...Start here.
> From:
> [] On Behalf Of
> Blanchard William
> Sent: Thursday, December 11, 2008 6:18 PM
> To:
> 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.

Received on Fri Dec 12 2008 - 08:55:58 CST

Original text of this message