Solution 1: Rebuild the index as reverse key indexes or hash partition the indexes which are listed in the ‘Segments by Row Lock Waits’ of the AWR reports. CREATE INDEX ON REVERSE; Reverse key indexes are designed to eliminate index hot spots on insert applications.

Reverse Key indexes to reduce index contention 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.

Waits for TX in mode 6 occur when a session is waiting for a row level lock that is already held by another session. This occurs when one application is updating or deleting a row that another session is also trying to update or delete.

Leaf blocks contain the indexed column value and ROWID of the table rows that they address.

Reverse key indexes literally reverse the bytes of the key value in the index to reduce block contention on sequence generated primary keys. Scalable Sequences were introduced in Oracle 18c as an alternative to using reverse key indexes.

This type of event is application level locking problem that occurs when a user session is trying to update or delete a row held by another session, which is an application design problem.

enq: TM – contention wait event indicates that there are unindexed foreign key constraints and this wait event is seen during DML on tables with unindexed foreign key. Oracle locks the table if a DML is made to the main table with the primary key.

Indexes are used in Oracle to provide quick access to rows in a table….Index the Correct Tables and Columns

1. Create an index if you frequently want to retrieve less than 15% of the rows in a large table.
2. Index columns used for joins to improve performance on joins of multiple tables.

Oracle Index-organized tables (IOTs) are a unique style of table structure that is stored in a B-tree index structure. Oracle Indexed-organized tables provide faster access to table rows by the primary key or any key that is a valid prefix of the primary key.

A reverse key index is created by including the REVERSE keyword in the index creation. CREATE INDEX t1_id_idx ON t1 (id) REVERSE; The contents of the table column is unchanged, only how the key is represented in the index block. As a result, there is nothing to see unless you dump the contents of the index blocks.

Large Waits on “Enq: TX – Index Contention” While a Massive Delete Takes Place or is Uncomitted (Doc ID 2281781.1) Last updated on DECEMBER 11, 2020

This usually happens when the application performs lot of INSERTs and DELETEs . The reason for this is the index block splits while inserting a new row into the index. The transactions will have to wait for TX lock in mode 4, until the session that is doing the block splits completes the operations

It’s possible that we see high Index leaf block contention on index associated with tables, which are having high concurrency from the application. This usually happens when the application performs lot of INSERTs and DELETEs . The reason for this is the index block splits while inserting a new row into the index.

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