Looking for something to help you performance tune your SQL-based database applications without starting heavyweight monitoring tools? A well-defined indexing strategy is one of the most important aspects of tuning your database environment. The Index Advisor was introduced in V5R4 DB2 for i5/OS to help improve your indexing strategy by providing you optimizer feedback without the need to turn on any monitoring tools.

The Problem Discovered

To illustrate Index Advisor's capabilities and benefits, we join a fictitious meeting in progress.

Index Advisor (IA): Admin, I have your daily briefing here. It appears that we have a situation on our hands. We released our new Gee-Whiz product last week, and the order entry part of the application was handling the dramatic increase in orders nicely — but suddenly, it started to perform poorly.

Administrator: What? How could this happen? We have a large system and have always been able to add new applications without particular attention to performance. Advisor, have you been able to determine where the problem is?

IA: As you know, I came in at the same time as V5R4 i5/OS. I automatically monitor the database query activity for you and log query performance information automatically in a database table called QSYS2.SYSIXADV. To simplify your analysis of this information, you can use the iSeries Navigator V5R4 interface to view the contents of this table and work with this information. You appear to have a performance problem accessing and processing the data for the Gee-Whiz application. Simply right-click the GEEWHIZ schema and select Index Advisor (Figure 1) to see what I'm talking about. I can also show you this index advice report for the whole database if you right-click the database name, or for a specific table in your schema if you right-click the table name.

For now, let's stick with the index advisor report for a given schema. I've gathered the key columns needed for the index, the number of times it was advised, and the last time it was advised. Because I also want this data to be a teaching tool, I provide a reason the index was advised and tell you which key columns you can move around (Leading Keys Order Independent when creating the index).

Administrator: Yes, but how do I know that your index advice is showing me the Gee-Whiz application's problem?

IA: By right-clicking a specific piece of index advice, you can select the Show Statements menu option. Doing so brings you to the SQL Plan Cache Viewer (Figure 2), which is also new in iSeries Navigator in V5R4. The viewer has a prefiltered list of the statements that reference this table and generate index advice. Several statements from our application are displayed on the right side, sorted in descending order by longest running execution. This view clearly shows some very slow performance for these statements in the Gee-Whiz application. As with the Index Advisor information, I collected this information without your needing to turn on any kind of monitoring. I do it automatically! I guess I am trying to tell you that the lack of proper indexes in your Gee-Whiz application is the direct cause of your problem. Basically, you have a poor or nonexistent indexing strategy.

Administrator: Okay, I think I get it now. This is all great information — I can't believe how easy it is to get at. But can we somehow save this specific set of statements so that our application developers can further analyze it when we are not in crisis mode?

IA: Sure, you can do that by selecting one or more of the statements and clicking the Create Snapshot button. This brings up the Save dialog box (Figure 3). Just click the OK button and a new SQL Plan Cache Snapshot is created for you. Because the SQL Plan cache itself is stored in memory, all statements in this cache are cleared at IPL time. So, you can think of taking an SQL Plan Cache Snapshot as making a permanent instance of this data. To get a more complete view of your application's query statements, you can remove the Indexes Advised filter, click the Refresh button, and get a list of all the statements that reference the ORDERS table and their performance characteristics. Then you can select all these statements and create another snapshot. This snapshot will give you a more global view of all the queries that might be affected by missing indexes. (See "CQE and SQE Advised Indexes" on page ProVIP 48 for a CQE restriction.)

Administrator: Ah, Developer is here. Developer, we have a serious performance situation with the new Gee-Whiz application. The order processing is being severely affected by the volume of orders. Index Advisor has narrowed the cause to a lack of indexing strategy. I need your help to get this fixed, now! To help us with creating indexes, Advisor has gathered the information automatically. Advisor, please continue.

IA: By right-clicking the advised index in the list, you can select the Create Index menu option to view the New Index dialog box as well as the key columns and the type of index to create (Figure 4). Click the Show SQL button on the New Index dialog box, and you get the Create Index statement in Run SQL Scripts to use for creating these indexes (Figure 5). You might also need another report that makes this index advice even more concise: With a recent addition I have made using a PTF, I can show you not only a list of advised indexes but also a list of condensed advised indexes. You can get to the list the same way as the original list of advised indexes: Select the GEEWHIZ schema from the list and choose the Condense Advised Indexes menu option. With this new report, I have taken much of the guesswork out of selecting the most useful advised indexes (Figure 6). As you can see, I've combined the index advice into three indexes and summed up their Times Advised for Query Use information. Because you can use the Create Index menu option from this list as well, I recommend that Developer start with this list to create the indexes. You can see the existing indexes on a table by going back to the condensed index advice list and right-clicking the advised index, selecting the Table menu option, and then choosing the Show Indexes menu option (Figure 7).

Developer: Um . . . Admin, from looking at the existing indexes, I see the index that I created on the ORDERKEY column. This is how we usually do it, so I just tested the application using an index on that column. I didn't think about the possibility of needing these other columns too. These additional advised indexes do seem logical, now that I think about the application design.

Administrator: Now that we have all this useful information, you have my permission to create the advised index, but please also save the Create Index statement to put it into our change-control process.

The Successful Solution

With the advised index created, the team examines the results.

Developer: I have created the advised index that was from the condensed list. How do we determine whether it is doing any good? How good is this information?

IA: You mean besides the application response time? That's easy. Let's refresh the SQL Plan Cache Viewer to see whether performance for the statements in the GEEWHIZ application is improving (Figure 8).

Developer: Wow, look at that! The response time for those queries has been reduced dramatically.

IA: Admin, I can show you quantitative evidence to prove that the index we just created is behind this improvement. By going back to the Show Indexes dialog box, we can look at the Last Query Use and Query Use Count columns to prove that this index is providing value (Figure 9). This information shows us that the optimizer likes our new indexing strategy and is using the indexes that it recommended. You can also view more information about this particular index, such as its size and how many rows it contains.

Administrator: Thanks for all the assistance Advisor. You really helped bring our new application back online. I am glad that you came with our new V5R4 upgrade. You truly are a great asset to our team.

IA: Developer, I suggest that you have your team review the SQL Plan Cache Snapshot that we captured to determine whether more improvements could be made to your indexing strategy. Admin, I also recommend that you capture an SQL Plan Cache Snapshot of these statements when you feel that you have an optimal query performance for this application. That way, you can compare this well-running environment with the same statements if you experience performance problems again.

Administrator: Indeed, Developer, we should use Advisor's talents to help us review our indexing strategy for all our applications. Now, if you'll excuse me, I need to give our CIO a full briefing on this matter.

Cleaning Up

Now that disaster has been averted, the Index Advisor can perform some cleanup operations and return to monitoring the system.

IA: Developer, I should tell you that I consider this SYSIXADV table to be more of a historical table, so I don't automatically remove index advice from it. You might find some management features useful for managing this list. To remove one or more of these from the list, just choose the ones you want, right-click them, and select Remove from List. This option might be useful if you want to remove specific pieces of advice that you know you aren't going to use.

You can use the other management features from the table, schema, or database by right-clicking them (Figure 10). The Clear All Advised Indexes menu option deletes all advised indexes for the selected object from the SYSIXADV table. This ability is useful when you feel that you have a good indexing strategy defined for your application and want to get a fresh start. The Prune Advised Indexes menu option deletes all advised indexes for tables that no longer exist. This option is a good way to prune out old index advice for tables that you used only for testing or no longer need.

One additional item that I didn't show you on the index advisor report is the Maintained Temporary Index (MTI) columns. These MTI columns are significant because they tell you when the advised index was so useful to the optimizer that it automatically created the MTI for you. They are still temporary indexes and will not survive an IPL, but any query statement in your environment can used them — not just the statement that caused the MTI to be created. Basically, the optimizer is telling you that this is a great candidate for a permanent index. Now I will get back to monitoring your system for you. Goodbye.

Can't Wait to Try It?

As this example shows, the new Index Advisor function in V5R4 of DB2 for i5/OS is a strong addition to your set of tools as you work through indexing strategies for your applications. Maybe some of these potential improvements are waiting for you to discover them within your own company. With the right tools and indexing strategy, you can improve your company's application response times and ultimately provide your customers with a better overall experience.

Jim Flanagan is an advisory software engineer at IBM in database development for DB2 for i5/OS. He is the team leader of iSeries Navigator — Database. Jim lives in Rochester, Minnesota, and can be reached at jflanagan@us.ibm.com.