Efficient RAC tables

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
ALLOCATE EXTENT ( SIZE 200K
DATAFILE ?/usr/u01/oradata/addb/ad_file3.dbf?
INSTANCE 2);

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