Database Block Size



DB_BLOCK_SIZE is the SGA initialization parameter that is used to determine the size of database blocks in bytes.

Oracle recommends that your database block size match, or be multiples of your operating system block size. One can use smaller block sizes, but the performance cost is significant. The choice should depend on the type of application you are running. If you have many small transactions as with OLTP, use a smaller block size. With fewer but larger transactions, as with a Data warehousing (DW) application, use a larger block size.

db_block_size = integer ( Range of values 2048 to 32768, but may be limited by operating system)

Whats is the ideal block size for My database?

There is no standard block size suggested, Based on the type of database and applications, we need to choose the block size that suits our need.
Baiscally we can choose smaller,medium and large block size, the following oulines each.

Smaller (2k, 4k) 


Good for small rows with lots of wide-spread random access I/O (row lookups by index, etc.) ·
Reduces the chances for block contention and possibility of “Buffer Busy Waits” ·


Smaller blocks support a smaller number of entries in the Interested Transaction List (controlled by InitTrans and MaxTrans),
limiting the number of concurrent changes · Not recommended for large data rows (limits the number of rows stored per block and increases potential for row chaining) ·
Less data transfer per I/O call

Medium (8k) 


The most suitable “middle ground” between block contention, efficient data storage, and I/O throughput for general transaction processing systems ·

Suggested for OLTP databases

Memory and I/O can be wasted if a majority of data is accessed randomly and consists of very small rows (<100 bytes)

Larger (16k, 32k, etc.)

More data transfer per I/O call ·
More space for index key storage, reducing index height and improved index-based queries ·
Less chance for chained and/or migrated rows · Excellent for sequential data access, table scans, etc prevalent in DSS and reporting environments

Suggested for DW or reporting databases.

In OLTP environments, increases the potential for block contention and the possibility of “Buffer Busy Waits”, especially when updating traditional rollback segment and index leaf blocks