When Index Scans Attack!

We all know that table scans can be (but aren’t always) a negative thing. I have spent less time worrying about index scans, though. Index access = good, right? I thought I’d share a recent scenario where an index scan was very expensive. Maybe still better than a table scan, but with one index, I reduced the impact of a problem query by 80%.

I’ve recently gotten my hands on the DBI suite of tools http://www.dbisoftware.com/ and am starting to use them to analyze a new set of databases in support of a WebSphere Commerce site that will be going live in a few weeks. I’m using screen shots from those tools in this blog post. I’m not endorsing them explicitly, just showing how I used them.

First, I had a performance problem. SOLR reindexing was taking a long time. The issue was being addressed from multiple directions, but seeing as it spkied CPU on the database server to 80-90 % for 45 minutes, SQL analysis of the time period was in order.

Here’s what I saw looking at statements for the period in question:statement_performance_solr

Clearly, I have multiple dragons to slay here. But that first statement is using over 50% of the CPU used during this time period. The text of the statement turns out to be:

SELECT IDENTIFIER, CATGROUP_ID_CHILD, URLKEYWORD, SEQUENCE
    FROM CATGRPREL T1, SEOURL T2, SEOURLKEYWORD T3, CATGROUP T4
    WHERE CATGROUP_ID_CHILD = T4.CATGROUP_ID
        AND T2.SEOURL_ID = T3.SEOURL_ID
        AND TOKENVALUE = CATGROUP_ID_CHILD
        AND LANGUAGE_ID = ?
        AND T3.STATUS = :ln
        AND CATGROUP_ID_CHILD IN
            (SELECT CATGROUP_ID
                FROM CATGROUP
                WHERE MARKFORDELETE = :ln)
        AND CATGROUP_ID_PARENT = ?
        AND TOKENNAME = :ls
        AND CATALOG_ID = ?
    ORDER BY SEQUENCE WITH UR 

A side note here – Brother-Pather aggregates multiple statements together for me. Note the :ln and :ls in there – those replace literal values. Multiple statements might show up in my package cache because my application is specifying different literal values for those, or the same literal values may have been used over and over again, too. Parameter markers still show up as question marks, like anywhere else.

I ran an explain and get:
solr_query_explain_before

Note that I have collapsed some parts of the explain above to focus in on where the bulk of the cost is coming from. Note the place where the expense really comes in is through an index scan. DB2 will tell us “index scan” in an explain plan when it uses the root page to find the intermediate page, and the intermediate page to find the leaf pages, and then fetches from the table by RID. But it will also show “index scan” for a scan where it reads every single leaf page in an index to get the RIDs it needs. I think that’s what was occurring here – page scans of every single leaf page in that index. Even with read-ahead sequential prefetch, the query was still using an awful lot of CPU cycles.

A design advisor on the query, came up with 4 index recommendations (for over 80% improvement), one of which was:

CREATE UNIQUE INDEX "DB2INST1"."IDX1408012049550" ON "WSCOMUSR"."SEOURL" ("TOKENNAME" ASC, "TOKENVALUE" ASC, "SEOURL_ID" ASC) ALLOW REVERSE SCANS COLLECT DETAILED STATISTICS;

This index has exactly the same columns as an existing index, just in a different order. This might make me shy away from it. But I could tell based on the the explain that this was the index I needed for this particular query – it would be the one with the ability to give me the most impact.

I created the index, and afterward the explain looked like this:
solr_query_explain_after

What a difference! Just from changing the order of two columns in a three column index.

WebSphere Commerce does not allow me to drop or in any way alter any existing unique index. I have tried before, even just changing a unique index in the include columns and keeping the same index name, but the application fails if I do so. That means that I now have two indexes on this table that cover the same data. Luckily insert/update performance on this particular table is not critical, so I can accept this for now. It is critical to understand which tables have critical insert/update performance in an OLTP database and which tables do not.

You may also like...

6 Responses

  1. Suvradeep Sensarma says:

    I have been recently (for about 2 months) following your blogs. Of all the blogs i followed for past 2-3 years your blog contains the most relevant and precise information covering a range of DB2 LUW topics..Few issues you have discussed here have been encountered by me in the past also(e.g. index reorg Z lock,also this particular article on index scans attack).Thanks for all your time for sharing these stuff. It will certainly help to grow the DB2 community.
    One thing i wanted to know, is there any utility like DB2TOP for DB2 on Windows?Actually i am very fond of DB2TOP. Like sometimes i want to know which application is consuming the most CPU,Memory,Sorting etc.

    • Ember Crooks says:

      If I recall, there’s a free tool called db2mon. But I haven’t worked on windows in several years. There is nothing identical to db2top that I have ever heard of. There are always the paid tools, which are nice if you can get them – DBI Software’s Brother-Panther, Dell’s tool – I think it’s called foglight.

  2. Noel says:

    Most interesting as I have had performance issues retrieving data from the SEO tables recently. Your example got me thinking about this further. Why is the existing concatenated index not being used efficiently? You are specifying tokenname and tokenvalue so there is no reason for it not to use the index. From the explain plan you show, it appears to be doing an index scan of the primary key (seourl_id) which makes no sense at all. sure enough, I am seeing the same in my query. I thought I’d try casting the columns and found that when I make the condition in my query

    tokenvalue = cast(catgroup_id as char(12))

    it uses the existing concatenated index. Cost reduction on that line is from 2750 to 15 which is a big saving within the nested loops being performed.

    Can anyone explain this? To me it seems to be a fault within the optimiser. I can not see any benefit in this case of scanning the primary key before accessing the table data. I would be interested if you see the same results. DB2 v 9.7.8.

  3. Nadir Doctor says:

    If databases resident on db2@windows are cataloged on a linux/unix database server, then db2top can be used to monitor these remotely.

    • Ember Crooks says:

      Agreed, and I have used this, but in some cases with poor network speeds, the refresh rate of db2top can be really frustrating.

  1. August 5, 2014

    […] When Index Scans Attack! […]

Leave a Reply

Your email address will not be published. Required fields are marked *