RE: System statistics and dbfmbrc
- From: "Connor McDonald" <mcdonald.connor@xxxxxxxxx>
- To: "'oracle-l'" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 8 Jan 2008 22:17:33 +0900
Hi Niall,
Our appproach here is
a) set db_file_mult... to 128 (because on our Sun system that's the best we
get via an 8k blocksize). We have been tinkering with 64 as well, because
our SAN admin has been mix and matching with 1m stripes and 512k
stripes...(Thanks Mr SAN-dufus)
b) we collect system stats each day during a 1hr "peak" period. (Stress
"collect" not "activate")
c) Once a month or so, we look at the mean for those samples, and try to
come up with sensible *constant* values for mbrc, sreadtim, mreadtim
...Personally, I don't give a toss how close the value we choose are
compared to the real world measurements - we just take a sample of our most
common/important end user application queries and make sure they will be
costed "optimally" under our system stats figures. For example, our current
values are sread=5, mread=10, mbrc=6 whereas the collected values for around
3, 2.5 and 4 respectively. (mreadtim faster than sreadtim probably due to
SAN read-ahread)
We stopped *automatically* activating system stats (which we used to do
fortnightly) because its so easy to have a massive impact on your plans
One unplanned for benefit of (b) was usefulness as a metric - eg when we
plotted our sreadtim over a 12 month time frame we got an almost perfect
relationship between degradation of sreadtim versus our organisation's
obsession with filling all existing disks to 100% before purchasing more...
Cheers
Connor
_____
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Niall Litchfield
Sent: Tuesday, 8 January 2008 8:07 PM
To: oracle-l
Subject: System statistics and dbfmbrc
All
I'm almost sure that I read an article linked to here, or perhaps just a
response, on the wisdom of setting dbfmbrc to an appropriately high value
(so that Oracle tries to read large chunks of disk at once in the event that
it does do a table scan) if system statistics are set (so the high dbfmbrc
doesn't figure in the cost calculations any more). I can't however find the
article. Is my memory going more than I thought or does such an article in
fact exist? If not can anyone think of any nasty side effects from following
a strategy like the one I outline above.
As a supplementary I'm intending that we spend some time getting system
stats "right" - following a suggestion made here a while ago - but then not
revisiting them unless the hardware changes. Do people do this, or do you
collect on a schedule?
--
Niall Litchfield
Oracle DBA
http://www.orawin.info
Other related posts:
