Skip to main content

Index Advisor Surfaces Maintained Temporary Indexes Activity on DB2 for i5/OS

Web Doc

Note: This is publication is now archived. For reference only.

thumbnail 

Published on 08 August 2006, updated 12 August 2008

  1. View in HTML

Share this page:   

IBM Form #: TIPS0624


Authors: Hernando Bedoya

    menu icon

    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.

    Context menu path showing schema to Index Advisor to 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.

    MTI information table sorted by the MTI USED column


    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.

    Context menus from item row to Table to Show Indexes


    MTI detail under Show Indexes is summary information. Refer to the Index Advice table for specifics about MTIs.

    MTI detail showing SQL name, type, schema, text, and query use


    The QSYS2/SYSIXADV columns are described in the following table.

    Column nameDescription
    Table for Which Index was AdvisedThe 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.
    SchemaSchema or library name for the table.
    Short NameSystem table name on which the index is advised.
    PartitionPartition detail for the index. Possible values:
    • <blank>, which means for all partitions
    • For Each Partition
    • Specific name of the partition
    Keys AdvisedColumn 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 IndependentThe keys at the beginning of the KEY_COLUMNS_ADVISED field that could be reordered and still satisfy the index being advised.
    Index Type AdvisedPossible values:
    Last Advised for Query UseThe timestamp representing the last time this index was advised for a query.
    Times Advised for Query UseThe 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 TimeEstimated time required to create this index.
    Reason AdvisedCoded reason why index was advised.
    Possible values:
    • Row selection
    • Ordering/Grouping
    • Row selection and Ordering/Grouping
    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 EstimateExecution 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 AdvisedNumber of rows in the table for which the index is being advised, for the last time this index was advised.
    NLSS Table AdvisedThe 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 AdvisedThe library of the sort sequence table.
    MTI USEDThe 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 CREATEDThe number of times that this specific MTI has been created by the optimizer. MTIs do not persist across system IPLs.
    MTI LAST USEDThe 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_USEDMTIUSEDBIGINTNumber of times an MTI that matched the advised
    definition was used by the database because a
    matching permanent index did not exist
    MTI_CREATEDMTICREATEDINTNumber of times this specific index advice was
    used by the database to create a MTI
    LAST_MTI_USEDLASTMTIUSETIMESTAMPLast 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.