RE: System statistics and dbfmbrc

I think OP¡¯s assumption(or memory?) is generally correct.  

One of the proof is that as of Oracle 11g, we have default value of 128 of dfmbrc.

(on my Windows version)
 

I didn¡¯t test it yet.

But if Oracle didn¡¯t totally changed the meaning dfmbrc, this means that Oracle recommends the appropriate high value of dfmbrc and let system statistics determine the mbrc value for optimizer.      

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Vlad Sadilovskiy
Sent: Tuesday, January 08, 2008 9:38 PM
To: niall.litchfield_at_gmail.com
Cc: oracle-l
Subject: Re: System statistics and dbfmbrc  

Niall,  

There was post named "I/O and db_file_multiblock_read_count" where this was discussed. In case you need it let me know.  

There were some discussion on Jonathan's blog. As to what would be the optimal figure of mbrc in aux_stats$ and that this affects only CBO. Actual mbrc is caped by extent boundaries and then by db_file_multiblock_read_count.  

I set db_file_multiblick_read_count to an "optimal" value beforehand according to a test on a clean, idle system and then let dbms_stats calculate aux_stats$ for different load types.

At this moment I regret not having only one thing - possibility to include this statistics on per segment basis.

Vlad Sadilovskiy
Oracle Database Tools
Web site: http://www.fourthelephant.com <http://www.fourthelephant.com/> Blog: http://vsadilovskiy.wordpress.com
<http://vsadilovskiy.wordpress.com/>    

On Jan 8, 2008 6:07 AM, Niall Litchfield <niall.litchfield_at_gmail.com> wrote:

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.orawin.info/>  

 



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