Tuning the Database - Expert Oracle RAC Performance Diagnostics and Tuning - page 277

• Increasing the sequence cache size; the difference between sequence values generated by

different instances increases successive index block splits and tends to create instance affinity
to index leaf blocks.

• Increasing sequence cache also improves instance affinity to index keys deriving their values

from sequences. This technique may result in significant performance gains for multi-instance
INSERT intensive applications.
Implementing the database partitioning option to physically distribute the data.

•

• Using Locally Managed Tablespaces (LMT) over dictionary-managed tablespaces.

• Using Automatic Segment Space Management (ASSM). ASSM can provide instance affinity to

table blocks. Starting with Oracle Database 10g Release 2, ASSM is the default.

Another alternative to this solution is to use reverse key indexes, which will help distribute the data across several
leaf blocks. While reverse key indexes can create performance issues with range scans, another alternative to avoid
contention is to use locally partitioned indexes or hash partitioned indexes to avoid contention on leaf blocks.

Reverse Key Indexes
A reverse key index reverses the bytes of each column value indexed while keeping the column order. By reversing the
keys of the index, the insertions become distributed across all leaf keys in the index. A reverse key index can be used
in situations in which the user inserts ascending values and deletes lower values from the table, such as when using
sequence-generated (surrogate keys) primary keys.
When using a surrogate key, especially in an insert-intensive application, the lowest level index leaf block will
encounter extensive contention. For example, the key values 1324, 1325, 1326, etc., (illustrated in Figure 9-1 ) are
sequentially written in ascending order. This will require a change to the leaf block, and when rows are inserted
concurrently, it causes block splits to happen more frequently. The end result is a potential performance bottleneck.
This overhead is even more significant in the RAC environment where multiple users insert data from different Oracle
instances.
As the word “reverse” implies, the actual key value is reversed before being inserted into the index. For example,
if the value of 4567 were reversed, its new value would be 7654. Compare the leaf block entries in Figure 9-2 with
Figure 9-1 ; the leaf block entries are different between the two types of indexes.

Root Block

< 6000 < 9000

< 6000 < 7000 < 8000 < 9000

Branch Block

key:4238 key:5238 key:6238 key:7238

Leaf Block

Figure 9-2. Reverse key index structure