When Index Scans Attack!

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 *