Oracle tuning is a series of trade-offs, and few decisions have a wider impact than selecting the right database block size. Oracle must fetch a
data block from disk or the data buffer cache whenever row information is requested. These fetch operations are the primary driver of I/O
in most Oracle databases. Because the operating system resources required 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 the DB_BLOCK_SIZE as large as
practical for the workload. Larger block sizes require more RAM to be available in the buffer cache, but the performance return on that investment
is typically significant.
Oracle allows only one DB_BLOCK_SIZE for the entire database. The DBA must choose a block size that services the majority of queries
with the minimum number of I/O operations. If the database server has sufficient memory and the buffer hit ratio does not suffer from the increased
RAM demand, larger block sizes consistently produce measurable performance improvements.
Oracle Minimum Block Size Recommendations
Oracle performance benchmarks demonstrate that every type of Oracle application benefits from larger block sizes, even when individual rows are
very small. The underlying reason is structural: Oracle index searches retrieve adjacent rows stored on the same block, and index range scans
frequently request multiple rows from a single block. A larger block captures more of this adjacent data in each I/O, reducing the total number
of disk reads required.
Oracle guidelines for block size selection by workload type are:
OLTP and mixed workloads — smaller block sizes (2 KB or 4 KB) reduce the memory footprint per block read.
DSS (Decision Support System) workloads — larger block sizes (8 KB, 16 KB, or 32 KB) maximize data returned per I/O.
In practice, Oracle recommends a minimum block size of 8K for most production environments. The performance benefit of moving from 4K to 8K
blocks is measurable across workload types, and the buffer cache overhead is manageable on modern hardware.
The DB_BLOCK_SIZE Initialization Parameter
The DB_BLOCK_SIZE initialization parameter sets the database block size at creation time. It applies to the SYSTEM and SYSAUX
tablespaces and is the default for all other tablespaces. This parameter cannot be changed without recreating the database, making it one of
the most consequential decisions in a new database build.
To view the current value in SQL*Plus:
SHOW PARAMETER DB_BLOCK_SIZE
Oracle Database supports up to four additional non-standard block sizes beyond the default. The most commonly used block size should be chosen
as the standard. Typically DB_BLOCK_SIZE is set to 4K or 8K. If the database block size differs from the operating system block
size, the database block size must be a multiple of the OS block size. For example, with a 2K OS block size, the following initialization
parameter setting is valid:
DB_BLOCK_SIZE=4096
When the database block size is aligned with the file system block size, I/O bandwidth efficiency improves. Misalignment — where the Oracle
block size is smaller than the OS block size — causes the OS to read more data than Oracle requested, wasting I/O bandwidth. Setting the Oracle
block size to a multiple of the file system block size can increase performance by up to 5 percent.
On IBM AIX on POWER Systems (64-bit), DB_BLOCK_SIZE can be set between 2 KB and 32 KB, with a default of 4 KB. On journaled
file systems, 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).
On raw partitions, the Oracle block size must be a multiple of the OS physical block size (512 bytes on IBM AIX 64-bit).
Two Factors for Choosing Block Size
Two factors drive the block size decision for any Oracle database:
Number of rows requested per query
Likelihood of future reads from the same block
Number of rows requested
When a query needs only a single row, fetching a large block brings more data into the buffer cache than the query requires. This consumes
RAM that could hold other frequently accessed blocks. In theory, a smaller block size is more efficient for single-row lookups. In practice,
however, Oracle benchmarks consistently show that larger blocks outperform smaller blocks even for single-row access, because index adjacency
means the surrounding rows in the block are likely to be needed by subsequent queries in the same session or by concurrent sessions. The
buffer cache cost of the extra data is outweighed by the reduction in physical I/O on future requests.
Future reads from the data block
When adjacent rows in a table are read together — as in a range scan of customer records stored in date sequence — a large block size allows
more of those adjacent rows to be retrieved in a single I/O. Because the customer rows reside next to one another on the same large block,
the query can complete with one I/O instead of many. The same principle applies to index range scans: index nodes are stored adjacent to
one another on the block, so scanning a large index block retrieves far more index entries per I/O than scanning multiple small blocks.
Oracle reads an entire 64K data block from the tablespace into the RAM data buffer in a single I/O operation.
Individual rows are then retrieved from the buffer without additional disk reads.
One I/O returns 800 times more data than a single-row fetch, dramatically reducing I/O overhead for range-based workloads.
Database Buffer Cache
The database buffer cache
holds copies of data blocks read from data files. All user processes concurrently connected to the instance share access to the buffer cache.
The buffer cache is logically segmented into multiple sets to reduce contention on multiprocessor systems. For each buffer, a corresponding
buffer header exists in the variable area of the SGA.
In Oracle 23ai, buffer cache sizing is managed through Automatic Memory Management (AMM) or Automatic Shared Memory Management (ASMM),
which dynamically resize the buffer cache based on workload demand. The historic DB_BLOCK_BUFFERS parameter — which set the
number of buffers explicitly — is superseded by DB_CACHE_SIZE under ASMM and by MEMORY_TARGET under AMM. For
environments still using manual configuration, the relationship remains: buffer cache size equals DB_BLOCK_SIZE multiplied by
DB_BLOCK_BUFFERS.
From Oracle 8 onward, the buffer cache contains three pools with separate buffer allocations and LRU lists:
The DEFAULT pool holds data blocks from schema objects not assigned to any specific pool, and objects explicitly
assigned to DEFAULT. Most workloads operate entirely within this pool.
The KEEP pool holds blocks for objects accessed with medium frequency or where consistent response time is required.
Blocks in the KEEP pool are retained in memory longer than in the DEFAULT pool. Use the KEEP pool for small reference tables that are
queried frequently but not frequently enough to remain permanently cached by the LRU algorithm alone.
The RECYCLE pool holds blocks that are rarely reused after the initial read. Blocks are flushed from the RECYCLE
pool as soon as they are no longer needed, preventing large full-table scans from polluting the DEFAULT pool and displacing more
valuable cached data.
When creating or altering tables and indexes, the target buffer pool is specified in the STORAGE clause:
ALTER TABLE orders STORAGE (BUFFER_POOL KEEP);
Database Block Buffers and the Buffer Holding Area
The database block buffers act as the holding area for all data moving between database files and user processes. Any data that reaches
the user from the database files, or that moves from the user into the database files, passes through the block buffers unless direct
insert or direct read is used for bulk data loading, sorting, or hashing operations.
Prior to Oracle 9i, database block buffers had to be of uniform size — 2, 4, 8, 16, or (on 64-bit OS) 32 KB. From Oracle 9i onward,
the database supports a default cache block size while also allowing non-default block sizes (2K, 4K, 8K, 16K, or 32K) for specific
tablespaces. The appropriate cache is selected based on the tablespace block size when retrieving and managing buffers in the SGA.
In Oracle Real Application Clusters (RAC), the database block buffers from each participating instance are merged through
cache fusion[1] into a logical buffer area that is many times larger than any single
instance could support alone. Cache fusion transfers block images between instances over the interconnect rather than forcing each
instance to read from disk, dramatically reducing I/O across the cluster.
Oracle Index Scan and Block Size
The block size effect on index performance is one of the strongest arguments for using larger blocks. In an index range scan, index
nodes are stored adjacent to one another on the data block. When those index nodes fit on a single large block, the entire range scan
completes in one or a few I/O operations. With smaller blocks, the same index entries are spread across more blocks, requiring more
I/O operations to traverse the same index range.
The practical result is significant: a single I/O with a 64K block returns 800 times more data than a single-row fetch from a 2K
block. For range-based queries — which are the dominant access pattern in most reporting and analytical workloads — this difference
directly translates to query response time.
The next lesson demonstrates how to optimize space usage within Oracle data blocks using PCTFREE, PCTUSED,
and segment space management.
[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, reducing the need for disk I/O across the cluster.
