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@xxxxxxxxxxx> 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

Other related posts: