Data Blocks  «Prev  Next»
Lesson 2Determining an appropriate database block size
ObjectiveSet the db_block_buffers parameter

Determine Database Block Size

Using block size for tuning

As we know from experience with Oracle, tuning is usually a series of trade-offs. Oracle must fetch a data block from the disk or data buffer whenever row information is requested. These fetch requests often translate into high I/O for our database.
Because the OS resources to fetch a small block (2k or 4k) are not significantly different from the resources required to fetch a large block (8k or 16k), I/O can often be minimized by making your db_block_size as large as possible. Large block sizes mean that more RAM must be available in the db_block_buffers.

Oracle Minimum Block Size

Minimum block size should not be less than 8K.
Oracle performance benchmarks show that every type of Oracle application will benefit from larger block sizes, even if the rows that are being requested are very small. This is because Oracle index searches retrieve adjacent rows on a block, and because Oracle index range scans often request multiple rows on a single block.
The Oracle Database Administrator's Reference 10g Release 2 (10.2) for UNIX-Based Operating Systems notes these guidelines for choosing blocksizes in AIX:
Oracle recommends smaller Oracle Database block sizes (2 KB or 4 KB) for online transaction processing (OLTP) or mixed workload environments and larger block sizes (8 KB, 16 KB, or 32 KB) for decision support system (DSS) workload environments.

Adjusting Oracle Block Size

During read operations, entire operating system blocks are read from the disk. If the database block size is smaller than the operating system file system block size, then Input-Output bandwidth is inefficient. If you set Oracle Database block size to be a multiple of the file system block size, then you can increase performance by up to 5 percent. The DB_BLOCK_SIZE initialization parameter sets the database block size. However, to change the value of this parameter, you must re-create the database. To see the current value of the DB_BLOCK_SIZE parameter, run the SHOW PARAMETER DB_BLOCK_SIZE command in SQL*Plus.

Setting Database Block Size

You can configure Oracle Database block size for better Input-Output throughput. On IBM AIX on POWER Systems (64-bit), you can set the value of the DB_BLOCK_SIZE initialization parameter to between 2 KB and 32 KB, with a default of 4 KB. If Oracle Database is installed on a journaled file system, then the block size should be a multiple of the file system block size (4 KB on JFS, 16 KB to 1 MB on IBM Spectrum Scale (GPFS)). For databases on raw partitions, Oracle Database block size is a multiple of the operating system physical block size (512 bytes on IBM AIX on POWER Systems (64-bit)).
The Oracle Database Administrator's Reference 10g Release 2 (10.2) for UNIX-Based Operating Systems states the following guidelines for choosing blocksizes in AIX: Oracle recommends smaller Oracle Database block sizes (2 KB or 4 KB) for online transaction processing or mixed workload environments and larger block sizes (8 KB, 16 KB, or 32 KB) for decision support system workload environments.

Oracle only allows one db_block_size for the entire database. Hence the DBA must decide on a common block size to service most queries with a minimum of I/O. If your database server has enough memory, and your buffer hit ratio does not suffer because of the increased demands on the RAM, you usually will see performance improvements with large block sizes.

Choosing data block size

There are two factors to consider in setting an appropriate block size: number of rows requested and future reads from the database.
Number of rows requested
Since you have a limited amount of memory, fetching a large block when you only need a single row wastes a huge amount of RAM in the db_block_buffers, and may take space from other blocks. Fetching a small block size would use less space. Let us review the example below.

1) We set our db_block_size to 64K and populate our database with rows.
1) We set our db_block_size to 64K and populate our database with rows. Our average row length is 80 bytes.

2) We request a row, causing Oracle to read a 64K block into the data buffer.
2) We request a row, causing Oracle to read a 64K block into the data buffer.

3) For this buffer, we read our 80 byte row.
3) For this buffer, we read our 80 byte row.

  1. We set our db_block_size to 64K and populate our database with rows.
  2. We request a row, causing Oracle to read a 64K block into the data buffer.
  3. For this buffer, we read our 80 byte row

Database Buffer Cache

The database buffer cache holds copies of the data blocks read from the data files. The term data block is used to describe a block containing table data, index data, clustered data. Basically it is a block that contains data. All user processes concurrently connected to the instance share access to the database buffer cache. The database buffer cache is logically segmented into multiple sets which reduces contention on multiprocessor systems. This area of the SGA contains only the buffers themselves and not their control structures. For each buffer there is a corresponding buffer header in the variable area of the SGA. From Oracle8 release onwards the buffer cache contains three buffer pools for different type of data usage. They are
  1. DEFAULT,
  2. KEEP, and
  3. RECYCLE.

These three buffer pools have separate allocations of buffers and LRU lists that manage buffers.
  1. The RECYCLE buffer pool is used to store blocks that are virtually never used after the initial read. This pool eliminates the data blocks from the memory when they are no longer needed. This is more like a work area for the blocks.
  2. The KEEP pool is for the allocation of buffers for
    1. objects that are accessed with medium frequency or
    2. for those which a consistent response time is desirable.
    This buffer pool retains the schema objects data block in memory.
  3. The DEFAULT buffer pool contains data blocks from schema objects that are not assigned to any buffer pool as well as for the schema objects that are explicitly assigned to the DEFAULT pool.

Database Block Buffers

The database block buffers act as the holding area for data used by the user and DBWR processes. Any data that gets to the user from the database files, or data that goes into the database files from the user or other processes, passes through the database block buffers unless direct insert or direct read is used for data loading, sorting, or hashing operations. The database block buffers in releases prior to Oracle9i had to be of uniform size, 2, 4, 8, 16, or for 64 bit OS, the 32 kilobytes in size. From Oracle9i onwards, the database has a default database cache block size, but other sizes (2K, 4K, 8K, 16K, or 32K) can also be specified. Based on the tablespace size, appropriate Cache is employed to retrieve and manage the buffers in the SGA. In the RAC database system, the database block buffers from each of the participating instance, through the process of cache fusion[1], are merged to form a logical database block buffer area that becomes many times larger than could be supported in a single instance.

Future reads from the Data Block

If you read adjacent information from the data block, setting a large block size will reduce the number of I/Os per transaction.
For example, in a read from a range of customer records stored in time sequence, because the other customer rows are very likely to reside beside one another on the same data block, it is possible to complete the query with one I/O. In a range scan using an index, because index nodes are stored next to one another on the data block, rather than on several smaller blocks, scanning the index on one large block greatly reduces I/O.
Click the link below to view the pictorial illustration.

Oracle index scan
Our one I/O returns 800 times more data in a single row fetch.
The next lesson demonstrates how to optimize use of space within Oracle data blocks.
[1] Cache Fusion: Cache fusion is a feature of Oracle Real Application Clusters (RAC) that allows instances to share data blocks from each other's buffer caches. This can help to improve performance by reducing the need for disk I/O.