Re: db_file_multiblock_read_count and 10g

From: Nuno Souto <>
Date: Sun, 25 Jan 2009 20:15:12 +1100
Message-ID: <>

Greg Rahn wrote,on my timestamp of 25/01/2009 6:40 PM:

> 10gR2 introduced Self-Tuning Multiblock Read Count

I'm still not sure that is the case. The specific parameter doco says that if not specified, it is set to a default value. It might be "blahblah" for others but it isn't of necessity auto-tuned.

*IF* there are system stats and *IF* the param is not specified, it might indeed be auto-set. But as Syed Jaffar Hussein pointed out and I have also observed a number of times, it is anything but "tuned". In fact in many cases of auto-setting, it is just plain wrong.

> The basic premise is to allow large I/Os for MBRC operations w/o
> giving the Optimizer too much preference to FTS. By default it uses a
> MBRC of 8 for costing, or the system stats MBRC if it is set.

This is where I think this whole thing comes of the rails. MBRC should never be taken as a parameter that controls or determines FTS by itself. All it should do is *WHEN* a FTS happens, set the I/O size to a value that makes it faster for the hardware to read large amounts of data.

In other words: MBRC's original purpose was to make FTS faster, it did not by itself determine its onset. That in some cases it does indeed trigger FTS only shows the horrible buggy state of some of the optimizer code.

In a way, the splitting of this parameter into two hidden ones may indeed in future cause the whole thing to start behaving appropriately. The splitting into an "execution" parameter and an "optimizer" parameter is what was needed. One optimizes the IO when a FTS is taking place, the other one influences the onset of FTS.

Not sure though there is anything "auto-tuned" about it at this stage, from what I've seen... But like Dion said: compare both auto and manual setting and see which is best. So far, I've been getting same or better performance setting it to the SAN's stripe block size rather than letting it set itself. here.

Nuno Souto
in sunny Sydney, Australia
Received on Sun Jan 25 2009 - 03:15:12 CST

Original text of this message