Automatically tuned multi-block reads in 10g

Question: I did play around with different settings of db_file_multiblock_read_count within 10g with no appreciable results. My setting of the db_file_multiblock_read_count parameter had almost no effect on query performance, I guess this is related to a read ahead cache on os/filesystem/disk device level with at least 128 blocks.

 I am concerned about switching to sequential reads with setting db_file_multiblock_read_count=0, using the new 10g feature "Automatically Tuned Multiblock Reads".

Is it already reliable to use "Automatically Tuned Multiblock Reads" with 10g or should one still run with an as high as possible setting for db_file_multiblock_read_count? Are there any drawbacks having db_file_multiblock_read_count on a high value ?

Answer:  Oracle notes that the cost of reading the blocks from disk into the buffer cache can be amortized by reading the blocks in large I/O operations. The db_file_multiblock_read_count parameter controls the number of blocks that are pre-fetched into the buffer cache if a cache miss is encountered for a particular block.

The value of this parameter can have a significant impact on the overall database performance and it is not easy for the administrator to determine its most appropriate value. Oracle Database 10g Release 2 automatically selects the appropriate value for this parameter depending on the operating system optimal I/O size and the size of the buffer cache

The Oracle database improves the performance of tablescans by increasing the number of blocks read in a single database I/O operation. If your SQL statement is going to read all of the rows in a table, it makes sense to return as many blocks as you can in a single read. In releases prior to Oracle10G R2, administrators used the db_file_multiblock_read_count initialization parameter to tell Oracle how many block to retrieve in the single I/O operation.

But setting the db_file_multiblock_read_count parameter too high can affect access path selection. Full table scans use multi-block reads, so the cost of a full table scan depends on the number of multi-block reads required to read the entire table. The more blocks retrieved in a single multi-block I/O execution, the more favorable a tablescan looks to the optimizer.

In releases prior to Oracle10G R2, the permitted values for db_file_multiblock_read_count were platform-dependent. The most common settings ranged from 4 to 64 blocks per single multi-block I/O execution.

The DB_FILE_MULTIBLOCK_READ_COUNT parameter controls the number of blocks pre-fetched into the buffer cache during scan operations, such as full table scan and index fast full scan.

Oracle Database 10g Release 2 automatically selects the appropriate value for this parameter depending on the operating system optimal I/O size and the size of the buffer cache.

This is the default behavior in Oracle Database 10g Release 2, if you do not set any value for the db_file_multiblock_read_count parameter (i.e. removing it from your spfile or init.ora file). If you explicitly set a value, then that value is used, and is consistent with the previous behavior.

Chris Foot notes:  In Oracle 10G R2, Oracle defaults the db_file_multiblock_read_count to the maximum number of blocks that can be effectively read. Although this value is also platform-dependent, Oracle documentation states that it is 1 MB for most platforms. This 1 MB size allows much more data to be read in a single operation in 10GR2 than previous releases.

In addition, this larger value does not make tablescans look more favorable to the optimizer. As a result, it is now recommended to not set the db_file_multiblock_read_count parameter and let the database determine the number of blocks read in multi-block I/O operations.

References:

See these related notes on Oracle multiblock reads with db_file_multiblock_read_count: