Oracle Block size Vs OS block size

[jmodic]

Originally posted by slimdave
I'd like to see some supporting evidence -- i don't believe that any overhead that might be associated with this block reconstruction would be enough to offset the loss in i/o performance from larger block sizes.

If you've got Jonathan Lewis's book "Practical Oracle 8i" somewhere near by, open it on page 192-193 (Chapter 10: "Files, Raw and RAID"). You'll see there in Table 10-1 the performance impact of block size missmatch. In short, with 8K OS block size, the time required to do 1,000 random writes to a file with DB_BLOCK_SIZE=4K was 25.7 seconds, while with DB_BLOCK_SIZE=8K it only took 15.6 seconds for the same operation. I think the points made there by Jonathan are quite sufficient supporting evidence that choosing DB_BLOCK_SIZE smaller than OS block size certanly bears some performance degradation for writes.

For those of you that don't have that book available, let me just quote Jonathan explanation what happens when DBWR has to write a block (let's say 4K database block) to the file (that resides on a file system that has 8K OS block size):

1. DBWR passes the Oracle block to the file system.
2. The file system calculates where to write it, but it can't write just 4K.
3. The filesystem therefore reads the surrounding 8k file-system block from disk.
4. Once the file-system block is in memory, the 4K from Oracle is coppied into it.
5. The file system then writes the updated 8K block to disk - whoops! The disk has moved on while the memory update was taking place
6. The file system has to wait for (at least) nother rotation to complete.

On average, a write to the file system that works the first time has to wait for half rotation for the right bit of disk to come under the write heads. If the Oracle block is smaller than the file-system block, a write to the file system has to work in two passes and has to wait on avarage 1.5 rotations.

P.S. It's not very clear from the original poster's question whether he uses "normal" block-based UFS, or maybe some newer extent-based Fast File System, like VxFS. I'm writing this with the assumption that he is talking about normal Unix File System,