RE: db_file_multiblock_read_count causing full scans to take longer?

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

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