This is an excerpt from the bestselling book
Oracle Grid & Real Application Clusters. To get immediate
access to the code depot of working RAC scripts, buy it
directly from the publisher and save more than 30%.
In RAC systems, 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 the buffer cache is an SGA feature with the setting of the
db_nk_cache_size parameter. Up to four block sizes can be specified
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
The following is an 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,
sub-caches can be configured within the buffer cache for each of
these block sizes. Sub-caches can also be configured while an
instance is running. To support this feature, db_nk_cache_size is a
dynamic initialization parameter.
In a 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. This
issue is examined more in the next section.
When a tablespace is created
with a blocksize clause, Oracle checks for the cache availability of
that particular block size at the instance where it is being
created. For example, assume that a tablespace is created in node-2
where the non-standard db_2k_cache_size is set. Since a 2k sized
cache is available, the create statement goes through.
SQL> create
tablespace TBS_2K blocksize 2K;
Tablespace created.
Next, assume that after creating
the above tablespace with 2k block size, the instance is restarted
without the 2k cache. The instance comes up without any issue,
however the first time Oracle attempts access an object from the
tablespace and tries to place blocks into the cache, the following
error will be received:
ORA-00379: no free buffers
available in buffer pool DEFAULT for block size x
As another example, switch to
Node-3 which does not have db_2k_cache_size set. When an attempt is
made to access that tablespace, Oracle produces an error.
SQL> create
table mytable (col1 varchar(16))
TABLESPACE TBS_2K ;
create table mytable (col1
varchar(16)) TABLESPACE TBS_2K
*
ERROR at line 1:
