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....