Friday, March 06, 2009

Low Cardinality != Bitmap Index

Sorry but this post is a bit of a rant. I was called into a performance issue yesterday. The users were complaining of slow performance. I issued extended SQL tracing on the session and found the SQL statement was a simple SINGLE ROW insert statement using bind variables. No triggers on the table.

What I found were hundreds of thousands of db file sequential read wait events to insert a single row. I checked out the data dictionary for any supporting indexes and found 10 indexes on the table, 4 of which were bitmap indexes. Fortunately, this was a 10g database, so the object number associated with the sequential reads were easily plucked using a simple AWK script.

wait #22: nam='db file sequential read' ela= 377 file#=17 block#=20988904 blocks=1 obj#=725386 tim=2691112678912

I found that nearly 99.99% of these wait events were owed to this object, a bitmap index.This application is not your standard OLTP as the underlying table gets loaded with thousands of rows each day with SINGLE ROW inserts. The dreaded concurrency and deadlocking did not come into play, well, because the load process is single threaded. However, all queries against this table need to perform very quickly. So, in that sense it has an OLTP face. Here is the rub. First, I asked if these indexes (in particular the bitmap indexes) could be dropped prior to their "load" and recreated after. The answer I received was essentially, "no, that is the way the application works." I then asked them to tell me why this index was a bitmap index. The developer stated the rationale was the fact that the data was uniformly distributed over 6 distinct values. I suppose that seems reasonable. I then asked the developer if this column was used in join conditions for other queries. The answer was a resounding NO.

Not to my surprise the index built as a standard b*tree index was just as efficient and lacked the horrific index maintenance overhead associated with SINGLE ROW inserts. The only reason the index was defined as a bitmap index was its cardinality and nothing more. I had them drop the index. The load that was taking 20+ hours to complete finished in under a minute. The lesson here is: Know your data, know your code and then evaluate the use of bitmap indexes to support your table access. The simple fact of low cardinality does not alone justify the use of a bitmap index. As a matter of fact, this bitmap index was so chubby that after it was re-created post load, it had been reduced in size by 99%. I suppose that is another point: Bitmap indexes aren't necessarily space savers either if used in an improper context.

BTW, the hundreds of thousands of blocks reads were not what you might have thought: locks against rows with the same bitmap as the inserted value for the bitmap column. Oracle was ranging over the index nonsensically looking for the proper place to dump the row. As the hundreds of thousands of sequential reads rolled by not a single TM lock was obtained and ZERO db block changes had accumulated. It was only when the row finally inserted that a few blocks changes showed up. This is just another example of a peculiarity with bitmap indexes that can crop up if used unlawfully.

27 Comments:

Blogger Hemant K Chitale said...

A development team that adds a BitMap Index only because the column has low cardinality without :

a. Verifying if the column is used in queries
b. Validating the impact on INSERTs and UPDATEs

is ..... you would begin to suspect .... likely to have done so on a few to dozens of other tables in a few to dozens of other databases.

You might be able to find and fix a few other such cases and get Good Marks from Management !

3/07/2009 10:45 AM  
Blogger Eric S. Emrick said...

Indeed :)

3/07/2009 1:50 PM  
Anonymous Anonymous said...

The real issue here is there should be no index on this column at all. The fact that the column is of low cardinality is irrelevant.

3/12/2009 6:50 AM  
Blogger Eric S. Emrick said...

@anonymous

The column values are in fact non-uniform and the queries usually go after the very selective value.

So, the index has a purpose, but a BITMAP index in this case had long term performance ramifications.

3/12/2009 10:17 AM  
Anonymous Anonymous said...

Interesting story as for me. It would be great to read something more concerning this topic.
By the way look at the design I've made myself A level escorts

11/19/2009 12:21 PM  
Anonymous Anonymous said...

porno izle
porno izle
porno izle
porno izle
porno izle

4/26/2010 4:07 PM  
Blogger R34Lw0rd said...

video izle
youtube
sikis izle
porno izle
dizi izle

5/09/2010 7:32 AM  
Blogger Unknown said...

Sikiş, sikiş izle, sikiş pornosu, sikiş seyret, sikiş videoları
En güzel videoları facebookta paylaş

5/30/2010 10:28 PM  
Blogger wiplast said...

thanks man sikimiyioyun oyna

6/03/2010 7:23 PM  
Blogger R34Lw0rd said...

porno videolarını izlenecegini güzel siteporno izle
porno videolarporno
sikiş videolarısikiş

6/14/2010 8:15 AM  
Anonymous viagra online said...

buy viagra
generic viagra

6/18/2010 1:09 PM  
Blogger Unknown said...

include on flat travesti shemale travesti turk site in travesti blons year travesti china trans travesti vwctor online travesti gay bu travesti escort she travesti chanleg travesti i can you travesti trv porno izle bedavais travesti yelchine travestiler super in travesti thank escort woork gulny

7/01/2010 5:00 AM  
Anonymous Send Flowers to UK said...

I suggest this site to my friends so it could be useful & informative for them also. Great effort.

7/26/2010 7:09 AM  
Anonymous Anonymous said...

Very good blog. Thanks for sharing this incident.

Thanks,
Jagan

10/18/2010 8:52 PM  
Anonymous gercek porno said...

Door To Door Transport industry with each passing day as new and innovative to meet these new companies to the sector has stepped

2/18/2011 7:35 AM  
Anonymous gül said...

http://www.travestiservisi.com

http://www.travesticeren.com

http://www.numberonearzu.com

http://www.travestiniz.org

http://www.trvgecilya.com

http://www.travestiw.com

2/24/2011 2:41 PM  
Blogger Yemeklerin Tarifleri said...

Fantastic website I will bookmark it and come back later. Thank you very much for posting this. konut

4/13/2011 6:06 AM  
Blogger Unknown said...

sınırsız porno izle | sikiş | porno izle | gizli çekim sikiş | porno izle | canli sikiş | adult film | sikiş izle | şifalı bitkiler | şikiş izle | kara şimşek izleShipping Containers are strong, secure and weatherprooof and are available in a variety of lengths. This makes our containers ideal for high security storage & shipping

4/24/2011 1:41 PM  
Anonymous viagra in canada said...

Thank you for sharing to us.there are many person searching about that now they will find enough resources by your post.I would like to join your blog anyway so please continue sharing with us

7/23/2011 8:10 PM  
Blogger Unknown said...

xxx
cute porn
sexy teen
cd sex
hot porn tube
sex video
fac sex
sex movies
xxx videos
teen sex
porn movies
porn videos
search porn tube

11/04/2011 10:25 AM  
Anonymous travesti said...

travesti - ankara travestileri-istanbul travestileri

3/28/2012 9:52 AM  
Anonymous Prank Calls said...

Nice blog and nice post, The topic here i found is really effective.

10/16/2012 7:34 AM  
Anonymous Show Plates said...

Nice blog, I really appreciate the way you are sharing your experiences.

10/18/2012 6:58 AM  
Anonymous Tina said...

Some posts really matters because they are valueable, I have found your post very valueable.

1/04/2013 7:29 AM  
Anonymous Superman said...

Glad to read your post :). It is very informative!

3/23/2013 9:50 AM  
Anonymous FOG said...

Very value able post, it keep me onto reading your whole story.

6/06/2013 7:45 PM  
Anonymous moona said...

Nice post realy good post for all, that you have updated us with all of nice information that can be very useful for future.

11/22/2014 4:40 AM  

Post a Comment

<< Home