Re: System statistics and dbfmbrc
Date: Tue, 8 Jan 2008 07:37:59 -0500
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.
On Jan 8, 2008 6:07 AM, Niall Litchfield <niall.litchfield_at_gmail.com> wrote:
> 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