Re: SQL Tuning Regarding System CPU Stats

<andrew.markiewicz_at_gmail.com> wrote in message news:1179781175.347032.213730_at_y2g2000prf.googlegroups.com...
> Thanks Jonathon. I will check out your blog.
>
>> The net result is that multiblock reads are assumed to be more
>> expensive than single block reads by a reasonable factor - and
>> this tends to be good enough.
>
> This I find interesting. Isn't this the same effect as the OICA
> parameter? Relative cost of index access vs table scan.
> What's old is new again.
> It sounds like measuring the exact CPU stats is a panacea in theory,
> but in practice it is not stable enough to rely on, at least not yet.
>

There is a very important difference between using optimizer_index_cost_adj and using system statistics.

The former reduces the cost of single block reads along indexed access paths. First, this omits some cases of single block reads, secondly - and more importantly - scaling costs down makes rounding errors more significant; so Oracle is quite capable of choosing the wrong index, see:

    http://www.jlcomp.demon.co.uk/18_oica_i.html

The latter increases the cost of multiblock reads, which makes rounding errors less significant, and is therefore much safer.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

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