Multiple Block Sizes in RAC environment Oracle Tips by Burleson Updated: 17 July 2015

Multiple Block Sizes in RAC environment

In RAC system, each instance can have its own type of buffer pools. Each instance can have its own set of non-standard block size buffers. The buffer pools can be of different sizes or not at all defined. It is recommended to design and tune each instance buffer according to the requirements placed by the application on that instance.

Supporting multiple block sizes in buffer cache is a SGA feature with the setting of one DB_nK_CACHE_SIZE parameter. You can specify up to four block sizes, in addition to a standard block size. The sizes and numbers of non-standard block size buffers are specified by the following parameters:

DB_2K_CACHE_SIZE
DB_4K_CACHE_SIZE
DB_8K_CACHE_SIZE
DB_16K_CACHE_SIZE
DB_32K_CACHE_SIZE

Example of Setting Block and Cache Sizes,

db_block_size=4096
db_cache_size=1024M
db_2k_cache_size=256M
db_8k_cache_size=512M

In the above example, the parameter DB_BLOCK_SIZE sets the standard block size of the database to 4K. The size of the cache of standard block size buffers will be 1024M. Additionally, 2K and 8K caches are also configured, with sizes of 256M and 512M, respectively.

In the initialization file, you can configure sub-caches within the buffer cache for each of this block sizes. Sub-caches can also be configured while an instance is running. To support this feature, the DB_nK_CACHE_SIZE is a dynamic initialization parameter.

In RAC system, it is possible to set up different non-standard block size buffers in different instances. But that would lead to problems during runtime.  Let us see how this is an issue.

Also note that RAC likes smaller blocksizes to reduce cache fusion pinging.

When you create a tablespace with BLOCKSIZE clause, Oracle checks for the cache availability of that particular block size at the instance where you are creating. Let’s take an example; assume that we create tablespace in node-2 where the non-standard DB_2K_CACHE_SIZE is set. Since 2K Sized cache is available, create statement goes through.

SQL> create tablespace TBS_2K blocksize 2K;

Tablespace created.

SQL> create tablespace TBS_2K blocksize 2K;

We will assume, after creating the above tablespace with 2K block size, we restart the instance with out 2K cache, the instance comes up without any issue, however, the first time Oracle attempts to access an object from the tablespace and tries to place blocks into the cache, we will receive the following error:

ORA-00379: no free buffers available in buffer pool DEFAULT for block size x

In another way, switch to Node-3 that does not have DB_2K_CACHE_SIZE set, when we try to access that tablespace, Oracle produces error.

SQL> create table mytable (col1 varchar(16))
TABLESPACE TBS_2K ;
create table mytable (col1 varchar(16)) TABLESPACE TBS_2K
*
ERROR at line 1:
ORA-00379: no free buffers available in buffer pool DEFAULT for block size 2K