RE: System statistics and dbfmbrc

Hi Niall,  

Our appproach here is  

1. 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)
2. we collect system stats each day during a 1hr "peak" period. (Stress "collect" not "activate")
3. 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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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 


--
http://www.freelists.org/webpage/oracle-l