Re: System statistics and dbfmbrc
Date: Tue, 8 Jan 2008 13:11:41 +0000
On Jan 8, 2008 12:58 PM, Wolfgang Breitling <centrex_at_centrexcc.com> 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
> anymore. It is true that having system statistics with MBRC uncouples the
> dbfmrc initialization parameter from the tablescan costing and allows one
> set it purely for execution efficiency. But I found that different SANs
> 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
> 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
> that input. And that gathering can, and should, continue even after the
> setting of system stats since application workload also can have an
> influence on
> the system statistics - e..g. MBRC - not only hardware changes.
> b) in 10.2 and up not gather system statistics but let it run with
> 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
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 http://www.orawin.info -- http://www.freelists.org/webpage/oracle-lReceived on Tue Jan 08 2008 - 07:11:41 CST