Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: What block size are you using for your new 9i data

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

From: Gaja Krishna Vaidyanatha <oraperfman_at_yahoo.com>
Date: Fri, 26 Apr 2002 12:13:32 -0800
Message-ID: <F001.00450FB7.20020426121332@fatcity.com>


Hi Bill & list,

The main function of a "read ahead algorithm" is to anticipate the nature of I/O requests on a given track of a disk's platter and see whether it is beneficial to "pre-fetch" some of the blocks, so that subsequent requests can be serviced from the either the controller's or the file system's cache, without having to "go to disk" multiple times.

The OS (or the sub-system) should normally return only the same number of blocks as requested. But, if there are multiple "read requests" from Oracle that are physically contiguous on disk and they also occur in a rapid succession, the OS or the I/O sub-system (as the case may be), "second guesses" the requestor's intent and assumes that more of the other blocks in the same track will also be requested, in the near future.

For a real "sequential scan", like in a full-table scan or an index fast-full scan, this is beneficial. But in the case of a range scan where only "a few contiguous blocks" are requested, pre-fetching 128K or 256K worth of data is wasteful use of a system's I/O resources. This is because, not all the blocks that is pre-fetched will be consumed.

The issue of an 8K DB_BLOCK_SIZE with say a 512-byte File System (or OS) Block Size, is that there is a 1-is-to-16 ratio between logical and physical blocks. So, for example if 4 Oracle blocks are requested, they translate into 64 FS (or OS) blocks. If these blocks are contiguous (and chances are good that leaf blocks in an index can be contiguous ), it becomes an "ideal condition" for the read-ahead algorithm to engage. So instead of servicing 32K of data, the sub-system retrieves 128K or 256K worth of data.

And, even if you have a 1-is-to-2 ratio between logical and physical blocks (DB_BLOCK_SIZE is 8K and FS Block Size is 4K), under the "right conditions", the read-ahead algorithm will engage and pre-fetch in a wasteful manner. So the bottom line is follows:

Keep DB_BLOCK_SIZE = FS(or OS) Block Size

This way, if Oracle requests for a few blocks in a track, the OS does not pre-fetch all of the blocks in the track. As mentioned before, in case of a "real sequential scan", the pre-fetch comes in goodstead.

Hope that helps,

Gaja


Gaja Krishna Vaidyanatha
Director, Storage Management Products,
Quest Software, Inc.
Co-author - Oracle Performance Tuning 101 http://www.osborne.com/database_erp/0072131454/0072131454.shtml

Do You Yahoo!?
Yahoo! Games - play chess, backgammon, pool and more http://games.yahoo.com/
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Gaja Krishna Vaidyanatha
  INET: oraperfman_at_yahoo.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).
Received on Fri Apr 26 2002 - 15:13:32 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US