RE: 10g slowdown

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 Received on Thu Dec 11 2008 - 17:49:34 CST