Index Advisor, Part 2
Published: April 11, 2012
by Paul Tuohy
In part 1 of this series, I introduced you to the basic functionality of Index Advisor. In this article, we will take a look at more of the options and functionality available with Index Advisor for Systems Director Navigator for i.
Apart from the obvious information (table and schema names), the Index Advisor window also gives us some pertinent information, such as:
- The advised key
- The first and last time the index was advised
- The number of times the index was advised
- The longest and average execution times for the queries that generated the index advise
- The estimated index creation time
- The number of rows in the table the last time the index was advised
- The reason the index was advised (row selection and/or ordering/grouping)
Of course, the most important information is the execution time, since this is usually what causes a performance hit. The number of times the index was advised can also be a possible performance bottleneck.
This is usually enough information to determine whether or not to go ahead and create an index (and if in doubt, create it anyway) but, at times, you may want to investigate a little further. For example, is the index being advised for one query statement or a number of query statements? If more than one statement, is the average query time higher for certain statements? What job/user initiated the query?
Well, this information is available in the SQL Plan Cache, and the easiest way to get there is to right-click on the required line in the list and select Show Statements from the context menu to see the window shown in Figure 1. Patience is required here: it usually takes a while for the contents of the window to be populated. The contents of the SQL Plan Cache are a discussion for another day, but the primary information we are looking for (the statement) is readily available. We also have the execution times for each of the statements.
Figure 1: SQL Plan Cache. (Click graphic to enlarge.)
Managing Index Advisor
Within the Index Advisor window you have the usual controls for selecting the sequence of columns and which columns to display (F12), the sequence of items in the list (click column heading or View-Sort to sequence by multiple columns) or the Include option (F11) allows you to define the criteria for the indexes that should be displayed in the list, as shown in Figure 2.
Figure 2: Include Option for Index Advisor.
When you right-click on the Database name in System i Navigator, the Index Advisor option on the context menu provides three options to aid in managing the contents of the SYSIXADV table:
- Clear All Advised Indexes clears the contents of SYSIXADV. It is worthwhile considering this option if you want to start with a clean slate.
- Condense Advised Indexes can be useful when the index advisor has advised many different indexes for the same table. This option will condense the advised indexes.
- Prune Advised Indexes removes advised indexes for tables that no longer exist.
Index Advisor in Systems Director Navigator for i
Systems Director Navigator for i offers the same Index Advisor functionality as System i Navigator. You access Index Advisor by selecting the Databases task (in IBM i Management) and clicking on the database name. Then, from the drop-down Select Action list, select Index Advisor→Index Advisor and click on the Go button. Note that the sub-list for Index Advisor also offers the options to Clear All, Condense, and Prune Advised Indexes. Figures 3 and 4 show the selection of Index Advisor and the resulting list.
Figure 3: Selecting Index Advisor in Systems Director Navigator for i. (Click graphic to enlarge.)
Figure 4: Index Advisor in Systems Director Navigator for i. (Click graphic to enlarge.)
The context menu for an item in the list (the double arrow after a name) provides the same context menu as we had in System i Navigator (as shown in Figure 4). We even have the option to view the SQL statements.
Choose Your Poison
Whether you opt to use System i Navigator, Systems Director Navigator for i, or running your own queries against SYSIXADV and coming up with your own automated or manual means of creating indexes; the important point is that you get the indexes created.
DB2 on i is probably the most powerful database in existence, but only if we give it the required information to perform to its best.
Paul Tuohy is CEO of ComCon, an iSeries consulting company, and is one of the co-founders of System i Developer, which hosts the RPG & DB2 Summit conferences. He is an award-winning speaker who also speaks regularly at COMMON conferences, and is the author of "Re-engineering RPG Legacy Applications," "The Programmers Guide to iSeries Navigator," and the self-study course called "iSeries Navigator for Programmers." Send your questions or comments for Paul to Ted Holt via the IT Jungle Contact page.
Index Advisor, Part 1