Wonder what it would be like to have your own advisor working in the background for you, always ready to provide relevant and accurate advice?
In V5R4, DB2 for i introduces its On Demand index advisor. This means that the database continuously generates, collects and groups index advice based upon the queries being executed all without turning anything on! The index advice contains a recommendation for a permanent index along with the environment that existed when the advice was generated, as well as including an estimate of how long it might take to create the index.
With V5R4, index advice can be found in four main areas:
- Within database monitor output under 3020 records
- Within Visual Explain under the Index and Statistics Advisor
- From IBM i Navigator's Index Advisor facility
- Via the SQL Plan Cache, by filtering on 'Queries with index advised'
The first two areas existed prior to V5R4. The last two areas are introduced with V5R4 and are the focus of this article.
First we will explain the new index advisor facility that is shipped with V5R4 and how it can be used to implement an improved index strategy through the use of an example. Next we will show how to locate the advisor information and describe the information it contains. Then we will look at how this index advice information can be used in conjunction with the Index Evaluator to give you a more complete picture of your query environment. So let's get started to see what this new advisor can do for you.
Based on what your goal is you can access the Index Advisor function database wide, schema (library) wide, or for a specific table. If you want to see all the advice for the database you would start from the database folder. If you wanted to view all the index advice for all tables in a schema you would start from the schema folder as shown in figure 1. If you know the table name that you are interested in, then you could go directly to the Tables folder and select the index advice for that table.
Throughout this article, we are going to make use of a standard sample schema under the name INDEXADVIC. The sample schema includes an SQL table (SALES) which has no defined indexes. A set of queries have been executed against the table to simulate normal business reporting activities. As the queries execute, the database collects index advice in a system catalog (SYSIXADV table in QSYS2). Advice is collected within this catalog until a user deletes the entire table or specific rows within the table. Refer to the Database Performance and Query Optimization Book for more details. The IBM i Navigator index advice facility is built over this catalog. Once all the sample queries have been executed, we show the index advice for that schema in figure 2.
After the list of advice is displayed, there are several key pieces of information that you can view. Each row in the list contains the table name and schema that this index was advised over. The list is sorted by default on the number of times the index was advised so that the most frequently advised indexes are first in the list. To resort the list by any other column, merely select that column header. You can also see additional statistics for when was the last time the index was advised and the estimated time it might take to create the index. With these pieces of information you can gauge the impact of creating the new index. If the index is recommended a large number of times, but was last recommended a month ago, and the table is one that is more of a historical table, you might conclude that there's no compelling reason to act on the advice. However, if the advice were recommended five minutes ago and is advised a large number of times, and the table is one of your key production tables, it may be a great candidate.
There is a column that tells you the type of index recommended, which is either Binary Radix or Encoded Vector Index (EVI). There is a column that shows you the reason why the optimizer recommended the index. All these columns combine to give you a more complete picture of the advice from the optimizer.
Another important piece of information is the list of key columns that are being advised for this index along with the leading key order independent columns. If there are keys in the leading key order independent column, it means that the index could have the key columns specified in any order. Indexes are created over specific keys, in a certain order. When the index advisor specifies leading key order independent columns, there's an opportunity to collapse advice to cover more queries. Since indexes need to be maintained by the database, better performance will be achieved by reducing the number of indexes.
As a consumer of this information, you will want to take into account multiple instances of index advice over the same table, as well as considering any existing indexes, to finally determine whether to create, drop or replace any indexes.
In addition, there are actions that you can perform on the entries in the list. First we will discuss the actions that work against the entire list. One of these is called Clear Advised Indexes. The underlying data store for the advice is an SQL table, SYSIXADV in schema QSYS2. This table is populated by the database, but is not maintained. As indexes get created or tables are dropped, the old advice is not removed automatically by the system. It can be considered a history type table and as such will keep accumulating rows, so you may want to clear out the table from time to time. You could use the Clear Advised Indexes menu option. For the Database-wide and schema-wide views you have an additional option to Prune Advised Indexes. This option will remove all index advice rows where the table that the advice was created for no longer exists. There is also a menu option to remove the selected row of index advice from the list.
Now for the individual menu options for the items in the list. First is the Create Index option which you can use to display the advised index in the New Index dialog. This will give you a way to view the recommended index as it would be if you were to create it from the New Index dialog, including index type, key columns, column order, etc. Additionally on the New Index dialog you could use the Show SQL button to display the CREATE INDEX DDL statement in Run SQL Scripts. From there you could copy and paste this SQL statement into your change management system. In our INDEXADVIC example, two rows of advice could be satisfied by creating an index with key columns SALES_PERSON ASC and SALES_DATE ASC as shown in figure 3. A third row is satisfied by creating an index with key columns SALES_DATE ASC , REGION ASC , SALES_PERSON ASC.
The next menu option you can choose for an advised index is to Show Statements. This will show all statements in the SQL Query Engine (SQE) Plan Cache that reference this same table AND also generated index advised. With this information you can now determine what other query statements have generated index advice over the same table, and thereby determine what additional positive impact may be gained by creating the index. Note: This option will only display SQL statements that were executed using the SQE.
Finally, there is a cascading Table menu available for each index advice that allows you to do many of the table actions that you can do for a particular table in the list. Things like view the contents, definition, description and several other options. The most intriguing of these options may be the Show Indexes menu option. If you select this option you can launch the Show Index list to see all the Indexes currently defined over the table. By using the index advice list in conjunction with the Show Indexes list, you can start to work on your indexing strategy for your table. For example if one index advice row shows that you should have an index with certain columns, but you already have some of those columns in an existing index. Maybe all you need to do is to add an additional column recommended by the index advisor to your existing index and then you would have a more useful index for the optimizer to use. In our example since we didn't have existing indexes, we decided to create a subset of the recommended indexes. So after creating the two indexes for INDEXADVIC, we can observe that the indexes exist and have never been used by the query engine since we haven't rerun the queries yet as shown in figure 4.
Now here is the exciting step. After re-executing the set of queries, all we need to do is to select refresh on the Show Indexes window to confirm that the new indexes were being used by the optimizer as shown in figure 5. Insiders tip. For existing indexes, it might be difficult to gauge the affect of a change in index strategy because the usage statistics have accumulated over time. To overcome this hurdle, use the CHGOBJD command to reset the statistics for existing indexes. (CHGOBJD OBJ(schema/index) OBJTYPE(*FILE) USECOUNT(*RESET))
So we have shown a simple but powerful example of an approach which can be used to test, refine and validate index strategies. Because the data involved is always available, the analysis can be done on any system or workload, including production level environments.
Now maybe it's time to buy the new index advisor a cup of coffee for a job well done.