AND-EQUAL vs BITMAP AND

My friend and colleague, Jan, sends me some great SQL performance issues from time to time. He and I had an email exchange around the Christmas holiday that was another interesting case so I thought I'd share it.

First a bit of background info. The problem query is running under both 9i and 10g database versions. On 9i, the query runs in about .5 secs and uses a AND-EQUAL operation. However, on version 10, the optimizer chooses a BITMAP AND operation and converts the b-tree indexes to bitmaps first. This is where the problem lies. The v10 optimizer choice to use the BITMAP AND operation increases the response time for the query to almost 1 minute (56.5 secs).

I recall that 10g deprecated the AND-EQUAL operation and will now choose this "index combine" operation using bitmap conversions instead most of the time. However, I also recall having read about performance issues occurring at times with this choice. I've seen a couple of tricks to work-around this problem. One is to set the optimizer_features_enable parameter to 9.2.0 in the session before executing the query (many times with a logon trigger). The other would be to split up the query into two queries using UNION ALL to put them together.

As you'll see below in the details, in v10 the majority of the time is spent in the plan step that converts the rowids of the b-tree index to bitmaps. But, the part that I'm not sure of (and haven't built a test case yet for review) is why the INDEX RANGE SCAN that provides the rowids to the bitmap conversion accesses so many more blocks than the range scan for the AND-EQUAL operation.

Now for the details:

select  count(item_t.poid_id0)
from    item_t                           -- num_rows 5,095,494
where 1=1
and   item_t.ar_billinfo_obj_ID0 = :1    -- num_distinct 599,778
and   item_t.ar_bill_obj_ID0     = :2    -- num_distinct 3,155,282

Bind variable values:  
:1 = 53
:2 = 10

The query under 9i uses AND-EQUAL as shown in the STAT lines from collected trace data:

STAT #2 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=79 pr=23 pw=0 time=514521 us)'
STAT #2 id=2 cnt=6 pid=1 pos=1 obj=352589 op='TABLE ACCESS BY INDEX ROWID ITEM_T (cr=79 pr=23 pw=0 time=162835 us)'
STAT #2 id=3 cnt=6 pid=2 pos=1 obj=0 op='AND-EQUAL (cr=76 pr=20 pw=0 time=142399 us)'
STAT #2 id=4 cnt=21 pid=3 pos=1 obj=368525 op='INDEX RANGE SCAN I_ITEM_AR_BNFO_OBJ__ID (cr=36 pr=1 pw=0 time=13924 us)'
STAT #2 id=5 cnt=16 pid=3 pos=2 obj=368532 op='INDEX RANGE SCAN I_ITEM_AR_BILL_OBJ__ID (cr=40 pr=19 pw=0 time=101036 us)'

Under 10g, using default settings the query uses a BITMAP AND plan:

STAT #2 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=9688 pr=9519 pw=0 time=56554617 us)'
STAT #2 id=2 cnt=6 pid=1 pos=1 obj=352589 op='TABLE ACCESS BY INDEX ROWID ITEM_T (cr=9688 pr=9519 pw=0 time=56554575 us)'
STAT #2 id=3 cnt=6 pid=2 pos=1 obj=0 op='BITMAP CONVERSION TO ROWIDS (cr=9685 pr=9519 pw=0 time=56554533 us)'
STAT #2 id=4 cnt=1 pid=3 pos=1 obj=0 op='BITMAP AND (cr=9685 pr=9519 pw=0 time=56554480 us)'
STAT #2 id=5 cnt=1 pid=4 pos=1 obj=0 op='BITMAP CONVERSION FROM ROWIDS (cr=3 pr=0 pw=0 time=128 us)'
STAT #2 id=6 cnt=22 pid=5 pos=1 obj=368525 op='INDEX RANGE SCAN I_ITEM_AR_BNFO_OBJ__ID (cr=3 pr=0 pw=0 time=90 us)'
STAT #2 id=7 cnt=6 pid=4 pos=2 obj=0 op='BITMAP CONVERSION FROM ROWIDS (cr=9682 pr=9519 pw=0 time=46814337 us)'
STAT #2 id=8 cnt=530398 pid=7 pos=1 obj=368532 op='INDEX RANGE SCAN I_ITEM_AR_BILL_OBJ__ID (cr=9682 pr=9519 pw=0 time=67896016 us)'

Notice how both of them use INDEX RANGE SCAN I_ITEM_AR_BILL_OBJ__ID, but the bitmap plan is doing 9682 cr against 40 for the and-equal. Why would that be the case?

My guess for the increased cr's comes from seeing cnt=530398 in step 8 of the bitmap plan where the index range scan occurs. That step is returning over half a million rows from the index that then are converted by the parent step 7. The parent ends up with cnt=6. What that says to me is that the conversion happened for the half million rowids and then were filtered to end up with only 6. But in the and-equal plan, the index range scan only returned cnt=16. I can see the behavior, my question is why? What's really going on? If any of you have the answer to that question, I'd appreciate hearing from you. I promise to test it for myself too! ;)

Performance optimization (or not) on a snowy day

A friend sent me this and it totally cracked me up. It's an excellent example of how working on the wrong thing is a bad idea. It doesn't matter how good you think the results of your efforts are, if you've been working on the wrong thing, you've just wasted a lot of time and effort. This also goes to show you that optimizing performance is not just a database challenge, but an every day challenge!

Enjoy!

Unsubscribe

Today I tried to unsubscribe myself from a mailing list that I'm not sure how I got on. I'm typically very careful about where I use my main work and personal email addresses so as to keep my Inbox from being flooded with junk email. But, I still occasionally find myself receiving emails that I just don't want to receive.

This was the case today when I attempted to unsubscribe from one such list. I clicked on the Unsubscribe link provided in the email and was taken to a page that showed my email address and a list of about 30 different mailing lists that I was (or could be) subscribed to. Out of the full list, two check boxes were marked. Since I didn't know about the other one that was marked, I thought this was good fortune that I could now unsubscribe to both on this one screen. So, I unchecked the boxes and selected the radio button at the bottom of the page that was labeled "Please remove me from all the above lists" and clicked Update.

I was a bit disturbed that I didn't get any confirmation message, but closed the browser and went about my business. Less than 5 minutes later, I got a rush of about 30 emails in a row. Yes, that's right...30. Guess where they were from? They were all email lists that were on the list where I'd just requested to be removed from all lists.

Well, that's precisely the opposite behavior that I wanted and expected. Instead of being removed from all lists, it appeared that I had been added. After a moment of thought, I decided that perhaps I had uncovered a bug in the unsubscribe program and created the problem myself by unchecking the boxes before I selected the "remove from all" option. I'm not happy at this point, but OK...I can fix this...I'll just unsubscribe again.

So, I click on the Unsubscribe link in the first email of the 30 I'd just received. This time I'm presented with an entirely different list of about 30 other mailing lists with the difference now being that the one list I want to unsubscribe from is one of the 30. Crap! I'm now thinking that means that I may actually have to go to all 30 emails and unsubscribe individually from each. Sigh...

This time, I leave the box beside the list I just received an email from and notice that at the bottom, there is another choice (besides the remove from all lists one). It says "Please remove me from all lists affiliated with the sender". I'm a bit afraid after my previous experience but I think "hey...maybe this will save me from having to go to all of the 29 other emails and unsubscribe there". So, I choose that option and click Update. Once again, I do *not* get a confirmation as to what changes, if any, were made from my update request. So, I decide to just keep the browser window open for a while and wait and see if I get another rush of emails.

After 10 minutes pass, I'm starting to feel pretty happy that I'd safely unsubscribed and closed the browser window I'd kept open just in case. I also deleted the 29 other emails I'd received. It's lunch time so I leave my computer for about 45 minutes.

Upon my return from my lunch, I notice the unread email count in my Inbox says 67. What the heck? As I looked over the list of emails, they were all emails requesting that I "click on this link" to confirm my request to unsubscribe to that particular list. I'm usually a very patient and easy-going person, but this is just too much for me. If I had access to whoever was sending me on this wild goose chase, I'd throttle them! I'm NOT happy. As a matter of fact, I'm royally hacked off! All I wanted to do was unsubscribe to one list and now I've been subscribed to a boatload more that all want me to confirm my desire to unsubscribe. Crap, crap, crap!

I swear to you that if I had clicked on the link to confirm my choice to unsubscribe and had been taken to that same screen again, I would've lost it. Fortunately for me (and my nearby possessions which would likely have suffered from my wrath), I was simply taken to a screen that showed my email address and a message saying that my email address would be removed from the list within 48 hours.

I had to do this 65 times (the other two emails in my Inbox were actually real business stuff!). I got through all this mess in less than an hour of my time, but that's nearly an hour out of my day that could've been much more productively spent! I wish I could bill whoever these folks were behind this fiasco for my lost time. Geesh!

The bottom-line here is that the whole process was absolutely not warranted. The list holder should've been able to unsubscribe me immediately from the original link in the original email. Unsubscribe means unsubscribe! Don't ask me about 30 other lists or if I want to subscribe to others, or be removed from all, or any of that stuff. Just take my address OFF the stupid list I've just clicked on the link about. What's so hard about that?!? And, why oh why, in this day and age, do I have to wait up to 48 hours before you will take me off your lists?! I get the whole distributed processing thing, but come on people! If they can send out new, "welcome to our list" emails almost immediately to new subscribers, these folks can certainly take my email address out of their system equally as fast. Besides, who's going to be more upset? Me, who wants off the stupid list but keeps getting emails after I've requested to be unsubscribed, or a new subscriber who has never received anything from you yet at all and has to wait a day or two?

I don't know about you all, but I'm sick and tired of being subscribed to lists I've never heard of. There are a few sites from which I have requested to be notified, but there are multitudes more that I care absolutely nothing about. All I feel I can do is scream out to the universe to STOP THE MADNESS! If you hear a sonic boom coming from the Pacific Northwest in the near future, it may just be me...imploding after having received another unwanted email.

In the meantime, I'm just doing some deep breathing exercises and trying to stop my mental thrashing of this particular list vendor. Surely it can't be good for my blood pressure. Sigh....

The Charles Schultz Philosophy

The following is a philosophy of life attributed to Charles Schulz, the creator of the 'Peanuts' comic strip. You don't have to actually answer the questions to get the point. Just ponder on them.

1. Name the five wealthiest people in the world.
2. Name the last five Heisman trophy winners.
3. Name the last five winners of the Miss America pageant.
4. Name ten people who have won the Nobel or Pulitzer Prize.
5. Name the last half dozen Academy Award winners for best actor and actress.
6. Name the last decade's worth of World Series winners.



How did you do? The point is, few of us remember the headliners of yesterday. These are no second-rate achievers. They are the best in their fields. But the applause dies. Awards tarnish. Achievements are forgotten. Accolades and certificates are buried with their owners.



Here's another set of questions. See how you do with these:

1. List a few teachers who aided your journey through school.
2. Name three friends who have helped you through a difficult time.
3. Name five people who have taught you something worthwhile.
4. Think of a few people who have made you feel appreciated and special.
5. Think of five people you enjoy spending time with.



Easier?

The lesson:
The people who make a difference in your life are not the ones with the most credentials...the most money...or the most awards. They simply are the ones who care the most.



For me, I thought this was something good to ponder on the weekend before Thanksgiving. I realized how easy it was for me to answer the second set of questions and how many people have touched my life. There have been family, friends, teachers, mentors, and colleagues who have given me something of themselves. I feel very, very thankful and my life is richer.

So, what are you thankful for?

One last hurrah on database independence

I had mentioned in my previous post on database independence about Toon Koppelaars presentation on a database centric approach to application development. I contacted him and he said he was going to post his presentation on his site soon. But in the meantime, I thought I'd show one of his slides which I think speaks volumes to the performance aspects of trying to keep with a database independent approach.



The slide shows:
a) If you put business logic in the mid tier, you’ll call the dbms an order of magnitude more.
b) If you also put data logic in the mid tier, you’ll call the dbms two orders of magnitude more.
c) Finally, if you also don’t use SQL effectively, you’ll call the dbms three orders of magnitudes more.

So, a single click on a "Save" button by the user can ultimately mean 100 calls the dbms has to execute to handle all the middle tier madness!

You should be able to download Toon's full presentation from RuleGen.com very soon. Give it a look...it's good stuff.

Database Independence?!

OK. Call me dense. Call me biased. Call me performance conscious. But I just can't groove on the concept of database independence. You know what I mean? The whole idea of having all your application logic built into your application and the database does basically nothing...well, it just doesn't make sense. Even after hearing all the arguments, I just don't get it.

There are so many optimizations that can't be utilized when the database isn't involved. The database ends up serving back boatloads of "simple" query results (usually max of 2-3 tables per query and mostly single table queries) and the application does everything else. The application basically processes the joins, applies the constraints...everything. What would amount to simple work that could occur in minimal time for Oracle, amounts to seconds of time processing the data at the application layer. Heck...if you're gonna do that, why not just have flat files to store your data and skip the database all together!? It'd sure save you a bundle on Oracle license fees!

Toon Koppelaars recently delivered a presentation at Miracle Oracle Open World that I attended that was about a database centric approach. I hope he'll post it somewhere soon for "public consumption" as it makes an extremely strong argument for going towards a "fat" database and getting the bulk of logic and function out of the application layer. Amen brother!

I'm ranting, so I'll stop. But, I'd love to hear from you about your thoughts. I'm an open-minded kinda gal, but when it comes down to it, I find all the reasons people share with me about why they absolutely have to write database independent code and put all the code in their application layer to be just a bunch of excuses. They are excuses that cover up 1) lack of sufficient knowledge of the databases they want to use and 2) a preference for what they think is easy. I mean, when you have a stable full of developers who can code Java fast and furious, but have been taught to not care about the database, designing for a database centric approach just isn't the forte...so it doesn't happen. But, when performance problems start to occur, they want to blame the database. Again...I just don't get it.

Can anyone help me out here?

Recent Presentations

I've posted my most recent presentations from Miracle Oracle Open World in Denmark and the OPP2008 (Oracle PL/SQL Programming Conference) in Chicago to SlideShare. By the way, SlideShare is pretty cool if you've never seen or used it. It's a great way to easily share presentations. You can also add audio to create SlideCasts. I'm thinking of adding an audio track over the presentations I've already uploaded, but that'll have to wait until I have more time to do so. So much to do...so little time!

I've also added a link to my blog sidebar that will access all my presentations I upload to SlideShare. So, after this post ages off my main blog page, it'll be easy to find them from the sidebar link. I plan to post all my presentations there after I've delivered them.

To access the presentation you want, just choose the name of the presentation on the right side and it will display in the view area on the left. Happy viewing!

| Get your Presentation Pack