speed in millions of operations per second
disk seek time in milliseconds
disk transfer time in bytes per millisecond
- MBRC is set to the actual value of db_file_multiblock_read_count.
- sreadtim is set to ioseektim + db_block_size / iotrfrspeed.
- mreadtim is set to ioseektim + db_file_multiblock_read_count * db_block_size / iotrfspeed
Therefore we are back to the problem of the settings of DB_FILE_MULTIBLOCK_READ_COUNT affecting explain plans. There is however an answer and the following explains why it could be worth not manually gathering system statistics in 10g.
If you ensure the parameter DB_FILE_MULTIBLOCK_READ_COUNT is not set and leave Oracle to set this to its default values; Oracle will not use this parameter in order to calculate the MBRC and will actually make a good effort of optimizing explain plans without the need to manually gather system statistics.
If DB_FILE_MULTIBLOCK_READ_COUNT is not set Oracle will use the value of the hidden parameter "_db_file_optimizer_read_count" for the MBRC.
However when you explicitly set db_file_multiblock_read_count to anything other than default (even if setting this to the same value which Oracle sets it to by default) this will then over-ride the hidden parameter so that db_file_multiblock_read_count is used for the MBRC. Of course this assumes that no system stats are manually gathered. If system stats are gathered then the parameter settings are not relevant and Oracle will use what ever the system stats figures are set to.
For example, with DB_FILE_MULTIBLOCK_READ_COUNT removed from the init.ora file and no manual gathering of system statistics; Oracle shows the following parameter values:
Oracle appears to have set the default value of DB_FILE_MULTIBLOCK_READ_COUNT to 128
I try an explain plan of:
select * from audit_data;
GOAL = ALL_ROWS Cost=431394 Cardinality=31420512 Bytes=7195297248
TABLE ACCESS FULL
Object owner=AUDIT Object name=AUDIT_DATA Cost=431394
Just to test the difference I then set db_file_multiblock_read_count to 128 (same as what appears in the default):
alter session set db_file_multiblock_read_count = 128
The explain plan then changes:
select * from audit_data;
GOAL = ALL_ROWS Cost=278886 Cardinality=31420512 Bytes=7195297248
TABLE ACCESS FULL Object owner=AUDIT Object name=AUDIT_DATA Cost=278886
So explicitly setting db_file_multiblock_read_count has caused the cost for the full table scan to have dropped; thus encouraging the optimizer to perform more full scans. This proves that Oracle was not using DB_FILE_MULTIBLOCK_READ_COUNT as the MBRC by default but only then did so when the parameter was explicitly set.
So in summary; in 10g it is worth considering to not gather system statistics but instead ensure the parameter db_file_multiblock_read_count is not set. Oracle should then calculate optimal explain plans based on appropriate derived system statistic values.
However, it is important to test performance levels with the above scenario and also with system statistics gathered. Based on this testing you should then decide which method provides the best performance for your environment.