RE: Index becomes slower over the day

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Wed, 14 Jan 2009 06:42:51 -0500
Message-ID: <812DACBE72A448A9B50654985E1A6455_at_rsiz.com>

What Greg wrote, ditto, but also, if you do before index rebuild and after index rebuild traces (10046), where it the reduction in time? Could it be your workload is primarily on followup to recent transactions(rows) and the break in work for the rebuild means many blocks of recent transactions become "clean" and possibly cached rather than being having to be read from undo and sorted out from other rows in the block still in flux to a read consistent version?

That's just one possibility underlying the question of whether the cost of the actual index part of the access is faster after the rebuild or you're seeing side effects of the rebuild. That is a question of fact that may help direct you to a solution more palatable than continously rebuilding the index.

Regards,

mwf

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Greg Rahn
Sent: Wednesday, January 14, 2009 3:01 AM To: usn_at_usn-it.de
Cc: oracle-l_at_freelists.org
Subject: Re: Index becomes slower over the day

Can you provide a bit more detail?
- definition of the index (ddl)
- what "using" means and what "case" you are referring to (what plans you are referencing)
- some detail of the data/keys that are participating in the index (random, increasing values, etc)
- version of database
- is the workload insert only? or some deletes or updates?

On Tue, Jan 13, 2009 at 11:49 PM, Martin Klier <usn_at_usn-it.de> wrote:
> Dear list,
>
> I've got the situation, that using one particular index is fast and
> becomes slower. In detail, the index becomes slower over the day/week,
> and since it's an OLTP system, this is annoying. (CPU and IO cost are
> high). The index always stays "VALID".
>
> Rebuilding it immediately speeds up the whole case by factors of ten,
> without any change in the execution plan before or after. But I can't
> rebuild it on an hourly basis, of course!

-- 
Regards,
Greg Rahn
http://structureddata.org
--
http://www.freelists.org/webpage/oracle-l





--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jan 14 2009 - 05:42:51 CST