Index Leaf Block contention Tuning

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

HW enqueue contention

HW enqueue

When a session needs access to a resource, it requests a lock on that resource in a specific mode. Internally, lock and resource structures are used to control access to a resource. Enqueues, as name suggests, have First In First Out queueing mechanism.

Segments have High Water Mark (HWM) indicating that blocks below that HWM have been formatted. New tables or truncated tables [ that is truncated without reuse storage clause ], have HWM value set to segment header block. Meaning, there are zero blocks below HWM. As new rows inserted or existing rows updated (increasing row length), more blocks are added to the free lists and HWM bumped up to reflect these new blocks. HW enqueues are acquired in Exclusive mode before updating HWM and essentially HW enqueues operate as a serializing mechanism for HWM updates.

Measuring HW enqueue contention

We will use few test cases to see how underlying extent size and table structures are affecting HW enqueue contention. But, before we need to find a way to measure total number of gets on HW enqueue. If total number of gets on HW enqueue is reduced, enqueue contention can be relieved.

Fixed table x$ksqst stores statistics about total number of enqueue gets, success and failures of those gets at instance level. For example, to see total number of gets on HW enqueue, following query can be used. Column ksqstreq indicates total # of gets and ksqstwat shows total # of waits.

SQL> select ksqstreq, ksqstwat from x$ksqst where ksqsttyp='HW';

From Oracle version 10g and above, x$ksqst is externalized as v$enqueue_statistics.