Re: problem with a query

From: John Gasch <jgasch_at_erols.com>
Date: 2000/04/05
Message-ID: <38EB98AB.35791841@erols.com>#1/1

When rows are deleted from a table, the index slots are merely marked as "deleted". If the index is on a monotonically increasing data value, such as a sequence number, then the index will grow without bound over time because the deleted slots won't get reused. The periodic rebuild works because it reclaims the space from the deleted rows.

Then why does the performance suddenly jump from minutes to hours, you ask? (This is a guess) As long as the entire index fits in the SGA cache, then performance will be fast because no I/O is necessary. However, once the index grows just beyond the point where it entirely fits in the SGA, then the uncached index blocks must be read from disk - orders of magnitude slower. If this is the case, and you have plenty of memory on the server, then increasing the DB_BLOCK_BUFFER parameter will increase the cache memory and thus decrease the frequency of rebuilds. (An LRU statistics report might confirm this).

John Gasch

---

kshave_at_health.gov.mb.ca wrote:
>
> I have a query that joins 3 tables together. The large table has
> around 3.5 million rows. The others are much smaller (a few thousand
> rows).
>
> This query runs once per week (Tuesday). Index and table statistics
> (estimate) are taken every Saturday. The query should only take
> approximately 5-7 minutes to run. However, I've had weeks where it runs
> for almost 10 hours. The last time this happened, I rebuilt one of the
> secondary indexes that it was using and the query ran in 5-7 minutes
> once again.
>
> This week the query once again has taken 10 hours. I looked at the
> index and it is only 2 levels deep (so it doesn't appear to be
> unbalanced). There has been data deleted from the table since the last
> index rebuild, but I don't think that this should cause any problems.
>
> I haven't tried rebuilding the secondary index again because I want
> to find out what the problem is. Any ideas?
>
> --
> -Keith
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Wed Apr 05 2000 - 00:00:00 CDT