RE: What block size are you using for your new 9i data

Sorry, I'm a bit non-clued up on this "read ahead algorithm". Could I be a pain and ask for more details? Does the OS return one OS block if exactly one is requested, but if 2 are requested it thinks "aha! sequential scan" and goes and gets 4 or 8 or something?

The follow on is, does this mean you should use a (minimal) 2k block size on UFS, 512 bytes blocks, or is this read-ahead overhead a smaller performance hit than that of using a database block size which is too small for the application?

Thanks
- Bill.

At 08:48 26/04/02 -0800, you wrote:
>All,
>
>You always want to ensure that your DB_BLOCK_SIZE =
>File System Block Size. This is to avoid wasted I/O
>and also the case where the "read ahead algorithm" is
>triggered accidentally, when 1 Database Block results
>in multiple file system blocks being read from disk.
>
>If your application performs range scans, there is a
>high possibility that multiple "single database block"
>read requests to a set of contiguous blocks, may
>result in the "read ahead algorithm" performing 128K
>or 256K pre-fetches, even though your application may
>have not required all 128K or 256K.
>
>This problem is rampant on ufs file systems where the
>default block size is 512 bytes, and with a 8K
>DB_BLOCK_SIZE, it takes 16 file system blocks to store
>1 DB block on disk. However, even if you have advanced
>file systems and have a 1-is-to-2 ratio of DB block
>is-to FS blocks, you are still in danger of
>overloading your I/O sub-system, "under the right
>conditions".

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Bill Buchan
  INET: wbuchan_at_uk.intasys.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).