Determine Database Block Size for SQL 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.
  
  1. 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.
  2. 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.

There are two factors to consider in setting an appropriate block size:

1. number of rows requested and
2. 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.

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.

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.

The DB_BLOCK_SIZE initialization parameter specifies the standard block size for the database. This block size is used for the SYSTEM tablespace and by default in other tablespaces. Oracle Database can support up to four additional nonstandard block sizes. DB_BLOCK_SIZE Initialization Parameter" The most commonly used block size should be picked as the standard block size. In many cases, this is the only block size that you need to specify. Typically, DB_BLOCK_SIZE is set to either 4K or 8K. If you do not set a value for this parameter, the default data block size is operating system specific, which is generally adequate. You cannot change the block size after database creation except by re-creating the database. If the database block size is different from the operating system block size, ensure that the database block size is a multiple of the operating system block size. For example, if your operating system block size is 2K (2048 bytes), the following setting for the DB_BLOCK_SIZE initialization parameter is valid:

DB_BLOCK_SIZE=4096

• Parameter DB_BLOCK_SIZE:
  The parameter DB_BLOCK_SIZE multiplied by the parameter DB_BLOCK_BUFFERS determines the size of the buffer cache. BUFFER_POOL_RECYCLE and BUFFER_POOL_KEEP determine the size to be allocated to the RECYCLE and KEEP pools, respectively, from the buffer cache. When creating or altering tables and indexes, you can specify the BUFFER_POOL in the STORAGE clause.

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.