Index Leaf Block contention Tuning Oracle Database Tips by Donald BurlesonMarch 22, 2015

Index block contention is very common in busy databases and it's especially common on tables that have monotonically increasing key values.

In a high-DML system, index management activities are constantly taking place (b-tree splitting and spawning) and these events can cause transient waits, but far and away, the greatest cause of index contention in an Oracle environment is the "high key" issue.  Oracle b-tree indexes are "right-handed" and the right-hand leafs of the b-tree contain the highest key in the lowest tree level.

Index leaf node contention happens when rows are inserted based on a user generated key (i.e. a sequence) and because the sequence key is always the high order key, each insert of a low-level index tree node must propagate upwards to the high-key indicators in the b-tree index. 

Detecting index leaf block contention

Index block contention can be tricky to diagnose and repair, especially since Oracle does not have many direct tuning knobs for tuning for contention.

The wait "enq: TX - index contention" indicates a wait on an index, and in a RAC environment you might also see "gc" (global cache) waits on indexes:

• gc buffer busy waits on Index Branch Blocks
• gc buffer busy waits on Index Leaf Blocks
• gc current block busy on Remote Undo Headers
• gc current split
• gcs ast xid
• gcs refuse xid

There are many ways to monitor for enqueue contention, including STATSPACK or AWR reports, v$session, dba_hist_enqueue_stat and the stats$enqueuestat table.  Here is a very simple script to detect index enqueue waits:

select
   sid,
   sql_text
from
   v$session s,
   v$sql q
where
   sid in (
      select sid from v$session where state in ('WAITING')
   and
      wait_class != 'Idle'
   and
      event='enq: TX - index contention'
   and (q.sql_id = s.sql_id or q.sql_id = s.prev_sql_id)
);

The details enqueue waits scripts are quite complex, but you can run scripts to detect enqueue waits.  Once you have seen index leaf block contention, there are several techniques to reduce this contention.  Let's look at various approaches to relieve index block contention.

Fixing Oracle index contention

There are three techniques that are used to relieve this index contention issue:

·         Reverse key indexes

·         Sequences with the cache and noorder options

·         Using hash partitioned global indexes

·         Adjusting the index block size

Let's look at each approach to reducing index leaf block contention.

Reverse Key indexes to reduce index contention

Using reverse-key indexes will speed-up Oracle insert statements, especially with an increasing key, like an index on an Oracle sequence (which is used for the primary key of the target table).  For large batch inserts, Oracle reverse key indexes will greatly speed-up data loads because the high-order index key has been reversed. 

In general, an Oracle reverse key index relieve data block contention (buffer busy waits) when inserting into any index where the index key is a monotonically increasing value which must be duplicated in the higher-level index nodes.  With the index key reversal, only the lowest-level index node is changed, and not all of the high-order index nodes, resulting in far faster insert speed. 

An Oracle reverse key index does not change the functionality of the index.  It's not the same as actually reversing the index key values and it only reverses the internal entries in the index to relieve index block contention.

In Oracle RAC, reverse-key indexes are especially important because they also reduce "index hot spots" because they reverse the bytes within the index leaf block, removing inter-node contention for a shared leaf block.

Sequences with the noorder and cache options to reduce contention

To reduce index contention for RAC environments you may also want to make sure that all sequences that appear in indexes is using the default "noorder" option and they you are using the cache option. 

If there is insufficient caching of sequences, contention can result which will show up as an increase in service times for DML. If there are performance problems due to sequence cache waits, examine the row cache locks statistics in the v$system_event view to determine whether the problem is due to the use of Oracle sequences.

When creating sequences for a RAC environment, DBAs should use the noorder keyword to avoid an additional cause of SQ enqueue contention that is forced ordering of queued sequence values.

In RAC, you can see sequence-related index enqueue delays in the eq_type column of the gv$enqueue_stat view.  A value of "SQ Enqueue" indicates that there is contention for sequences. In almost all cases, executing an alter sequence command can increase the cache size of sequences used by the application.

Hash partitioned global indexes to relieve index contention

Starting in Oracle 10g we got the hash partitioned global index, the idea that an index tree can be partitioned in the same fashion as a partitioned table. 

In a hash-partitioned global index, each partition contains the values determined by Oracle's hashing algorithm, a software function that take a index symbolic key and generates unique values with blistering fast speed.

When an index is monotonically growing because of a sequence or date key, global hash-partitioned indexes improve performance by spreading out the contention. Thus, hash-partitioned global indexes can improve the performance of indexes in which a small number of leaf blocks in the index have high contention in multi-user OLTP environments.

Adjusting the index block size to relieve index contention

The physical block size for an index matters because Oracle chose the block size boundaries to be the same as the b-tree node size.  Because the blocksize affects the number of keys within each index block, it follows that the blocksize will have an effect on the structure of the index tree. All else being equal, large 32k blocksizes will have more keys per block, resulting in a flatter index than the same index created in a 2k tablespace.

As a general rule, RAC likes smaller block sizes, and a database with 2k blocksizes will have less overhead at the cache fusion layer when blocks are transported to other RAC nodes.

For indexes, a smaller blocksize means that there will be fewer keys per block, and fewer keys per block can reduce index leaf block contention by reducing the likelihood that any two tasks will be waiting for the same block.

Note:  Adjusting the index block size may only produce a small effect (1%-20% improvement) and changing the index block size should never be your first choice for reducing index block contention, and it should only be done after the other approaches have been examined.

Conclusions on tuning for index leaf block contention

In a table that have a generated or a sequential key, insert DML can cause contention in the index leaf blocks, especially when the primary key is based on a monotonically increasing value such as a date or a sequence.  There are many ways to relieve index leaf block contention, but you should only undertake these tuning approaches when you see wait events that are directly related to index block contention.