Re: db_file_multiblock_read_count and 10g

From: Jared Still <jkstill_at_gmail.com>
Date: Fri, 23 Jan 2009 15:22:33 -0800
Message-ID: <bf46380901231522v382a752by267324047e86a820_at_mail.gmail.com>

On Fri, Jan 23, 2009 at 1:53 PM, Lou Avrami <avramil_at_concentric.net> wrote:

>
> In 9i these databases had the parameter db_file_multiblock_read_count set
> to 64. I believe that in 10g the general recommendation is to NOT set
> db_file_multiblock_read_count, that the database will dynamically determine
> to appropriate value for each unique transaction.
>
> Does db_file_multiblock_read_count get set automagically in 10gR2? Does
> anyone have any experiences they can relate in 10gR2 with it being defined
> dynamically, as opposed to explicitly setting it?
>

The docs may be helpful in this case:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams047.htm#CHDFAFHE

As of Oracle Database 10g release 2, the default value of this parameter is a value that corresponds to the maximum I/O size that can be performed efficiently. This value is platform-dependent and is 1MB for most platforms.Because the parameter is expressed in blocks, it will be set to a value that is equal to the maximum I/O size that can be performed efficiently divided by the standard block size. Note that if the number of sessions is extremely large the multiblock read count value is decreased to avoid the buffer cache getting flooded with too many table scan buffers.

*Even though the default value may be a large value, the optimizer will not favor large plans if you do not set this parameter*. It would do so only if you explicitly set this parameter to a large value.

One less knob to tweak, unless there is a good reason. :)

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

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

Received on Fri Jan 23 2009 - 17:22:33 CST