Re: OPTIMIZER_INDEX_COST_ADJ, Conceptual Idea

That's probably because the systems you see most frequently are built on filesystems and have a 16 block db_file_multiblock_read_count, don't use direct i/o, and possibly stripe at too fine a granularity.

In cases like this, Oracle issues a multiblock read for up to 16 blocks, the operating system splits the call into 16 separate reads for one block. Each read may turn into a full f/s overhead.

My typical experience has been a little different, a factor of about 4 seems to be quite common when running on file-system without direct i/o. Of course, this can very dramatically depending on the type of application and the amount of available memory - with big swings even on the same system during the course of a day.

--
Regards

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

Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____England______January 21/23
____USA_(CA, TX)_August


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





Don Burleson wrote in message
<998d28f7.0301201611.792c4759_at_posting.google.com>...


>OK, but why then do almost all Oracle systems show multi-block

>(scattered) reads having average waits that are 10x longer than index

>(sequential) reads?

>

>Anyone have an insight?

>

>Any link to this hotsos discussion?