Oracle Database - DB_FILE_MULTIBLOCK_READ_COUNT Parameter

> Database > Oracle Database

1 - About

This parameter determines how many database blocks are read in a single I/O (with a single operating system READ call) during a:

SQL parallel execution is generally used for queries that will access a lot of data, for example when doing a full table scan. Since parallel execution will bypass the buffer cache and access data directly from disk you want each I/O to be as efficient as possible, and using large I/Os is a way to reduce latency.

The optimizer uses this value to cost:

Larger values result in a cheaper cost for full table scans and can result in the optimizer choosing a full table scan over an index scan.

The upper limit for this parameter is platform-dependent. If you set DB_FILE_MULTIBLOCK_READ_COUNT to an excessively high value, your operating system will lower the value to the highest allowable level when you start your database.

If this parameter is not set explicitly (or is set is 0), the default value corresponds to the maximum I/O size that can be efficiently performed and is platform-dependent.

Maximum values generally range from 64 KB to 1 MB.

Advertising

3 - Example

To end up with 1 MB, for 8K block size, you may use

db_file_multiblock_read_count=128.
[email protected]>show parameter db_file_multiblock_read_count

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
db_file_multiblock_read_count        integer     128

4 - Documentation / Reference