Oracle Database - DB_FILE_MULTIBLOCK_READ_COUNT Parameter

Card Puncher Data Processing

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.

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

Documentation / Reference





Discover More
Card Puncher Data Processing
Oracle - Controlling the Behavior of the Query Optimizer with initialization parameters

This section lists some initialization parameters that can be used to control the behaviour of the query optimizer. These parameters can be used to enable various optimizer features in order to improve...
Db File Scattered Read
Oracle Database - 'db file scattered read' wait event

db file scattered read is a wait event. It's a multiblock read into many discontinuous SGA buffers This event signifies that the user process is reading buffers into the SGA buffer cache and is waiting...
Card Puncher Data Processing
Oracle Database - FIRST_ROWS

FIRST_ROWS is an optimizer goal in order to ask it the best response time. You can instruct this instruction through: a hint or by setting the value of the optimizer goal In the two case, it instructs...
Card Puncher Data Processing
Oracle Database - Multiblock

Oracle Database - Multiblock



Share this page:
Follow us:
Task Runner