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

On Mon, 19 Nov 2001 18:10:37 +0100, "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:

>For a given SQL statement and optimizer goal, the
>lowest cost is supposed to be the fastest query path.
>So the answer to your question ought to be YES.

Oh, good - I thought I was going daft... :-)

>Inevitably, the calculations used by Oracle in estimating
>the cost do not always come to the right answer, so
>the answer to your question has to be qualified with
>'so long as nothing goes wrong, and Oracle does not
>get deceived by the stats it finds and ...'. This is why you
>can hint an access path, which is given a higher cost,
>but executes more quickly.

Right. I can see that. See below for a bit further on this, though...

>When using FIRST_ROWS, the total cost of execution
>is not the critical factor - the cost (time) of getting the first
>row from the result set is the critical factor. Consequently
>Oracle may find a FIRST_ROWS path __which it wouldn't
>otherwise discover__ that is nominally very expensive in
>terms of getting the whole result set, but cheap in terms
>of getting the first row.

Got it! That's the factor I was (probably) missing. I think I can see now why I'm seeing the results I am.

But just to take the point above about time vs cost a little further, are there any "common" reasons why this should happen? This seems to be a fairly general problem on this database - at least, I'm getting reports of general slowdowns since we started calculating stats for the data, so it doesn't seem to be limited to the one query. As I mentioned in another post, I've estimated stats on all the tables, and I've computed histograms where it seems appropriate, so the calculations should be getting "good" data.

I suspect that for some reason, the CBO's "logical" view of the world in terms of IOs isn't matching up with the "real world" of physical IO times and CPU usage, but I'm not sure where I should be looking to prove this. I see no sign of CPU or IO stress on the machine...

We could just delete the stats and go back to the RBO, but I'm not sure that's the best approach for the longer term.

Thanks for your help,
Paul. Received on Tue Nov 20 2001 - 09:18:03 CST