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

On Tue, 20 Nov 2001 12:00:53 +0100, "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:

>I thought this thread was based on trying to
>solve a problem on a single database

It is.

>on a specific database
>with specific set of values for the session parameters
>on a fixed set of data
>for a given set of values for the statistics
>
>(and I think that covers all the bases - and perhaps
>it ought to be noted on the FAQ)
>
>then the path with the lowest cost if the one
>which the cost-based optimizer thinks will be the
>fastest way to satisfy the query according to the
>specific requirements of the optimizer goal.

That's exactly the situation I have. Thanks for putting it so clearly.

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 don't *think* I've given it any misleading information (stats are up to date, histograms in place, init.ora parameters sane as far as I can tell) but I guess I must have - or could there be some other explanation?

Sorry it's taken so long to prune this down to a clear statement of my question - I hope this explains what I'm concerned about.

Paul.

(This feels so much like a case of "if you lie to the computer, it will get you" - but I didn't *mean* to lie, honest!) Received on Tue Nov 20 2001 - 09:39:15 CST