Setting Nonstandard/Multiple Block Sizes

A block size of 8 KB is optimal for most systems. On the other hand, OLTP systems can use smaller block sizes (such as 4K) and DSS systems can use larger block sizes (such as 16K, 32K).

Guidelines for database block size:

1) You need to set Oracle Block size equal to or a multiple of the operating system block size.

2) If your rows are small and you use a large block size, when you fetch a block you may get lots of rows and waste your buffer cache. It may also cause block contention.

3) If your rows are large then you may need a large block size to prevent “chaining”.

4) Larger Oracle block sizes may give better index balancing with more rows in each branch.

Specially when both DSS and OLTP are to be utilized on the same system, instead of using same block size for all tablespaces, multiple block sizes can be used for improving disk IO performance.

Although, there’s only one DB_BLOCK_SIZE initialization parameter, Oracle Database can support up to four “additional” nonstandard block sizes.

Let’s see how we can create a tablespace which has a different block size than DB_BLOCK_SIZE. “Create tablespace” statement has a parameter for block size:

But when we issue the above command, we see an error message:

ORA-29339: tablespace block size 4096 does not match configured block sizes

Oracle doesn’t let us create this tablespace because we haven’t set the buffer cache for 4K blocks. To use nonstandard block sizes, we must configure sub-caches for all of the nonstandard block sizes that we want to use:

Syntax:

ALTER SYSTEM SET DB_nK_CACHE_SIZE=X;

Example:

After issuing the above command, we can successfully create our tablespace:

Tables and indexes created in this tablespace will be cached in our new buffer pool specially reserved for 4K data blocks.