Question: I did play around with different settings
of db_file_multiblock_read_count within 10g with no appreciable results.
My setting of the db_file_multiblock_read_count parameter had almost no
effect on query performance, I guess this is related to a read ahead cache on os/filesystem/disk
device level with at least 128 blocks.
I am concerned about switching to sequential reads
with setting db_file_multiblock_read_count=0, using the new 10g feature
"Automatically Tuned Multiblock Reads".
Is it already reliable to use "Automatically Tuned
Multiblock Reads" with 10g or should one still run with an as high as possible
setting for db_file_multiblock_read_count? Are there any drawbacks having
db_file_multiblock_read_count on a high value ?
Answer: Oracle notes that
the cost of reading the blocks from disk into the buffer cache can be
amortized by reading the blocks in large I/O operations. The
db_file_multiblock_read_count parameter controls the number of blocks that are
pre-fetched into the buffer cache if a cache miss is encountered for a
particular block.
The value of this parameter can have a significant impact on
the overall database performance and it is not easy for the administrator to
determine its most appropriate value. Oracle Database 10g Release 2
automatically selects the appropriate value for this parameter depending on the
operating system optimal I/O size and the size of the buffer cache
The Oracle database improves the performance of tablescans
by increasing the number of blocks read in a single database I/O operation. If
your SQL statement is going to read all of the rows in a table, it makes sense
to return as many blocks as you can in a single read. In releases prior to
Oracle10G R2, administrators used the
db_file_multiblock_read_count
initialization parameter to tell Oracle how many block to retrieve in the single
I/O operation.
But setting the
db_file_multiblock_read_count parameter too
high can affect access path selection. Full table scans use multi-block reads,
so the cost of a full table scan depends on the number of multi-block reads
required to read the entire table. The more blocks retrieved in a single
multi-block I/O execution, the more favorable a tablescan looks to the
optimizer.
In releases prior to Oracle10G R2, the permitted values for
db_file_multiblock_read_count were platform-dependent. The most common settings
ranged from 4 to 64 blocks per single multi-block I/O execution.
The
DB_FILE_MULTIBLOCK_READ_COUNT parameter controls the number of blocks
pre-fetched into the buffer cache during scan operations, such as full table scan
and index fast full scan.
Oracle Database 10g Release 2 automatically selects the
appropriate value for this parameter depending on the operating system optimal
I/O size and the size of the buffer cache.
This is the default behavior in Oracle Database 10g Release
2, if you do not set any value for
the
db_file_multiblock_read_count parameter (i.e. removing it from your
spfile or init.ora file). If you explicitly set a value, then that value is
used, and is consistent with the previous behavior.
Chris Foot notes: In Oracle 10G R2, Oracle defaults the
db_file_multiblock_read_count to the maximum number of blocks that can be
effectively read. Although this value is also platform-dependent, Oracle
documentation states that it is 1 MB for most platforms. This 1 MB size allows
much more data to be read in a single operation in 10GR2 than previous releases.
In addition, this larger value does not make tablescans look more favorable to
the optimizer. As a result, it is now recommended to not set the
db_file_multiblock_read_count parameter and let the database determine the
number of blocks read in multi-block I/O operations.
References:
See these related notes on Oracle multiblock reads with
db_file_multiblock_read_count:
