Re: Parameter to influence Oracle's Idea of IO Cost?

On May 9, 2:30 pm, Robert Klemme <shortcut..._at_googlemail.com> wrote:
> On 09.05.2007 13:06, Charles Hooper wrote:
>
>
>
>
>
> > On May 9, 6:48 am, Robert Klemme <shortcut..._at_googlemail.com> wrote:
> >> Hi,
>
> >> this is on 10.2.0.1.0. I think I remember that there is a parameter
> >> that will affect how Oracle costs IO but can't seem to find it (searched
> >> DB Reference, Performance Tuning Guide, PL/SQL Package Reference, Web).
> >> Does my memory fail me?
>
> >> Do you know other ways to influence how Oracle costs single block reads
> >> vs. multi block reads (apart from DB_FILE_MULTIBLOCK_READ_COUNT and
> >> hinting that is)? Thank you!
>
> > There are several parameters, here are a few:
> > OPTIMIZER_INDEX_CACHING: Helps correct the optimizer's assumption that
> > all index reads are physical reads. This has an effect when multiple
> > tables are joined using nested loops with index access for the inner
> > (second) table, but also has an effect for cost calculation of in-list
> > iteration. This parameter does not affect the cost of a single table
> > indexed access path.
>
> > OPTIMIZER_INDEX_COST_ADJ: Reduces the cost of single block reads,
> > which tends to reduce the tendency of excessive tablescans. The
> > downside is that due to rounding errors, the optimizer may decide to
> > use the wrong index. This is a percentage multiplier of the original
> > cost of the index access cost: 6891 * 68% = new cost of 4685.88
>
> Yeah, I found those but believed there was a different parameter that
> directly affects SBR vs. MBR. Probably a case of stale memory. :-)
> However, your additional explanation is greatly appreciated as the
> Oracle docs are a bit sparse IMHO. Thank you!
>
> > CPU costing statistics, which are easily visible in a 10053 trace
> > file. If CPU costing statistics are gathered during a period of
> > limited activity, the system may generate odd numbers, which will
> > affect costing of single block versus multiblock reads.
>
> So are you suggesting to gather statistics during normal operation?
>
>
>
>
>
> > For instance:
> > -------------------------------------------------------------------
> > SYSTEM STATISTICS INFORMATION
> > *****************************
> > Using WORKLOAD Stats
> > CPUSPEED: 486 millions instructions/sec
> > SREADTIM: 1 milliseconds
> > MREADTIM: 10 millisecons
> > MBRC: 13.000000 blocks
> > MAXTHR: 18690048 bytes/sec
> > -------------------------------------------------------------------
>
> > The above will lead the cost based optimizer to believe that a single
> > block read will require 1ms, while a multiblock read will require
> > 10ms, and that 13 blocks can be retrieved in a single multiblock
> > read. I don't believe that DB_FILE_MULTIBLOCK_READ_COUNT is used for
> > cost calculations in Oracle 10g, but instead MBRC is used.
>
> So you are saying that the optimizer does not use the DB parameter but
> what it gathers from the statistics? Did I get that right?
>
> Thanks again!
>
> Kind regards
>
> robert- Hide quoted text -
>
> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

system statistics invalidate those two parameters. Tom Kyte, in one his volumes, states system statistics supersede those parameters and are a better solution.

--
Sybrand Bakker
Senior Oracle DBA