Re: mbrc (was: buffer cache - once again)

I suppose with mbrc you actually mean dfmrc (db_file_multiblock_read_count). In 9i there is a system_statistics value MBRC which is used by the CBO to cost full scans in place of dfmrc, provided you have collected system statistics. You can then set dfmrc to the highest value possible for your OS without incurring more full scans.

As for your problem, is it possible that you just hit a busy spot during your dfmrc=128 setting, or are those results repeatable? In the latter case it may be an efficiency deficiency on the part of the OS: maybe it is taking much longer to read 128 blocks than it does reading 16 blocks. What are the results for interim values - dfmrc=32, 64, 96 ?

At 10:36 AM 5/13/2003 -0800, you wrote:
>On somewhat related topic (well, not exactly and hence
>change of the subject line)
>
>I was also playing with Steve's multiblock setting to
>see how far can I push it/how extent boundaries affect
>it/how CBO changes its mind with regard to execution
>plan etc - all the fun stuff (BTW somebody mentioned
>here that in 9i there's another parameter that
>compensates for the high multiblock values, so messing
>with optimizer_index_* is not the only alternative.
>Does anybody know what it is?)
>
>What I see here is something that I can't seem to find
>any reasonable explanation for. With mbrc set at 16 it
>seems to work (according to statistics) twice faster
>than with mbrc set at 128 (well, actually set at 1000,
>but "correted" by Oracle to 128). Plans are the same -
>FTS for both (due to hint or lack of limiting clause).

Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Wolfgang Breitling
  INET: breitliw_at_centrexcc.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).