Re: MBRC & db_file_multiblock_read_count

On Jan 17, 2:53 pm, "Yannick ROGER" <yannick.ro..._at_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 multiblock  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/cd/B19306_01/server.102/b14237/initparams047.htm
http://www.oracle.com/technology/pub/articles/lewis_cbo.html
http://www.google.com :)

Regards,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Thu Jan 17 2008 - 06:28:26 CST