|
 |
|
Oracle db_file_multiblock_read_count
Don Burleson
|
10gr2 Note: Starting in
Oracle 10g release2, Oracle recommends not setting the
db_file_multiblock_read_count parameter, allowing Oracle to
empirically determine the optimal setting. For more details,
see my notes on
10gR2 automatically tuned multi-block reads.
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. The Oracle 10g release 2 Performance Tuning Guide page
14.4 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 mbrc value
gathered as part of workload statistics is thus useful for FTS
estimation."
Remember, the parameter db_file_multiblock_read_count
is only applicable for tables/indexes that are full scanned, but it
also effects the SQL optimizer in its calculation of the cost of a
full-table scan.
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
But how do we know the value of the max I/O chunk
size?
The maximum effective setting for
db_file_multiblock_read_count is OS and disk dependant. Steve
Adams, an independent Oracle performance consultant (see
www.ixora.com.au ), has
published a helpful script to assist you in setting an appropriate
level. This script conducts a test and sample actual I/O chunk
sizes on your server to aid you in setting
db_file_multiblock_read_count:
http://www.ixora.com.au/scripts/sql/multiblock_read_test.sql
|
