Oracle

December 9, 2010

BITMAP index and Locking issue

Filed under: Uncategorized — srivenu @ 11:00 am

While analyzing a performance issue, i observed several insert and some update sessions waiting on TX enqueue. Further digging showed that the table involved had a BITMAP index.

It was an OLTP system and the table in question was a sort of QUEUE table having large concurrency. The DBA considered a BITMAP index to be more suitable in this case as the column (some sort of STATUS) had low cardinality. Which turned out to be a really bad decision in this case!

There are several notes and articles on the web unwrapping the myths and facts of BITMAP indexes.

The following article by Jonathan Lewis – Understanding Bitmap indexes is a must read.

So are these from Richard Foote – Bitmap Index Degradation Since 10g (Fix You), Bitmap Index Degradation After DML Prior To 10g (Beauty and the Beast)

About these ads

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: