Hi,

I'm working on Oracle 10g.

What is the diffrence between:
- MBRC setted by dbms_stats.set_system_stats or automaticly with
dbms_stats.GATHER_SYSTEM_STATS
and
- db_file_multiblock_read_count setted in int file?

Is the scope of these 2 parameters the same?
Is one override the other?
Do i have to set the same value for both of them?

Can you recommend a good doc about this?

Regards,

Yannick

On Jan 17, 5:53*am, "Yannick ROGER" <yannick.ro...@achatpro.com>
wrote:
> Hi,
>
> I'm working on Oracle 10g.
>
> What is the diffrence between:
> - MBRC setted by dbms_stats.set_system_stats or automaticly with
> dbms_stats.GATHER_SYSTEM_STATS
> and
> - db_file_multiblock_read_count setted in int file?
>
> Is the scope of these 2 parameters the same?
> Is one override the other?
> Do i have to set the same value for both of them?
>
> Can you recommend a good doc about this?
>
> Regards,
>
> Yannick

Paraphrased from "Cost-Based Oracle Fundamentals":
Oracle uses MBRC from the SYS.AUX_STATS$ for cost calculations, but
the runtime engine tries to use DB_FILE_MULTIBLOCK_READ_COUNT when
performing physical reads. On Start up, Oracle determines the
operating system's largest physical read size and silently uses that
to limit whatever value is set for DB_FILE_MULTIBLOCK_READ_COUNT.

I believe that Jonathan Lewis now recommends not setting (or at least
experimenting with not setting) the DB_FILE_MULTIBLOCK_READ_COUNT
parameter. See:
http://jonathanlewis.wordpress.com/2...tats-strategy/

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

On Jan 17, 2:53 pm, "Yannick ROGER" <yannick.ro...@achatpro.com>
wrote:
> Hi,
>
> I'm working on Oracle 10g.
>
> What is the diffrence between:
> - MBRC setted by dbms_stats.set_system_stats or automaticly with
> dbms_stats.GATHER_SYSTEM_STATS
> and
> - db_file_multiblock_read_count setted in int file?
>
> Is the scope of these 2 parameters the same?
> Is one override the other?
> Do i have to set the same value for both of them?
>
> Can you recommend a good doc about this?
>
> Regards,
>
> Yannick

MBRC in aux_stats$ shows *average* number of blocks read in multi-
block reads performed during the stats gathering period.
db_file_multiblock_read_count puts a cap on the maximum number of
blocks that should be read in single sequential read operation (and
note that if it's unset or set unrealistically high then actual value
will be limited to OS-dependent maximum i/o size, typically 1MB.)
Without system statistics, the CBO will use this parameter (augmented
a bit) in table scan cost calculations, but if system stats are there
the CBO will use them instead. If you explicitly set
db_file_multiblock_read_count, with or without system stats, Oracle
will try to satisfy it when actually reading the data even though it
may not be used when calculating the cost of a table scan.

More reading:
http://download-uk.oracle.com/docs/c...tparams047.htm
http://www.oracle.com/technology/pub...lewis_cbo.html
http://www.google.com

Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com