db_file_multiblock_read_count size – for the new oracle dba




you are a new oracle dba and you already know that the db_file_multiblock_read_count is used by oracle to specify the maximum number of blocks to read from disk in a full table scan. This parameter is important for performance tuning I/O(reads or writes to disk).

Below articles explain what is meant by the parameter and what is the normal size on most operating systems.

 

How is Parameter DB_FILE_MULTIBLOCK_READ_COUNT Calculated? [ID 1398860.1]

To Bottom


Modified:01-Mar-2013Type:HOWTOStatus:PUBLISHEDPriority:3
Comments (0)

 

 

In this Document
Goal
Solution
References


Applies to:

Oracle Server – Enterprise Edition – Version: 11.2.0.2 and later   [Release: 11.2 and later ]
Information in this document applies to any platform.

Goal

This document illustrate how Oracle calculate DB_FILE_MULTIBLOCK_READ_COUNT and the internal formula used.

Solution

From Oracle Documentation :

DB_FILE_MULTIBLOCK_READ_COUNT is one of the parameters you can use to minimize I/O during table scans. It specifies the maximum number of blocks read in one I/O operation during a sequential scan. The total number of I/Os needed to perform a full table scan depends on such factors as the size of the table, the multiblock read count, and whether parallel execution is being utilized for the operation.

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.

Online transaction processing (OLTP) and batch environments typically have values in the range of 4 to 16 for this parameter. DSS and data warehouse environments tend to benefit most from maximizing the value of this parameter. The optimizer is more likely to choose a full table scan over an index if the value of this parameter is high.

The maximum value is the operating system’s maximum I/O size expressed as Oracle blocks ((max I/O size)/DB_BLOCK_SIZE). If you set this parameter to a value greater than the maximum, Oracle uses the maximum.

The formula as internally used is as below:

db_file_multiblock_read_count = min(1048576/db_block_size , db_cache_size/(sessions * db_block_size))

Without WORKLOAD stats, CBO computes multiblock reads as:

io_cost = blocks/(1.6765 * db_file_multiblock_read_count 0.6581 )
With WORKLOAD stats, then:

io_cost = blocks/mbrc * mreadtim/sreadtim

 

 

 

 

 

What Is The Value Of SSTIOMAX And DB_FILE_MULTIBLOCK_READ_COUNT In Oracle 8i, 9i,10g, 11g? [ID 291239.1]

To Bottom


Modified:01-Mar-2013Type:HOWTOStatus:MODERATEDPriority:3
Comments (0)

 

 

This document is being delivered to you via Oracle Support’s Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.

Applies to:

Oracle Server – Enterprise Edition – Version 8.1.7.4 and later
Information in this document applies to any platform.
***Checked for relevance on 10-Jul-2012***

Goal

What is the value of SSTIOMAX and DB_FILE_MULTIBLOCK_READ_COUNT in Oracle 8i, 9i,10g and 11g?

Fix

Till 8i the formula in the note below can be used for setting the db_file_multiblock_read_count value
Note 131530.1 – SSTIOMAX AND DB_FILE_MULTIBLOCK_READ_COUNT IN ORACLE 7 AND 8

In Release 9.2 and above; follow the explanation below:

Each version of Oracle on each port, is shipped with a preset maximum of how much data can be transferred in a single read (which of course is equivalent to the db_file_multiblock_read_count since the block size is fixed).
For 8i and above (on most platforms) this is 1Mb and is referred to as SSTIOMAX.
To determine it for your port and Oracle version, simply set db_file_multiblock_read_count to a nonsensical 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
So on this system (8.1.7 on Win2000, 8k blocksize), the maximum is 128 (or 1MB).
———————————————————————————

Let see how to set MBCR value.
The SSTIOMAX (max I/O) which is port specific and is 1MB for Solaris 64-bit (up to and including 10gR2)
The maximum block setting that would be picked up on Solaris 64-bit would be 1M

The lowest blocksize is 2k so the max mbrc would be 512 in this case.
However, the calculation for the read-ahead other then the standard blocksize would be limited by the read-ahead calculated from the standard blocksize.

For example:

std blocksize = 8k.
mbrc = 1M/8k = 16   …read -> 8k * 16= 128k

tablespace blocksize -> 4k
mbrc = 128k/4 = 32

———————————————————————————

std blocksize = 8k
mbrc = 1M/8k = 128

mbrc is limited to -> 1M/8k = 128
read is limited to -> 8k * 128 = 1M

tablespace blocksize -> 4k
mbrc = 1M/4 = 256

NOTE:
Up to and including 10.2.0.5, SSTIOMAX is 1M for for instance Solaris SPARC 64-bit and Linux
With 11gR2, SSTIOMAX has apparently been increased to 32M as DB_FILE_MULTIBLOCK_READ_COUNT can be set to 4096 with DB_BLOCK_SIZE=8192.

References

NOTE:1275525.1 – OpenVMS: DB_FILE_MULTIBLOCK_READ_COUNT and MAX I/O SIZE for Oracle RDBMS on hp OpenVMS
NOTE:30712.1 – Init.ora Parameter “DB_FILE_MULTIBLOCK_READ_COUNT” Reference Note
NOTE:841444.1 – How To Set DB_FILE_MULTIBLOCK_READ_COUNT in 10g
NOTE:957460.1 – Health Check Alert: DB_FILE_MULTIBLOCK_READ_COUNT should be set to recommended value (Only applies to data warehousing)
NOTE:131530.1 – SSTIOMAX AND DB_FILE_MULTIBLOCK_READ_COUNT IN ORACLE 7 AND 8

 

Author: admin