Tell RAC to Leave Your Leaves Alone

Last year I published the slides from my presentation Managing Sequences in a RAC Environment which discussed the administrative and performance management skills related to the use of Sequence Number generation in RAC using both Oracle and user defined sequences.  One use of sequences about which most DBAs and developers are aware is that of generating primary keys for a table and naturally the key data for the underlying index.

But the choice of sequence parameters can have an effect on performance when using high volume insert applications in a RAC database environment. The problem is one of Index Leaf Block contention whose symptoms are indicated by the following wait events:

1. enq: TX – index contention
2. gc buffer busy waits on Index Branch Blocks
3. gc buffer busy waits on Index Leaf       Blocks
4. gc current block busy on Remote Undo Headers
5. gc current split

There may also be increasing numbers in the following System Statistics:

1. Leaf node splits
2. Branch node splits
3. Exchange deadlocks
4. gcs refuse xid
5. gcs ast xid
6. Service ITL waits

Finally there may be an increase in the following statistics in the V$SEGMENT_STATISTICS view for indexes on tables with high volume inserts:

1. gc buffer busy
2. gc current blocks received

I have had many delegates in the classroom on my RAC courses who have reported such symptoms which suggests that they may have this problem. We can understand the causes by looking at the implications of the [ORDER | NOORDER] options on index performance. Note that all the other options are discussed in detail in the presentation mentioned above.

If a sequence is created with the ORDER option then the order of numbers allocated is guaranteed even though multiple instances may run the application requesting the next sequence number. As pointed out in my presentation, RAC databases manage this by caching the same range of numbers in the row cache of all instances having used the sequence, and uses the SV instance lock to indicate which instance’s row cache  contains the most recent next value for the sequence. If a request for the next number comes from the instance owning the SV instance lock, then the sequence in incremented and the SV lock continues to be held by the same instance’s LCKO process. If another instance requests the next value then the LCKO process on the requesting instance acquires the SV lock from the owning instance  which also passes the nextval value in the same message to the requesting instance which becomes the new owner of the SV lock for that sequence, updates its row cache and assigns the number to the requesting server process in the requesting instance.

There is of course some overhead in acquiring the SV instance lock but the bigger problem is the overheads for an index that uses the sequence number as a key. If high volume inserts occur then the right side leaf block will get the usual buffer busy waits occurring, due to multiple concurrent inserts from a sequence based key but there may be requests from other instances for inserts into the same index. This will require that the index leaf is served over the interconnect, once it is not busy, hence the additional Cache Fusion overheads mentioned earlier.

Now consider what occurs if the sequence uses the NOORDER option. In this case each instance using the sequence caches a separate range of numbers. For example if the CACHE option is set to 50,000, and there are four instances which used the sequence initially in the order 1,3,4,2, then instance one will cache the first 50,000 numbers, instance three the next 50,000, instance four the next 50,000 and the next 50,000 by instance two. No SV lock will be used to coordinate the row caches to determine which row cache has the correct “next value” since each instance caches separate ranges of numbers.

If the table and index are new then initially all inserts into the index from all instances will use the same leaf block as there will be only one. But after a number of block splits occur to the index, discrete ranges of leaf blocks will develop holding the keys in the four ranges of numbers. For example, assuming a roughly equal number of inserts occur from all instances, then the first N leaves will contain keys for the first range of cached numbers from instance one, the second group of N leaves will contain keys for the second range of cached numbers from instance three and so on.  Effectively we now have four separate key and leaf ranges and each range has their own “right handed” leaf for that range.

Since each  “right handed” leaf will  be used only by the instance that has cached the range of values corresponding to the range of leaves, Cache Fusion overheads for the high volume insert will be nearly eliminated since effectively, each instance has their own “right hand” leaf block in which to insert keys.

The benefit of using CACHE with NOORDER on indexes is a reduction in overheads for high volume inserts but at a cost of having the keys generated out of order. So the first 10 values generated assuming the sequence starts with 1 and increments with 1 could be as follows:

1. 50,001
2. 1
3. 2
4. 50,002
5. 150,001
6. 3
7. 4
8. 50,003
9. 100,001
10. 100,002

If the order of key generation is important then this technique is not available but if the requirement is for unique key values and the order is unimportant then using CACHE and NOORDER is the best way to reduce the overheads of the both the sequence generation and the overheads of high volume insert activity.

In addition one may reduce  hot leaf block contention problems by using REVERSE KEY indexes if no range scans are required, by using HASH PARTITIONED GLOBAL indexes if already using the partitioning option or by a combination of both. These techniques can also help reduce buffer busy wait and cache fusion overheads but in many cases REVERSE KEY indexes can not be used since they prevent the optimiser from generating plans using an INDEX RANGE SCAN access method. HASH PARTITIONED GLOBAL indexes require the partitioning license and prevents this from being used by all Oracle DBAs.

As with tuning tip, the degree to which this will improve performance depends on many variables so try it and benchmark the difference it might make in your RAC environment.