Wednesday, February 6, 2013

Row lock contention during inserts on table with Bitmap Indexes

Recently i investigated an issue which several concurrent sessions running inserts statements were waiting on enq: TX - row lock contention. 

I run an ASH report form Oracle EM for the duration the problem observed and i found the objects where were the root cause for these waits were 2 indexes. 

I checked the table structure and the mentioned indexes and found out that they were BITMAP indexes. As mentioned in Oracle® Database Data Warehousing Guide "Bitmap indexes are primarily intended for data warehousing applications where users query the data rather than update it. They are not suitable for OLTP applications with large numbers of concurrent transactions modifying the data"  

I have seen this behavior and in other OLTP applications and its an index design problem on the table. After explain this to the DEV team i drop and recreated the indexes as normal B-Tree indexes and problem resolved.

Please check also the following Blog entries with details on that problem. 

No comments:

Post a Comment