Re: formula for OPTIMIZER_INDEX_COST_ADJ

On Dec 11, 10:09 am, Helma <helma.vi..._at_hotmail.com> wrote:
> Hello David,
>
> Thanks! The value is 167. I only experimented with values below 100
> with the explain plans of troublesome SQL's . I guess i have to look
> at this.
>
> Thanks...

I must apologise, I had my 'formula' wrong; a better approximation is based upon the average values for singleblkrdtim and the difference between readtim and singleblkrdtim in v$filestat:

select round(avg(singleblkrdtim)/avg(readtim-singleblkrdtim)*100,0) optimizer_index_cost_adj
from v$filestat
/

This works for 9i and later releases, and coincides with the suggested method of calculation from Jonathan Lewis:

"Ideally you need to use something like Orion (the Oracle tool) or iozone to find out the relative speeds of (the equivalent of) your single block and multiblock I/O read sizes, then set optimizer_index_cost_adj to 100 * “single block read time” / “multiblock read time”."

Again, my apologies for any inconvenience this has caused.

David Fitzjarrell Received on Thu Dec 11 2008 - 11:58:57 CST