Re: db_file_multiblock_read_count and performance

Post, Ethan wrote:

> I think what he is trying to prove is that given two full table scans
> one should perform faster by adjusting the size of the multiblock reads.
> The right questions might be....
>
> 1) Is DBMRC informative in nature only (tells Oracle what the actual
> read count size is for OS)?

The db_file_multiblock_read count is an operational directive telling Oracle how many blocks it should try to read with a single IO when it does full scans. Unfortunately, prior to Oracle 9 and system statistics, the CBO also used this value to determine the cost of a full scan. When gathering system statistics from an actual workload, Oracle will observe how many blocks it typically / on average actually reads with a single IO during full scans. That number is then used by the CBO in full scan costing. That frees dfmrc to be a purely opreational parameter without the sideffect on access plan costing and choice.
>
> or
>
> 2) Does DBMRC actually influence the size of read count at IO level?
>

Yes. It sets the maximum # of blocks that Oracle is "allowed" to read with a single IO. The actual # of blocks it reads with each multiblock IO can be lower due to several factors.

-- 
Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
--
http://www.freelists.org/webpage/oracle-l