Use a large database block size

How does a large block size save I/O?

The figure above shows a hypothetical index in a database with an 8K block size. The figure below shows the equivalent index after the database has been rebuilt with a 16K block size. The most obvious difference is that the number of leaf blocks has been halved. As a direct result, the number of pointers to leaf blocks has been halved also. Because the number of leaf block pointers has been halved, the space required for the last level of branch blocks has been halved as well. Therefore the number of blocks at that level of the index has been reduced to one quarter. In general, for large indexes the space saving at each level of the B*-tree increases exponentially with the height of the tree. Thus the height of the tree is reduced by one level in some cases.

The shaded blocks in the figures illustrate the dual saving in logical reads due to a larger database block size. If the height of the tree is reduced, fewer block gets are required to traverse the tree from the root block to a leaf block. And in an index range scan, only half the number of single block reads are required to scan the leaf blocks.

Of course, these are savings in logical reads. But are there equivalent savings in physical reads as well? Of course, that depends on which blocks are cached. Because the index branch blocks tend to be well cached anyway, doubling the database block size benefits index range scans more than it benefits index probes.

What about the impact on the buffer cache?

• The number of buffers required in the RECYCLE buffer pool is not directly affected by the database block size. Therefore, nearly twice as much memory is required for this buffer pool.

• The amount of data cached in the KEEP buffer pool is unchanged, but more space is wasted in sparse blocks. So a little more memory is required in this buffer pool.

• However, far fewer buffers are required in the DEFAULT buffer pool for index branch blocks. (This is particularly true from version 8.1, because the new buffer cache LRU mechanism is more effective at caching branch blocks). So, despite that some space is used less effectively in segment header blocks and index leaf blocks, the memory requirements for this pool are greatly reduced.

Although circumstances vary, the reduction in the size of the DEFAULT buffer pool normally offsets the growth in the RECYCLE and KEEP buffer pools. This is illustrated in the figures above and below. So, contrary to popular belief, rebuilding a database with a larger database block size can be memory neutral and need not impair data caching. However, due care must be taken in the sizing of the buffer pools.

In view of this you should normally use a large database block size for unbuffered databases, particularly if your application is heavily dependent on the performance of index based access paths, particularly index range scans.

What other factors are there?

• If Oracle uses the readv() system call for multiblock reads, a larger database block size maximizes the size of multiblock reads. This is because these multiblock reads are constrained in size by the operating system limit on the number of buffers that may be passed to the readv() system call.

• In parallel server databases, the number of block gets, and thus the number of PCM instance lock acquisitions, is reduced.

• Overall data density is improved, because the total amount of space taken by the fixed portion of the block headers is reduced. However, more care may be needed in managing data block concurrency because of the greater number of rows in each block. Ideally, any risk of high data block concurrency should be addressed by using partitioning strategies, rather than by intentionally compromising data density.

• The degree of row chaining required for very long rows is reduced.

• The degree of block chaining in the data dictionary clusters is reduced, as is the risk of block chaining in hash clusters used by the application for efficient primary key based look ups.

• The number of available process and transaction freelists per segment is increased. This can be helpful for applications with very high delete and insert concurrency requirements, although partitioning the key segments yields the same benefit.

• The size of the transaction tables in the rollback segment header blocks is increased. This reduces the risk of having to roll back to the transaction tables themselves during consistent read operations on other blocks. This in turn reduces the risk of snapshot too old errors due to an inability to roll back the transaction tables.

• Most transactions use fewer rollback segment data blocks. This means that they require fewer gets on the rollback segment header block, and thus the risk of contention for the header blocks is reduced.

• Fewer writes are required when blocks are modified in a sequential pattern, such as for data loads and global updates. This also applies to the rollback segment data blocks.

• Less redo is generated when blocks are modified sequentially, because of reduced block level overhead. However, much more redo may be generated if blocks are modified during online backups, unless the backups are performed via RMAN. This is because, for each series of changes to a block, the whole block must be logged as part of the block level overhead, to enable recovery should the backup contain any split blocks. However, RMAN backups do not contain split blocks, because RMAN compares the block header and footer, and re-reads any blocks that appear to be in flux.

  (Logging blocks during backups can of course be avoided by setting _log_blocks_during_backup to FALSE. But this should only be done if your datafiles are on file systems, your database block size is no larger than the file system block size, and your backups are performed through the file system code layer by a utility such as cpio or tar).

What about using multiple block sizes?

© Ixora Pty Ltd.   All rights reserved. 12-Oct-2007 22:22