Re: System statistics and dbfmbrc

From: Niall Litchfield <>
Date: Tue, 8 Jan 2008 13:11:41 +0000
Message-ID: <>

Thanks Wolfgang.

On Jan 8, 2008 12:58 PM, Wolfgang Breitling <> wrote:

> I am sure that I have said something like that in the past and maybe
> written in
> some paper or post. I am not so sure about that strategy in ageneral
> sense
> anymore. It is true that having system statistics with MBRC uncouples the
> dbfmrc initialization parameter from the tablescan costing and allows one
> to
> set it purely for execution efficiency. But I found that different SANs
> have
> different "sweetspots" for multi-block reads. If you go away from that,
> even on
> the high side, multiblock read efficiency decreases again.

That matches up with the discussion in the thread Vlad referenced as well. In my defence I did say 'appropriately high' value. In the case of one of our SAN's the maximum effective value seems to be 25 (200k) which is just an odd number - the files in question are on ASM though. It does reinforce the idea that the testing of a sweet spot using dd or similar is a good plan - though all the tests I've seen proposed seem to involve only the one concurrent session

> Just my empirical
> gathering.
> As far as gathering system statistics I see two strategies, and I'm
> sitting on
> the fence at the moment as to which one to favour:
> a) gather system statistics into the stattab table on a regular basis,
> have a
> look at the trend and pick and set the dictionary system statistics based
> on
> that input. And that gathering can, and should, continue even after the
> initial
> setting of system stats since application workload also can have an
> influence on
> the system statistics - e..g. MBRC - not only hardware changes.

Good point.

> b) in 10.2 and up not gather system statistics but let it run with
> noworkload
> statistics and also take dbfmrc out of the init.ora and let Oracle let it
> fluctuate with the workload. That aspect of uncertainty still scares me a
> little.

Me too - it's one reason why I'm moving us to collecting (optimizer) stats monthly as a matter of course with more frequent collections the exception driven by proven need.

Thanks for your input.

Niall Litchfield
Oracle DBA

Received on Tue Jan 08 2008 - 07:11:41 CST

Original text of this message