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

The two main features are that Oracle does not distinguish between a buffer get and a disk read in terms of cost, and does not distinguish between a single block disk read and a multiblock disk read in terms of cost.

Consequently the CBO tends to over-price paths based on indexed access paths, even when the DBA can see that most of the data is buffered, and therefore more of the nominal disk reads will actually be buffered and therefore a couple of hundred times quicker than Oracle allows them to be.

The main 'tuning' problems with this are:

    db_file_multiblock_read_count set too high     ditto for sort_area_size
    ditto for hash_area_size

To counter this, Oracle 8 introduced

    index_optimizer_caching
to allow you to specify what percentage of a typical index would be cached, and

    index_optimizer_cost_adjust
which allows you, in effect, to specify how much of a typical table will be cached.

(Tim Gorman has a paper on the last two at http://www.evtdb.com which gives a different, though I think equally valid, interpretation of the latter parameter).

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases

Screen saver or Life saver: http://www.ud.com
Use spare CPU to assist in cancer research.

Paul Moore wrote in message ...


>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.

>