enq: TX row lock contention During Inserts due to Bitmap Indexes

Bitmap indexes are not suited on tables that have frequent DML operations. It was shown on an earlier post how the bitmap index locking happens.
enq: TX row lock contention waits happen when multiple sessions try to modify the same row, but could manifest in other areas as well such as inserts on a primary key column and inserts on bitmap index columns. Since insert is effectively creating a new row seeing enq: TX row lock contention on inserts could be bit puzzling.
In the case of inserting into a primary key column subsequent sessions will wait on enq: TX row lock contention event until first session commit or rollback. This is required to identify and raise ORA-00001: unique constraint in cases where duplicate values are being inserted into primary key column. Therefore it is not uncommon to see enq: TX row lock contention waits on inserts when there's a primary key column and a highly concurrent insert rate. To reduce this wait if possible remove the primary key but may not always possible due to application logic.
Second place where enq: TX row lock contention events comes up is when inserting to table with bitmap indexes. enq: TX row lock contention waits happen since bitmap index entries must be updated based on the inserted rows. This has a far worse performance impact than insert on a primary key. Below is a simple test to illustrate the point.
Create a table with primary key

create table abc (id number, a varchar2(1), b number(1), c varchar2(1), primary key (id));

Create bitmap indexes as below

SQL> create bitmap index aidxa on abc(a);
Index created.

SQL> create bitmap index aidxb on abc(b);
Index created.

SQL> create bitmap index aidxc on abc(c);
Index created.

Open two sqlplus sessions and run the following two PL/SQL blocks. On session 1

begin
    for i in 1 .. 100000
    loop
      insert into abc values(i,dbms_random.string('i',1),round(dbms_random.value(1,5)),dbms_random.string('i',1));
      commit;
    end loop;
end;
/

On session 2

begin
    for i in 100000 .. 200000
    loop
      insert into abc values(i,dbms_random.string('i',1),round(dbms_random.value(1,5)),dbms_random.string('i',1));
      commit;
    end loop;
end;
/

Monitor the waits either through em console or active session history view. In this case APConsole has been used and output is given at the end.


Drop the bitmap indexes and truncate the table and run the test again. The image below shows the comparison of the two test cases.

It is apparent that amount of cpu time is high during the test with bitmap index in-place but the point is the enq: TX row lock contention which is none when test was run without bitmap index. Therefore when there are high enq: TX row lock contention waits seen for insert statements it is worth investigating whether the table in question has bitmap indexes.

Related post
Bitmap Index Locking

Metalink Notes
TX Transaction and Enq: Tx - Row Lock Contention - Example wait scenarios [ID 62354.1]