I actually had a blog entry started on this topic before IDUG. I knew this was possible, but not exactly how to do it. Then I sat in Scott Hayes' session – D04 – More Sage Advice: Invaluable DB2 LUW Performance Insights from Around the Globe, and he had all the details I needed to really get rolling on trying this out – saving me valuable time in research.
Indexing is relatively easy with a simple data model, or even a complicated one that doesn't join a lot of tables, once you get used to it. But get to a point where you've got views and tables and dozens of objects involved, where the time is not primarily spent on reading data from the tables and indexes, but in the joining and filtering, and it starts to get difficult.
I'm calling this multiple index regression analysis because we're looking for which recommended indexes most impact our query performance. The DB2 design advisor can recommend many indexes to improve performance, and some of them are more impactful than others. In some analysis I was doing last week, one of the indexes turned out to only reduce query execution time by 0.0001 timerons – not worth the overhead and space for that particular index. This does not use actual statistical formulas like those used in Multiple Linear Regression, but I could see a whole-workload approach where that would be interesting.
This is a highly advanced DB2 topic. It is assumed that the reader understands terms like Timeron and gets the concepts and can read an explain and db2advis output.
A bit more horrendous, that, and looking at it in detail, there's no obvious gigantic table scan or even fetch from a table after reading an index that provides a significant amount of the time.
CREATE UNIQUE INDEX "ECROOKS "."IDX1505210120080"
ON "DB2     "."ORDER" ("TRAILER_SIZE" ASC, "DL_AP_DATE2"
ASC, "CUSTOMER_RATE" ASC, "NOTIFIED_DATE" ASC, "DATE_ENTERED"
ASC, "COMMODITY" ASC, "NEEDS_PAPERWORK" ASC, "LINE_OF_BUSINESS"
"DL_AT_TIME2" ASC, "DL_AT_DATE2" ASC, "DL_AT_TIME1"
ASC, "DL_AT_DATE1" ASC, "DL_AP_TIME2" ASC, "DL_AP_TIME1"
ASC, "DL_AP_DATE1" ASC, "PU_AT_TIME2" ASC, "PU_AT_DATE2"
ASC, "PU_AT_TIME1" ASC, "PU_AT_DATE1" ASC, "PU_AP_TIME2"
ASC, "PU_AP_DATE2" ASC, "PU_AP_TIME1" ASC, "PU_AP_DATE1"
ASC, "S_LOCATION_ID" ASC, "CHASSIS" ASC, "CHECK_DIGIT"
CREATE INDEX "ECROOKS "."IDX1505210116380" ON "DB2     "."EDI_204"
("CUST_S_LOCATION_ID" ASC, "ORDER_ID" ASC, "REFERENCE"
CREATE UNIQUE INDEX "ECROOKS "."IDX1505210115300"
CREATE UNIQUE INDEX "ECROOKS "."IDX1505210115350"
CREATE INDEX "ECROOKS "."IDX1505210115360" ON "EDI     "."EDI_204_ADDITIONAL_FIELDS"
CREATE INDEX "ECROOKS "."IDX1505210116350" ON "DB2     "."CODE"
("CODE_TEXT" ASC, "TYPE" ASC, "CODE_ID" ASC, "CODE"
CREATE UNIQUE INDEX "ECROOKS "."IDX1505210121040"
Some of those indexes I'm not likely to try. index for example is awfully wide. index is nearly identical to an existing index, with the difference being in the order of the last two columns. And 19 total indexes that DB2 wants to use for this one query? Wow, that's a lot.
Also, I don't have a full-size test or QA environment – the data size in my lower environments is significantly smaller, so it is hard for me to scale down to see what happens with different indexes in those environments. One option here is to mimic statistics in my lower environments. I've done that before, but I want to try mimicking the new indexes instead.
First, my explain tables are all currently shared tables. With multiple DBAs potentially working in this environment, I have two real choices to isolate my data – #1 is to create my own set of explain tables and make use of them. #2 is to note the time before I collect data and then the time after I collect explain and db2advis data and look only within that period to verify that only the data I want is included. I'm chosing the second option. With only 3 potential people running explains and none of us dedicated to that full time, this seems like a good option – we're not likely to conflict.
I need to capture the explain and db2advis data before I work on anything. Note that I happen to be working in a Windows environment from a powershell prompt for this work – everything here would be the same on Linux/UNIX.
PS D:\xtivia\queries> db2 "select current timestamp from sysibm.sysdummy1"
CREATE INDEX "DB2ADMIN"."IDX1505231648190" ON "DB2     "."EDI_204"
CREATE INDEX "DB2ADMIN"."IDX1505231647170" ON "EDI     "."EDI_204_ADDITIONAL_FIELDS"
CREATE INDEX "DB2ADMIN"."IDX1505231648160" ON "DB2     "."CODE"
What this gets me is all of the details about this query without any changes, and the recommended indexes – in this case 7 new indexes. I don't really like all of those indexes, but I need to figure out which ones will give me the most impact and analyze other characteristics about them. Note the use of the -p option on the db2advis. This causes DB2 to retain information about the explain plan and recommended indexes.
The next step is to gather the costs of this query before and after the indexes recommended by the db2 design advisor. Note that we have to fill in the timestamps from above to limit the data returned.
What we have in the explain tables at this point is really two sets of data – the data from the original explain, and the data from an explain as if all of the recommended indexes existed.
I hide and add some columns, but I'm running this same query between every change I make so I keep track of what I've done and not done.
At this point, there are two different methods that Scott laid out in his presentation on this topic – Index Addition and Index Subtraction.
PS > db2 "update advise_index set use_index = 'N' where exists= 'N'"
Now one by one we set use_index equal to 'Y' for each index and run an explain using the 'evaluate indexes' option.
PS D:\xtivia\queries> db2 "update advise_index set use_index = 'Y' where name = 'IDX1505231647110'"
Note that the explain mode used is not yes or explain, but evaluate indexes.
PS D:\xtivia\queries> db2 "update advise_index set use_index = 'N' where exists= 'N'"
PS D:\xtivia\queries> db2 "update advise_index set use_index = 'Y' where name = 'IDX1505231647160'"
Note that I do tend to run a db2exfmt each time. I'm not technically required to, but I find them useful to have to go back and look at. Again, this index appears to have ZERO impact by itself.
Note that by far, the biggest single impact is that wide index that I really don't like the idea of. Only one other of the 7 recommended new indexes shows any impact by itself. This leads me to wonder why DB2 is even recommending some of the others. I'm hoping that it is because a combination of them makes a real difference. Let's try Index Subtraction to see if that is true.
For Index Subtraction, we tell DB2 to give us the numbers as if all of the proposed indexes exist, and we take away only one at a time to see the impact of not having that particular index. The steps are very similar, just with slightly different updates to the USE_INDEX column.
To start with, we want all of the recommended indexes for this query to be marked with USE_INDEX of Y.
Now one at a time, we will mark the indexes to not be used, and run the explain using evaluate indexes.
PS D:\xtivia\queries> db2 "update advise_index set use_index='N' where name = 'IDX1505231647110'"
Given how wide this index is, I'm really concerned about adding it to a table that is already a bit over-indexed, so I haven't decided yet if I'm going to add it. But at least I now understand that it's the only index really worth considering for this query.
I find that for now, I like to use both the index addition and subtraction methods to really see what difference there might be in the results.
A big thanks to Scott Hayes for covering this topic in depth at his IDUG North America presentation this year. This process has become a regular tool in my arsenal.
Hi Ember this is a very nice article, thanks for sharing it !!.
After you run db2advis you can also get the explain plan of the query with the recommended/new indexes. In this case you didn't copy/paste it but I wonder; most likely the big new index (IDX1505210120080) is used in a join with another table. Don't you think maybe by creating an index using only the column(s) joined with that other table could provide a good result (timerons) without including the rest of the columns?. Cause normally DB2 will only suggest such huge indexes to do an index-scan…. but I guess the most important thing is to optimize the join.
Absolutely, there is additional analysis I could do as to which columns in that index are truly necessary. Would probably make a good separate blog entry.
You can do this from Data Studio and the Query Tuner. After the Index Advisor is run, you Test Candidate indexes by running explain with different combinations of indexes added or removed. You can then do a graph compares to see which indexes helped the most.
Is the Query Tuner a pay-to-use tool? Not a fan of GUIs, so don't use them much.
Even Im not but i recently tried my hands on the Query Tuner in IBM DS, believe me its generating an awesome report. Yeah i agree its basically drawing the data from explain information but html report its generating on a consolidated basis can be used for further discussion with the developers and RCA.
Bit of a late response:-) I've been thinking about investigating this since I saw Scotts presentation at IDUG a couple of years ago, but I somehow forgot about it. The other day I stumbled on your article whilst looking for something else, so I thought I give it a shot. The article is great and easy to follow, so it was easy to get things going.
NR is a bit-mask for the array of indexes (3 means first and second index). It's just a sketch, but once the data is in a table we can do all sort of analyses on it. It should also be easy to add more dimensions to the cost-table.
I actually have a working Jupyter Notebook for this now, it is just not quite ready to publish yet.