Index Advisor, Part 2
April 11, 2012 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. More Options Apart from the obvious information (table and schema names), the Index Advisor window also gives us some pertinent information, such as:
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.
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:
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. RELATED STORY
|
