Re: db_file_multiblock_read_count and 10g
- From: Nuno Souto <dbvision@xxxxxxxxxxxx>
- Date: Sun, 25 Jan 2009 20:15:12 +1100
Greg Rahn wrote,on my timestamp of 25/01/2009 6:40 PM:
10gR2 introduced Self-Tuning Multiblock Read Count
http://download.oracle.com/docs/cd/B19306_01/server.102/b14214/chapter1.htm#FEATURENO05506
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. 10.2.0.3 here.
--
Cheers
Nuno Souto
in sunny Sydney, Australia
dbvision@xxxxxxxxxxxx
--
//www.freelists.org/webpage/oracle-l
Other related posts:
