Re: db_file_multiblock_read_count causing full scans to take longer?

On 12/19/2006 01:44:05 PM, Don Seiler wrote:
> 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.
>
> Granted, there needs to be some query tuning done (or a crutch index
> in the meantime) so that we aren't doing this full scan, but I thought
> that db_file_multiblock_read_count would make full scans take less
> time, if anything. Autotraces and query plans show similar numbers,
> as far as I can tell.

The db_file_multiblock_read_count parameter serves to determine the maximum length of the IO vector in readv call. The maximum size of single atomary I/O is different from OS to OS and from file system to file system. If your vector gets too big, it must be internally broken into multiple I/O requests, which brings I/O scheduler into play. So, OS has much more work to do then if you simply put few more vectors into readv call. That will slow you down. You do not want to increase MBRC above the maximum size of a single atomic disk IO on your platform.

-- 
Mladen Gogala
http://www.mladen-gogala.com

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