RE: System statistics and dbfmbrc
- From: "Ukja.dion" <ukja.dion@xxxxxxxxx>
- To: <vlovsky@xxxxxxxxx>, <niall.litchfield@xxxxxxxxx>
- Date: Tue, 8 Jan 2008 22:05:33 +0900
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Vlad Sadilovskiy
Sent: Tuesday, January 08, 2008 9:38 PM
To: niall.litchfield@xxxxxxxxx
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@xxxxxxxxx> 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/>
Other related posts:
