Index Leaf Block contention Tuning
Oracle Database Tips by Donald BurlesonConsulting
March 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
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:
sid in (
select sid from v$session where state in ('WAITING')
wait_class != 'Idle'
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
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
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
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
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
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.
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
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.