Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

   
 

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


The above text is an excerpt from:

Oracle Grid and RAC
Oracle 10g Grid Computing with RAC
ISBN 0-9744355-4-6

by Mike Ault, Madhu Tumma

 


   
��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.