RE: 10g slowdown
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
- To: "William.Blanchard@xxxxxxxxxx" <William.Blanchard@xxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 11 Dec 2008 18:49:34 -0500
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@xxxxxxxxxxxx<mailto:mark.bobak@xxxxxxxxxxxxxxx>
www.proquest.com<http://www.proquest.com>
www.csa.com<http://www.csa.com>
ProQuest...Start here.
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Blanchard William
Sent: Thursday, December 11, 2008 6:18 PM
To: oracle-l@xxxxxxxxxxxxx
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.
Other related posts:
