IMPORTANT: In
Oracle 10gr2 and beyond, it's critical to
un-set the db_file_multiblock_read_count
parameter and remove it completely from your pfile or spfile. If not, your
optimizer may choose unnecessary large table full-table scans.
Also see these important notes on
db_file_noncontig_mblock_read_count.
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 db_file_multiblock_read_count 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.
- Before 10g R2, DBA's used the db_file_multiblock_read_count
initialization parameter to tell Oracle how many block to retrieve in the
single I/O operation.
- Before 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.
Automatically tuned db_file_multiblock_read_count
Now in 10gr2 and beyond, Oracle introduces "automatically
tuned db_file_multiblock_read_count", an exciting new features that uses
external disk workload statistics (via dbms_stats.gather_system_stats) to
optimizer the setting for db_file_multiblock_read_count. The Oracle
Performance Tuning documentation notes:
""In release 10.2, the optimizer uses the value of mbrc when performing
full table scans (FTS).
The value of db_file_multiblock_read_count is set to the maximum
allowed by the operating system by default. However, the optimizer uses mbrc=8
for costing.
The "real" mbrc is actually somewhere in between since serial multiblock
read requests are processed by the buffer cache and split in two or more
requests if some blocks are already pinned in the buffer cache, or when the
segment size is smaller than the read size.
The db_file_multiblock_read_count value gathered as part of
workload statistics is thus useful for FTS estimation."
As a review, ?multi-block? reads are commonly
db file scattered read
operations, where the disk read-write head locates itself under the proper
cylinder, and sits there, as the disks rotation feeds the data blocks into
the Oracle SGA (or PGA if using parallel full-scans).
TA suboptimal setting for
db_file_multiblock_read_count can cause the optimizer to smile to favorably upon
full-scan access, causing some DBA to wrongly lower their setting for
optimizer_index_cost_adj
and set a higher value for
optimizer_index_caching,
both used as alternatives to dbms_stats.gather_system_stats in cases where
the optimizer ?wrongly? chooses full scan access over index access.
In 10gr2 and beyond, the
db_file_multiblock_read_count is
not used to estimate the average number of blocks read and a separate metric
for the estimated number of actual block reads.
Instead, the optimizer computes two new values, one for optimizer
costing and another for the number of I/O requests.
The average block reads for optimizer costing
_db_file_exec_read_count:
The actual number of block to read in real I/O operations
There are several hidden parameters relating to file I/O in multiblock
reads:
_db_file_exec_read_count
_db_file_noncontig_mblock_read_count
_db_file_optimizer_read_count
_sort_multiblock_read_count
_index_prefetch_factor
db_file_noncontig_mblock_read_count
In sum, it's important to ?unset? db_file_multiblock_read_count (by
removing it from the spfile), so that the optimizer can independently
compute these two estimates for multiblock I/O.
*********************************************
The combination of your db_block_size and the setting for
db_file_multiblock_read_count determine the "real" read sizes for an Oracle
database, and prior to Oracle 10g release 2, setting the
db_file_multiblock_read_count was tricky.
According to Oracle, this is
the formula for setting db_file_multiblock_read_count:
max
I/O chunk size
db_file_multiblock_read_count = -------------------
db_block_size
When you implement
multiple
blocksizes you should set your db_block_size based on the size of the
tablespace where your large-object full-scans will be occurring. Remember, the
parameter db_file_multiblock_read_count is only applicable for
tables/indexes that are full scanned.
See my related notes on db_file_multiblock_read_count:
and include the URL for the page.
