Re: I/O and db_file_multiblock_read_count

And what did I say that was different from your first sentence? Second sentence I think is wrong. I'll deliberately use explicit tag for parameter and system statistics MBRC value to reduce possible misinterpretation.

If Kevin would calculate statistics with MBRC(parameter)=16, he'd gotten MBRC/MBR time(statistics) figures that would make CBO generate more plans with full scans than in case he'd calculated it with MBRC(parameter)=128. This is because full scans in fact (not presumably) as twice as fast with MBRC(parameter)=16.

Let the number of multiblock reads required with MBRC(statistics)=16 be 20% more than MBRC(statistics)=128.

If after that Kevin would change MBRC(parameter) to 128 without recalculating MBRC/MBR time(statistics) with MBRC(parameter)=128, it would not only make full scans required to perform MBRC(statistics)=128 perform as twice as slow but it would also inflict 20% more slowed by x2 multiblock reads.

• Vlad

On 12/10/06, Mladen Gogala <mgogala_at_verizon.net> wrote:
>
>
> On 12/09/2006 10:34:42 AM, Vlad Sadilovskiy wrote:
> > I don't understand why people insist on gathering system statistics as
> > Hemant noted, instead of answering the question why higher MBRC
> parameter
> > resulted in longer run. MBRC from system statistics is used solely for
> CBO
> > purposes. The actual reading is done in accordance
> > with db_file_multiblock_read_count.
> >
>
> When you gather system statistics, the value of MBRC does not influence
> the optimizer any more. The biggest single problem with MBRC was that it
> used to make full table scans appear cheaper and its increase would slant
> your execution plans toward full table scan. Now, you can collect the
> optimizer
> statistics with MBRC=8 and increase it to 16 or 32 without the price to
> pay.
>
> --
> Mladen Gogala
> http://www.mladen-gogala.com
>
>

--
http://www.freelists.org/webpage/oracle-l