Oracle 参数 DB_FILE_MULTIBLOCK_READ_COUNT 官方解释，作用，如何配置最优化建议
本站中文解释
DB_FILE_MULTIBLOCK_READ_COUNT参数指定每次网状I/O运算时Oracle所使用的数据块的个数。
该参数的默认值遵循如下规则：
64位系统：
– 对于大对象（大于 16KB）LOB字段，首选128。
– 对于普通表，如果每个块小于16K，首选128；如果每个块大于16K，首选16；
– 对于索引，如果每个块小于16K，首选128或32；如果每个块大于16K，首选8。
32位系统：
– 对于普通表，如果每个块小于8K，首选64；如果每个块大于8K，首选8；
– 对于索引，如果每个块小于8K，首选64或16；如果每个块大于8K，首选4。
正确设置方式：
（1）收集需要做查询的表和索引的全局数据路径，使用SELECT OWNER, SEGMENT_NAME,TABLESPACE_NAME, SEGMENT_TYPE, BLOCK_SIZE FROM DBA_EXTENTS;来收集表和索引所存储的表空间、每个块的大小等信息；
（2）通过上述获得的每个块的大小及Oracle推荐的设置，来确定每个表和索引的DB_FILE_MULTIBLOCK_READ_COUNT参数值。例如：如果每个块小于16k，那么给普通表设置为128，给索引设置为128或32；如果每个块大于16k，那么给普通表设置为16，给索引设置为8.
（3）使用ALTER SYSTEM SET DB_FILE_MULTIBLOCK_READ_COUNT=值 SCOPE=SPFILE;命令有效设值参数；
（4）如果参数的值已经发生变更，则需要重启Oracle实例才能生效
官方英文解释
DB_FILE_MULTIBLOCK_READ_COUNT specifies the maximum number of blocks read in one I/O operation during a sequential scan.
| Property | Description |
|---|---|
|
Parameter type |
Integer |
|
Default value |
The default value corresponds to the maximum I/O size that can be efficiently performed and is platform-dependent |
|
Modifiable |
|
|
Modifiable in a PDB |
Yes |
|
Range of values |
Operating system-dependent |
|
Basic |
No |
DB_FILE_MULTIBLOCK_READ_COUNT is one of the parameters you can use to minimize I/O during table scans. 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.
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 I/Os if you do not set this parameter. It will favor large I/Os 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, then Oracle uses the maximum.
See Also:
Oracle Database
Performance Tuning Guide for information about how setting this parameter incorrectly can cause excessive I/O waits for some execution plans
