Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Automatically tuned multi-block reads in 10g

Oracle Database Tips by Donald Burleson|
November 3, 2015


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.


See these related notes on Oracle multiblock reads with db_file_multiblock_read_count:

If you like Oracle tuning, you might enjoy my book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.