Index Advisor Surfaces Maintained Temporary Indexes Activity on DB2 for i5/OS
Web Doc
Note: This is publication is now archived. For reference only.
Abstract
DB2® for i5/OS® on V5R4 provides features and tooling to define, test, and improve index strategies. With the addition of Maintained Temporary Index (MTI) detail, the user can better understand temporary indexes and their effect on system performance.
Written by Scott Forstie
SQL development leader
IBM® DB2 UDB for i5/OS
Contents
Maintained Temporary Indexes (MTIs) are created and used by the SQE optimizer in V5R4, under certain situations where a matching permanent index does not exist. The MTI existence and usage information has been externalized to the customer by way of the iSeries Navigator Index Advisor and Show Indexes facilities.
Viewing the MTI information in the context of index advice can help explain performance fluctuations. In one common scenario, performance is shown to be degraded after an IPL, but improves over time as queries are executed. MTIs do not persist across an IPL. By accessing the Show Indexes detail for the table, you can confirm that MTIs exist. Further, the index advisor information details how often a specific MTI has been created and used and how recently it was used.
When paired with other index advice environmental information such as Times Advised and Average Query Estimate, a better index strategy can be determined. If an MTI becomes a crucial part of an index strategy, it might be the perfect time to change the permanent indexes to avoid using the MTI altogether. This can be accomplished by simply launching the Create Index dialog from the index advisor. The optimizer will recognize the existence of a matching permanent index and discard the MTI. This change will be reflected in the index advisor table via the MTI Last Used timestamp.
After making any change to an index strategy, the usage information within the permanent indexes can be reset. Resetting the usage statistics makes it easier to evaluate the value of the current set of permanent indexes. Because an implied maintenance cost is associated with each index, having as few permanent indexes as possible is preferable.
Use this command to reset Index statistics:
CHGOBJD OBJ(schema/index) OBJTYPE(*FILE) USECOUNT(*RESET))
Additionally, after an index strategy is changed, it might be useful to clear out the existing index advice for that schema or table. There are many ways to clear the advice, both within iSeries Navigator and from Run SQL Scripts. After clearing the advice and index statistics, the index strategy is ready to be evaluated. These techniques work in a performance analysis environment, on a development system or on production machines.
The IBM eServer iSeries database has an on demand index advisor solution. The information is there, just waiting for someone to extract the value by making better choices to achieve optimal performance.
Visual tour
From the iSeries Navigator Schemas folder view, right-click the schema of choice to launch Index Advisor.
The MTI information appears on the far right side. The table can be resorted by selecting any column header. The table shown here is sorted by MTI USED.
Right-click a row of index advice to launch Show Indexes (as shown) for easy comparison between existing permanent indexes and MTIs over this table to the indexes being advised.
MTI detail under Show Indexes is summary information. Refer to the Index Advice table for specifics about MTIs.
The QSYS2/SYSIXADV columns are described in the following table.
Database manager indexes advised system table
PTF information
The Index Advisor MTI enabling Server PTFs were included in SF99504: 540 DB2 UDB for iSeries Group Level #4.
The iSeries Navigator enabling Client PTFs will ship on September 1, 2006.
Related articles
DB2 for i5/OS Redefines On Demand for Indexing
http://search.crownpeak.com/cpt_redirect/1143?account=466885406922&qid=1970&ht=
The Optimizer Takes Its Own Advice
http://www.ibmsystemsmag.com/i5/june06/trends/6112p1.aspx?ht=
Taming the Business-Intelligence Monster
http://www.ibmsystemsmag.com/i5/august07/features/16080p1.aspx
Viewing the MTI information in the context of index advice can help explain performance fluctuations. In one common scenario, performance is shown to be degraded after an IPL, but improves over time as queries are executed. MTIs do not persist across an IPL. By accessing the Show Indexes detail for the table, you can confirm that MTIs exist. Further, the index advisor information details how often a specific MTI has been created and used and how recently it was used.
When paired with other index advice environmental information such as Times Advised and Average Query Estimate, a better index strategy can be determined. If an MTI becomes a crucial part of an index strategy, it might be the perfect time to change the permanent indexes to avoid using the MTI altogether. This can be accomplished by simply launching the Create Index dialog from the index advisor. The optimizer will recognize the existence of a matching permanent index and discard the MTI. This change will be reflected in the index advisor table via the MTI Last Used timestamp.
After making any change to an index strategy, the usage information within the permanent indexes can be reset. Resetting the usage statistics makes it easier to evaluate the value of the current set of permanent indexes. Because an implied maintenance cost is associated with each index, having as few permanent indexes as possible is preferable.
Use this command to reset Index statistics:
CHGOBJD OBJ(schema/index) OBJTYPE(*FILE) USECOUNT(*RESET))
Additionally, after an index strategy is changed, it might be useful to clear out the existing index advice for that schema or table. There are many ways to clear the advice, both within iSeries Navigator and from Run SQL Scripts. After clearing the advice and index statistics, the index strategy is ready to be evaluated. These techniques work in a performance analysis environment, on a development system or on production machines.
The IBM eServer iSeries database has an on demand index advisor solution. The information is there, just waiting for someone to extract the value by making better choices to achieve optimal performance.
Visual tour
From the iSeries Navigator Schemas folder view, right-click the schema of choice to launch Index Advisor.
The MTI information appears on the far right side. The table can be resorted by selecting any column header. The table shown here is sorted by MTI USED.
Right-click a row of index advice to launch Show Indexes (as shown) for easy comparison between existing permanent indexes and MTIs over this table to the indexes being advised.
MTI detail under Show Indexes is summary information. Refer to the Index Advice table for specifics about MTIs.
The QSYS2/SYSIXADV columns are described in the following table.
| Column name | Description |
| Table for Which Index was Advised | The optimizer is advising creation of a permanent index over this table. This is the long name for the table. The advice was generated because the table was queried and no existing permanent index could be used to improve the performance of the query. |
| Schema | Schema or library name for the table. |
| Short Name | System table name on which the index is advised. |
| Partition | Partition detail for the index. Possible values:
|
| Keys Advised | Column names for the advised index. The order of the column names is important. The names should be listed in the same order on the CREATE INDEX SQL statement, unless the leading, order-independent key information indicates that the ordering can be changed. |
| Leading Keys Order Independent | The keys at the beginning of the KEY_COLUMNS_ADVISED field that could be reordered and still satisfy the index being advised. |
| Index Type Advised | Possible values:
|
| Last Advised for Query Use | The timestamp representing the last time this index was advised for a query. |
| Times Advised for Query Use | The cumulative number of times this index has been advised. This count should stop increasing when a matching permanent index is created. The row of advice will remain in this table until the user removes it. |
| Estimated Index Creation Time | Estimated time required to create this index. |
| Reason Advised | Coded reason why index was advised. Possible values:
|
| Logical Page Size Advised (KB) | Recommended page size to be used on the PAGESIZE keyword of the CREATE INDEX SQL statement when creating this index. |
| Most Expensive Query Estimate | Execution time in seconds of the longest-running query that generated this index advice. |
| Average of Query Estimates (seconds) | Average execution time in seconds of all queries that generated this index advice. |
| Rows in Table when Advised | Number of rows in the table for which the index is being advised, for the last time this index was advised. |
| NLSS Table Advised | The sort sequence table in use by the query that generated the index advice. For more detail about sort sequences: http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/rzajq/usesortseq.htm and http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/db2/rbafzmstsortsequence.htm#sortsequence |
| NLSS Schema Advised | The library of the sort sequence table. |
| MTI USED | The number of times that this specific MTI has been used by the optimizer. The optimizer will stop using a matching MTI when a permanent index is created. |
| MTI CREATED | The number of times that this specific MTI has been created by the optimizer. MTIs do not persist across system IPLs. |
| MTI LAST USED | The timestamp representing the last time this specific MTI was used by the optimizer to improve the performance of a query. The MTI Last Used field can be blank, which indicates that an MTI that exactly matches this advice has never been used by the queries that generated this index advice. |
Database manager indexes advised system table
The following table describes the indexes advised system table.
Column name | System column name | Data type | Description |
TABLE_NAME | TBNAME | VARCHAR(258) | Table over which an index is advised |
TABLE_SCHEMA | DBNAME | CHAR(10) | Schema containing the table |
SYSTEM_TABLE_NAME | SYS_TNAME | CHAR(10) | System table name on which the index is advised |
PARTITION_NAME | TBMEMBER | CHAR(10) | Partition detail for the index |
KEY_COLUMNS_ADVISED | KEYSADV | VARCHAR(16000) | Column names for the advised index |
LEADING_COLUMN_KEYS | LEADKEYS | VARCHAR(16000) | Leading, Order Independent keys, the keys at the beginning of the KEY_COLUMNS_ADVISED field that could be reordered and still satisfy the index being advised |
INDEX_TYPE | INDEX_TYPE | CHAR(14) | Radix (default) or EVI |
LAST_ADVISED | LASTADV | TIMESTAMP | Last time this row was updated |
TIMES_ADVISED | TIMESADV | BIGINT | Number of times this index has been advised |
ESTIMATED_CREATION_TIME | ESTTIME | INT | Estimated number of seconds for index creation |
REASON_ADVISED | REASON | CHAR(2) | Coded reason why index was advised |
LOGICAL_PAGE_SIZE | PAGESIZE | INT | Recommended page size for index |
MOST_EXPENSIVE_QUERY | QUERYCOST | INT | Execution time in seconds of the query |
AVERAGE_QUERY_ESTIMATE | QUERYEST | INT | Average execution time in seconds of the query |
TABLE_SIZE | TABLE_SIZE | BIGINT | Number of rows in table when the index was advised |
NLSS_TABLE_NAME | NLSSNAME | CHAR(10) | NLSS table to use for the index |
NLSS_TABLE_SCHEMA | NLSSDBNAME | CHAR(10) | Library name of the NLSS table |
| MTI_USED | MTIUSED | BIGINT | Number of times an MTI that matched the advised definition was used by the database because a matching permanent index did not exist |
| MTI_CREATED | MTICREATED | INT | Number of times this specific index advice was used by the database to create a MTI |
| LAST_MTI_USED | LASTMTIUSE | TIMESTAMP | Last time an MTI was used by the database because a matching permanent index did not exist |
PTF information
The Index Advisor MTI enabling Server PTFs were included in SF99504: 540 DB2 UDB for iSeries Group Level #4.
The iSeries Navigator enabling Client PTFs will ship on September 1, 2006.
Related articles
DB2 for i5/OS Redefines On Demand for Indexing
http://search.crownpeak.com/cpt_redirect/1143?account=466885406922&qid=1970&ht=
The Optimizer Takes Its Own Advice
http://www.ibmsystemsmag.com/i5/june06/trends/6112p1.aspx?ht=
Taming the Business-Intelligence Monster
http://www.ibmsystemsmag.com/i5/august07/features/16080p1.aspx
Special Notices
The material included in this document is in DRAFT form and is provided 'as is' without warranty of any kind. IBM is not responsible for the accuracy or completeness of the material, and may update the document at any time. The final, published document may not include any, or all, of the material included herein. Client assumes all risks associated with Client's use of this document.