RE: 10g slowdown
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
- To: "hrishys@xxxxxxxxxxx" <hrishys@xxxxxxxxxxx>, "William.Blanchard@xxxxxxxxxx" <William.Blanchard@xxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 12 Dec 2008 09:55:58 -0500
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.
-Mark
________________________________________
From: hrishy [hrishys@xxxxxxxxxxx]
Sent: Friday, December 12, 2008 4:37 AM
To: William.Blanchard@xxxxxxxxxx; oracle-l@xxxxxxxxxxxxx; 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 ?
regards
Hrishy
--- On Thu, 11/12/08, Bobak, Mark <Mark.Bobak@xxxxxxxxxxxx> wrote:
> From: Bobak, Mark <Mark.Bobak@xxxxxxxxxxxx>
> Subject: RE: 10g slowdown
> To: "William.Blanchard@xxxxxxxxxx" <William.Blanchard@xxxxxxxxxx>,
> "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
> 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@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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
