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 RAC Cluster Tips by Burleson Consulting

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:

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

Therefore, it is recommended that all instances start with the same set of non-standard block size buffers (sub-cache) with the db_nk_cache_size parameter. Note that Oracle is not enforcing the existence of the same set of sub-caches when the instance joins the cluster. It is better to create the sub-cache with all possible caches (2K, 4K, 8K, 16K, 32K etc) to avoid run time errors.

Redo Log Buffers

Redo log buffers are used to hold the redo records generated by each data changing transaction. It is a circular buffer. Redo entries contain the information necessary to reconstruct, or redo, changes made to the database by INSERT, UPDATE, DELETE, CREATE, ALTER, and DROP operations.

A redo log buffer is written out to the online redo log by the log writer process when:

* The buffer becomes one third full.

* Three seconds have elapsed.

* When a DBWn process writes modified buffers to disk.

* On commit record - when a user process commits a transaction.

While it is acceptable to have different sized redo log buffers and redo logs on each instance in a RAC database, this is not a suggested configuration. It can lead to confusion and misunderstandings during recovery operations. Each instance in a RAC database must have its own thread of redo logs.

 


This is an excerpt from the bestselling book Oracle Grid & Real Application Clusters, Rampant TechPress, by Mike Ault and Madhu Tumma.

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

http://www.rampant-books.com/book_2004_1_10g_grid.htm


 

 
��  
 
 
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 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational