Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Veritas Quickio and DB_BLOCK_SIZE

Re: Veritas Quickio and DB_BLOCK_SIZE

From: Gaja Krishna Vaidyanatha <>
Date: Thu, 04 Oct 2001 12:53:58 -0700
Message-ID: <>

Hi Satar & list,

To add to the issues and concerns that Jonathan has already so eloquently outlined, let me add a key factor that needs to be considered.

I/O tuning fundamentals require us to ensure that the filesystem blocksize = db_block_size. The default filesystem blocks size in Veritas is 1K and it is more than likely that almost every Veritas filesystem that is out there is in fact created with an 1K block size. This is true even though we are talking about Quick I/O which works on a Veritas-simulated raw device.

At any rate, creating an database (regardless whether it is OLTP or otherwise) with a db_block_size of anything other than the filesystem block size is a receipe for chronic I/O bottlenecks.

For example, if db_block_size were 2K and the filesystem block size were 8K, every 2K worth of I/O requested by Oracle, will actually result in the I/O sub-system performing 8K worth of I/O (essentially your system will be doing 4 times more I/O than what is required).

On the flipside, if you have db_block_size at 8K and the filesystem block size at 2K, you end up with a scenario, where every 8K I/O request by Oracle will cause 4 filesystem blocks to be read. When applications perform enough "single-block" index range-scans, it will cause the read-ahead algorithm to accidentally engage, resulting in 128K or even 256K worth pre-fetches, which results in "wasted I/O capacity". From a "real life" perspective, you will rarely find "true OLTP" systems these days. Most systems out there are hybrid in nature, OLTP during the day and DSS at night. And that is a huge factor to consider.

There have been many studies that have been done to measure and benchmark query performance, load time, index creation and transaction rate for various values of db_block_size. In every published study, it has been proved beyond a shred of doubt that each increase of db_block_size provides approximately 40% increase in performance (especially in query execution and large loads, which are significant for the batch window). Further, the height of an index is another "significant factor" in I/O performance during index scans, and it is dependent on db_block_size. The smaller the value of db_block_size, the larger is the "number of read system calls" Oracle will have to perform.

Bottom line, I personally will never create a database with a db_block_size less than 8K, regardless of whether we use Veritas or not, given the "hybrid" nature of most databases today. More importantly, the values of filesystem blocksize and db_block_size needs to be kept in strict equality. Failure to do so will result in severe I/O performance problems "by design".

While the 8K block size minimum is good for most hybrid systems of today, applications that are purely DSS, the recommendation of picking the largest possible db_block_size on that platform still holds good. When it doubt, go with the larger block size, as it is much easier to deal with block-level and row-level contention by proactively configuring INITRANS, MAXTRANS, PCTFREE and PCTUSED. Contention for the cache buffer chain and cache buffer lru latches can be dealt with by setting the relevant init.ora parameters.

In closing, I want to use the "car anology" in this thread - it is not relevant whether you recommend a Honda or a Benz(regardless of one's personal definition of "sex on wheels") when someone asks for a car recommendation. What matters is that you recommend a car that does not have an inherent "design problem".

Hope that helps,


Gaja Krishna Vaidyanatha
Director, Storage Management Products,
Quest Software, Inc.
Co-author - Oracle Performance Tuning 101

Do You Yahoo!?
NEW from Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month.
Please see the official ORACLE-L FAQ:
Author: Gaja Krishna Vaidyanatha

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: (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 Thu Oct 04 2001 - 14:53:58 CDT

Original text of this message