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
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
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
* 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
ALLOCATE EXTENT ( SIZE 200K
* 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
* 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
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
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.