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

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 9 May 2007 04:06:48 -0700
Message-ID: <1178708808.075019.192600@w5g2000hsg.googlegroups.com>

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!
>
> Kind regards
>
> robert

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

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. 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.

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