Re: db_file_multiblock_read_count causing full scans to take longer?
- From: "Don Seiler" <don@xxxxxxxxx>
- To: "Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>
- Date: Tue, 19 Dec 2006 13:29:25 -0600
Christian,
FWIW I did do 10046 traces of non-parallel full scans using various
db_file_multiblock_read_count values, from 16, 32, 64, 128, and 256.
128 seemed to be where it topped out. However I'm finding the poor
performance at anything over 32 now.
Thanks for the PDF, I'll give it a read now.
Don.
On 12/19/06, Christian Antognini <Christian.Antognini@xxxxxxxxxxxx> wrote:
Don
> After having it set in our dev instances for a week, I raised our
> db_file_multiblock_read_count in production from 16 to 128 (based on
> documented 10046 traces). After getting tickets about certain
> operations being slow, I identified a select query against our NOTES
> table as doing a full scan, which it did previously as well. However
> the full scan now takes 133 seconds, as opposed to 8-10 seconds with
> db_file_multiblock_read_count of 16 or 32.
This is a known "issue". I copy pasted the following paragraph from a
paper that I wrote last year: "Multi-block reads are a performance
feature. Therefore, DB_FILE_MULTIBLOCK_READ_COUNT should be set to
achieve the best performance. To do so is important to recognize that
higher values don't provide better performance in all cases and, in
addition, that it makes no sense to exceed the maximum physical I/O
size. A simple full table scan with different values gives useful
information about the impact of this initialization parameter and,
therefore, assists in finding the "best" value."
For addition information see
http://www.trivadis.com/Images/CBOConfigurationRoadmap_tcm17-14317.pdf.
HTH
Chris
--
//www.freelists.org/webpage/oracle-l
Other related posts:
