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

On May 9, 8:30 am, 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

Paraphrased during a second read through of "Cost-Based Oracle Fundamentals": Oracle uses MBRC from SYS.AUX_STATS$ for cost calculations, but the runtime engine tries to use DB_FILE_MULTIBLOCK_READ_COUNT to retrieve table data. Several of the other explanations were obtained from the same book or from Cary Millsap's "Optimizing Oracle Performance" book.

Definitely gather system statistics when Oracle is under a normal to heavy load. As the above shows, you may obtain wild statistics otherwise.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Wed May 09 2007 - 07:45:02 CDT