When can a Temp Table index have ROW LOCK WAITS?

Toad for Oracle & DevOps Toolkit
1

I have a Oracle AWR report that shows that Temp Table Indexes are having ROW LOCK WAITS.

The Temp table is defined as ON COMMIT PRESERVE ROWS

Question: What scenarios can a Temp Table Index have ROW LOCK Waits ???

Segments by Row Lock Waits

  • % of Capture shows % of row lock waits for each top segment compared
  • with total row lock waits for all segments captured by the Snapshot
    Owner
    Tablespace Name
    Object Name
    Subobject Name
    Obj. Type
    Row Lock Waits
    % of Capture
    ONAIR
    ONA_TS1
    SU_WEEK_INV_BATCH
    TABLE
    715
    26.70
    ONAIR
    TEMP
    TMP_EVENT_INV_FDO_IDX
    INDEX
    227
    8.48
    ONAIR
    TEMP
    TMP_EVENT_INV_SU_DETAILS_IDX
    INDEX
    216
    8.07
    ONAIR
    TEMP
    TMP_EVENT_INVENTORY_SU_IDX
    INDEX
    136
    5.08
    ONAIR
    TEMP
    TMP_SU_EVENT_DATE_IDX
    INDEX
    114
    4.26
2

In terms of Index contention no matter what type of table we have reasons for such index contention would be same when compared to non-temp tables.

Index contention can be due to multiple events, some of the popular events are

  1. enq: TX - index contention

  2. enq: TX - row lock contention

These wait events can be due to multiple reasons like Index root block split, Concurrent DML’s on bitmap index, Two sessions trying to insert same primary key value, Inserting into child table when related parent key has been deleted but not commited in case of foreign key constraint enforcement…many more.