RE: db_file_multiblock_read_count causing full scans to takelonger?

From: <oracle-l-bounce_at_freelists.org>
Date: Tue, 19 Dec 2006 21:52:06 +0100
Message-ID: <F2C9CCA71510B442AF71446CAE8AEBAF8B57BD@MSXVS04.trivadis.com>

Brandon

> Am I missing something, or isn't it best to just leave this parameter
> unset (default) in 10.2, in which case Oracle will automatically
adjust
> it to the max io size of your OS?

Mhmm... I don't like that for the simplest reason that the maximum is not the best value for performance! As the OP noticed...

> Along with computing system stats on a typical workload so you have an

> accurate mbrc, you should get the best of both worlds - accurate
costing
> and optimized sequential scans, right?

In some situations, as I just wrote, no.

> I have it unset on my 10.2 AIX 5.3 system and it has defaulted to 128
> with an 8k block size (1MB).

Notice that there are some limits... According to my tests the value of db_file_multiblock_read_count is calculated with the following formula:

min( DbCacheSize/(Sessions*DbBlockSize) , 1048576/DbBlockSize )

Regards,
Chris

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Dec 19 2006 - 14:52:06 CST