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.

36 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  
Blogger 123 123 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 オテモヤン said...

オナニー
逆援助
SEX
フェラチオ
ソープ
逆援助
出張ホスト
手コキ
おっぱい
フェラチオ
中出し
セックス
デリヘル
包茎
逆援
性欲

2/07/2010 10:54 AM  
Blogger aa said...

角色扮演|跳蛋|情趣跳蛋|煙火批發|煙火|情趣用品|SM|
按摩棒|電動按摩棒|飛機杯|自慰套|自慰套|情趣內衣|
live119|live119論壇|
潤滑液|內衣|性感內衣|自慰器|
充氣娃娃|AV|情趣|衣蝶|
G點|性感丁字褲|吊帶襪|丁字褲|無線跳蛋|性感睡衣|

2/23/2010 1:29 PM  
Blogger cc22 said...

角色扮演,
睡衣,
SM,
潤滑液,
情趣玩具,
愛愛,
視訊美女戀愛ing,
視訊美女kk俱樂部,
視訊kk俱樂部,

跳蛋,
G點,
按摩棒,
跳蛋,
飛機杯,
充氣娃娃,
自慰套,
情趣娃娃,
自慰器,
情趣用品,情趣,

4/19/2010 3:28 AM  
Blogger wiplast 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 iyinet webmaster forumu 2008 seo yarışması 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  
Anonymous Volker from Germany said...

Hallo, Ich haben eben Eure Internetseite besucht und nutzen sogleich die Gelegenheit,euch auch einen Gruß aus Deutschland in Eurem Gästebuch zu hinterlassen. P.S. Kommt uns doch auch mal besuchen
Urlaub an der Ostsee
oder Nordsee

6/06/2010 11:32 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 travesti 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  
Blogger Cris said...

This information is very interesting, I really enjoyed, I would like get more information about this, because is very beautiful, thanks for sharing! costa rica investment opportunities

7/30/2010 4:07 AM  
Blogger niz said...

Hello .. firstly I would like to send greetings to all readers. After this, I recognize the content so interesting about this article. For me personally I liked all the information. I would like to know of cases like this more often. In my personal experience I might mention a book called Generic Viagra in this book that I mentioned have very interesting topics, and also you have much to do with the main theme of this article.

8/28/2010 9:00 PM  
Anonymous Anonymous said...

Very good blog. Thanks for sharing this incident.

Thanks,
Jagan

10/18/2010 8:52 PM  
Blogger bedava chat said...

something you are really a great article söyleyimmi friends mynet I suggest you all had a nice example of this site shares Enter admin for the count I'm sure many would work too, but thank you very much chat ve sohbet If you help me I would be happy to get out searches such as the every time you come to places you want to wish you continued success bedava chat I would be glad my yazımıda me happy sharing site will also publish chat arkadaşlık siteleri sohbet I'm sure everything is reciprocal writing and more beautiful things I wish to share çet almanya chat comments by the writings of friends who share a beautiful almanya sohbet I hope to see you again that I take care of yourself respects everyones greetings from Turkey

11/21/2010 4:25 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 youlacka said...

That is really very good article.Thanks! kizlarla chatcetseviyeli sohbetchat odalarisohbet odalarisohbet odalariask sozlerisevgi sozleri

4/07/2011 9:46 AM  
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 altan alır 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 Metin 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  
Blogger bretthauer3073 said...

Read all the related Posts:


Basic of VBScript Language for QTP

Introduction to QTP (QuickTest Professional) Part2

Introduction to QTP (QuickTest Professional) Part3

Introduction to QTP (QuickTest Professional) Part4

Basic of VBScript Language for QTP


Read all the related Posts:

64 Software Manual Testing Interview Questions

Answers To Common Job Interview Questions

Behavioral Questions In Interviews

Questions to Ask at an Interview

Competency based Interview Questions

12/03/2012 1:06 PM  
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