Oracle Database - DB_FILE_MULTIBLOCK_READ_COUNT Parameter
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:
- full table scans and
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.
Articles Related
Example
To end up with 1 MB, for 8K block size, you may use
db_file_multiblock_read_count=128.
dwh@gbrdwhs>show parameter db_file_multiblock_read_count
NAME TYPE VALUE
------------------------------------ ----------- ---------
db_file_multiblock_read_count integer 128