Re: formula for OPTIMIZER_INDEX_COST_ADJ
Date: Thu, 11 Dec 2008 09:58:57 -0800 (PST)
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.
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:
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