WHAT IS DB_FILE_MULTIBLOCK_READ_COUNT?
- The db_file_multiblock_read_count initialization parameter determines the maximum number of database blocks read in one I/O operation during a full table scan. The setting of this parameter can reduce the number of I/O calls required for a full table scan, thus improving performance.
- While it may be tempting to set a very high value for Before changing the value of db_file_multiblock_read_count to improve overall performance, we must consider several factors before doing so.
- The total number of I/Os actually required to perform a full table scan depends on other factors such as the size of the table and whether parallel query is being used. The cost-based optimizer uses all of these factors, including db_file_multiblock_read_count to determine the cost of full table scans.
- The goal of setting the db_file_multiblock_read_count parameter is that table scans are performed in fewer, larger I/Os. This is done by evaluating the number of blocks required to complete each table scan over time, then adjusting the parameter so that on average, most scans can be performed in one I/O.
- Starting with Oracle10g Release 2 the DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter is now automatically tuned to use a default value when this parameter is not set explicitly. This default value 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 thatis equal to the maximum I/O size that can be performed efficiently divided bythe standard block size.
