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

 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
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

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

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 









Efficient RAC tables

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%.

One obvious method of reducing pings between instances is to isolate the transactions that use a specific data set to a specific server in the RAC cluster. For example, in a multiuse instance that contains multiple applications, isolate each application?s user logins to a specific node.  For example, sales users use the sales node, accounting uses the accounting node, and so on. In the case of a node failure, the users switch to one of the other nodes.

This compartmenting of transactions is difficult to implement for a large, multiuse RAC database where many different groups of users use each of the applications on the RAC cluster. In the case of a multi-use instance that is used by almost all corporate users, other techniques must be employed to optimize performance.

Creating Efficient RAC Data Objects

The creation of efficient RAC data objects entails using storage wisely in accordance with good RAC practices. Some of the good RAC practices, to put it quite frankly, waste disk and memory space to improve data sharing and dispersal characteristics.

An example of an efficient RAC object is one that is used by only a single instance at a time. To achieve this singularity of use, the rows-per-block (RPB) of the data object must be reduced. By reducing the RPB, the chances that multiple instances in the RAC cluster will require data in the same block are decreased. The following are several techniques that can be used to reduce the RPB in a RAC data object:

* Use a smaller block size for objects that will be used across several instances.

* Adjust pctfree to restrict the RPB since a higher pctfree will reduce the RPB.

* Use data fillers, for example, CHAR data types can be used to artificially extend the size of a data row, thus reducing RPB.

Other techniques to improve the efficiency of data objects include:

* Use as few indexes as possible to optimize data retrieval from the RAC data objects. Index node contention is the largest source of intra-node block pings, or as Oracle calls them, intra-instance block transfers. Index maintenance causes a great deal of intra-node pinging. 

* Use automated freelist management.

* For high insert objects, pre-allocate extents to avoid dynamic space management. Assign allocated extents to specific instances. This avoids intra-instance block transfers during insert activity from multiple nodes. For example:

ALTER TABLE ad_proofs
DATAFILE ?/usr/u01/oradata/addb/ad_file3.dbf?

* Use locally managed tablespaces to avoid uet$ and fet$ block pinging between instances.

* Use reverse-key indexes for indexes that may become right-hand indexes due to high insert rates. This removes the capability to use index scans. Use only when required.

* Design indexes such that the clustering factor is as close to the number of used blocks as is possible. Testing various column orders in concatenated indexes does this. In single column indexes required for SQL optimization, consider re-ordering the table in index order to reduce clustering factor.  This technique can result in hot blocks and is the reverse of the previous suggestion to use reverse-key indexes, which actually increases the clustering factor.

This may seem perplexing, since some of the suggestions are contradictory. The correct approach depends on the specific tuning situation. In a situation where hot blocking is occurring in that multiple instances want the same index block because all of the current data entries are indexed there, randomizing the index nodes will reduce the possibility of intra-node index block pinging.

This is demonstrated in Figure 12.3 below. In the case where the data referenced in a single index block is needed by a single instance, the number of data blocks required is reduced by concentrating the data into as small a number of data and index nodes as possible. This reduces the intra-node pinging of data blocks. This is demonstrated in Figure 12.4. So in the first case shown in Figure 12.3, intra-node index block pinging is reduced, and in the second shown in Figure 12.4, the intra-node pinging of data blocks is reduced. The appropriate technique will have to be determined for each tuning situation.

Figure 12.3: Example of a Reverse Key Index

Figure 12.4: Clustering Factor Affects

Figure 12.3 illustrates that a hot index block, such as an index containing sequential data, by date or number, that is required by multiple nodes, increases the intra-node index block pinging, as the nodes transfer the index block to perform data lookups.

This demonstrates the effects of a poor clustering factor when the data in a table is poorly ordered, thus spreading data blocks containing index-sequential data across multiple instances. This random scattering of data into data blocks results in the possibility of multiple data blocks being required by a single instance to satisfy a range type query against a single index node. The result is the false pinging of data blocks between instances.

Neither situation is desirable. Use the reverse-key index method when multiple instances will require access to current data referenced in ascending numeric key or date key indexes. Use method two when data is usually accessed by an index range scan for specific periods from the individual instances. Method two is also good for concatenated indexes.

What about character-based indexes? By design, character-based indexes will always be random unless the table is sorted in character index order. This is because a character-based index organizes the data reference pointers in alphabetical order, while the data will usually be in natural insert order, based on the mean frequency charts for the specific language the database uses. Character-based indexes will always be skewed towards those letters that appear most frequently in the leading portions of the words for a given language.  In English the letters N, R, S, L, and T lead the frequency charts.


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.


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