Best Setting for Oracle Multiblock Read Count!!

Author: Jeremy

Page: 1 2 3

Multiblock Read Count is the average number of blocks in a multi-block read. DB_FILE_MULTIBLOCK_READ_COUNT parameter can be used to minimize I/O during table scans. Therefore optimal setting of this parameter is very much important in order to boost up performance of your database.

Setting of DB_FILE_MULTIBLOCK_READ_COUNT parameter is very critical and affects the cost and memory management of your system. db_file_multiblock_read_count should be set to correct value as you will get poor access path selection if you give the optimizer incorrect information. Setting too high value for this parameter can badly affect the performance of your system.

Oracle measures the time taken by reads and calculate costs based on these numbers. However one must be careful as if Oracle measures the MBRC value on a table that is cached in the storage system then can easily get bad values. sreadtim is the average time for a single-block read request in milliseconds and mreadtim is the average time for a multi-block read request in milliseconds and it can be tricky to get the best values for mreadtim and sreadtim.

The multiblock reads always read into the cold end of the LRU list. The memory used for multi-block reads from the buffer cache by Oracle are determined by the setting for Multiblock Read Count. We do not want much memory to be used for multiblock on a system with many concurrent users.

There are a number of approaches available on web that will help you determine the best setting for Multiblock Read Count Values. In this article we will discuss a few of them.

Determining Multiblock Read Count Value:

In order to determine db_file_multiblock_read_count for your port and Oracle version you simply have to set db_file_multiblock_read_count to a dummy value and Oracle will size it down for you.

SQL> alter session set db_file_multiblock_read_count = 1000;
Session altered.

SQL> select value from v$parameter where name = 'db_file_multiblock_read_count';

VALUE
-----------
128

Determining Multiblock Read Count Value based on Storage Configuration:

Traditionally best value for the Multiblock Read Count was dependent on your storage configuration. This was because without system statistics a single block disk request and a multi-block disk request have about the same cost for the Oracle optimizer.

[STRIPE WIDTH] X [STRIPE SIZE] / [DB_BLOCK_SIZE]. Continued...

Page:  1 2 3

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Improved Statistics Gathering in Oracle 11g
   DBA Tips for Verifying Oracle Data Replication!!
   Data warehouse vs. Database - Similar – different – better?
   DBA Tips for Oracle Freezing!!