Re: CBO calculates lower cost, but runs slower...

On Tue, 20 Nov 2001 16:39:15 +0100, Paul Moore <paul.moore_at_atosorigin.com> wrote:

>Now, I change *one* parameter (OPTIMIZER_GOAL) to force the optimizer to
>choose a plan with a higher cost (ie, which it thinks will be slower -
>but it chooses it because it expects to get the first row back sooner).
>
>And in fact, the query runs faster. That is the core of my question.
>
>Clearly, the optimiser's opinion is wrong. But what confused it?

I think I've solved it. In actual fact, the speed difference wasn't related to the execution plan at all! It was related to *when* I ran the query.

It turns out that there is a large batch load job on the system, which runs in the background every hour. This job trawls through a large amount of data in doing its work - enough to use a reasonable portion of the buffer cache - none of which is part of my query. As a result, the buffer cache is effectively being flushed over a period of 25 minutes each hour.

It just so happened, that my "fast" queries were being run when I had the cache nicely populated, and my "slow" ones were during or just after the batch job. (Not quite that simple, but that's the basic effect).

Thanks to all on the newsgroup who gave suggestions, and in particular for keeping me focussed on the "bigger picture" rather than getting bogged down in the details of the individual query plan.

Now to decide whether to suggest a redesign of the system, or just to throw RAM at the buffer cache and hope :-)

Paul. Received on Wed Nov 21 2001 - 10:04:11 CST