IC SunsetThe developerWorks Connections platform will be sunset on December 31, 2019. On January 1, 2020, this forum will no longer be available. More details available on our FAQ.
Topic
  • 6 replies
  • Latest Post - ‏2018-03-09T19:56:08Z by B_Benson
B_Benson
B_Benson
6 Posts

Pinned topic Index Advisor - MTI Used & Times Advised for Query Use - and 2000+ MTIs

‏2018-03-08T16:20:20Z | advisor index mti performance

Hello,

I've been around Db2 since the mid-80s, but brand new to Db2 on the i.  So forgive me if this is a foolish question.

I've been reading a lot about performance on the i (Kent Milligan, Mike Cain, Toronto Users Group, Redbooks).

However, I'm a bit confused from what I'm seeing when using Index Advisor.  It is returning some recommendations that have high values in "Times Advised for Query Use" and then 0 for MTI Used.  One I see has been Advised for Query Use over 1 billion times (with a B) and MTI Used is 0 - the Last Advised for Query Use timestamp was 3 hours ago from my typing this.  Perhaps I'm missing the true intent of these columns...

I'm assuming an MTI was created for the columns that the output lists in the "Keys Advised" column (my assumption could very well be wrong).  Why is it not used?  Does it create this MTI and then the optimizer looks at the statistics for it and figure it won't be beneficial?

And since I'm on the subject of MTIs - does it hurt to have all these MTIs around?  Should I drop those that are more than 6 months old or so or just used once or twice?  When DML is run against a table, are all of these MTIs updated even if they've not been used in months or years?  I've seen one table with over 2000 MTIs - some of the recommendations are from 2014 (and this confuses me - I thought they disappeared after an IPL - certainly this machine has been rebooted since then - or perhaps the MTI is gone, but the recommendation is still in the system table).  Most of the number of advised times are under 10 times total.  Only 25 of these 2000+ indexes have an MTI Used count of more than 10.  On the z and LUW, they say having bunches of indexes effects DML/Load performance and if they aren't being used enough, drop them to improve overall performance.  2000 would be considered a bit excessive (to put it lightly). ****** Editted - looks like MTI Created with a non-zero means the MTI was created - is that correct?   If so, I show 57 with non-zero entries - that's still a lot, but nothing like 2000+.

Hope my questions are clear - thanks for any help or direction.  Any recent good books/sites you can point me to?

Updated on 2018-03-08T18:11:53Z at 2018-03-08T18:11:53Z by B_Benson
  • Cliff Dowell
    Cliff Dowell
    6 Posts
    ACCEPTED ANSWER

    Re: Index Advisor - MTI Used & Times Advised for Query Use - and 2000+ MTIs

    ‏2018-03-08T20:29:53Z  

    Hi Brian,

    I'll take a swing at some of these. :)

    Many "Times Advised" but no "MTI Used": Without knowing the details of the table in question, perhaps it might be that the engine determined that while the index would be desirable, the actual creation time of the index would have been cost prohibitive to the overall execution time of the query? Check the MTI Created Column for that row. If it's Zero, one may have never been created. 

    Does it hurt to have all these MTIs around: When I have time, I go out and research the use metrics of the MTIs. Just like other index advice, if they appear relevant with frequent and recent usage, I create the index as a permanent object with a source and bring it under the control of our change management system. If the MTI hasn't been used in months/years (time for an IPL?? Just kidding!), I usually check it against the index advised date and clear the advice for that record if it's not recent. When a matching persistent index is found by the optimizer, the MTI is deleted. Index advice will persist past an IPL. Since our business day is routine, I keep and work with advice for 90-180 days. Since we do our own development, how/what data we access can change over time. It's possible that a heavily advised index may not be advised any longer since the programmer's last change. 

    Bunches of indexes effects DML/Load performance: On the IBM i, things will react generally similar to what you know of the Z. In general terms, I agree that high index counts over a table will impact inserts and updates. There are tradeoffs for different types of performance in differnt types of tables. Rather than digressing into a dissertation on Index Strategies, I'll offer that in an OLTP environment with high volume, keeping indexes limited to the essential is "generally" good. If you're fortunate enough to have an OLAP environment, you'll likely have different and varied indexes for reporting but you'll also be using other tools/strategies to augment indexes to get your performance.

    This may be a bit older (V5R4) but there is some good info in it regarding MTIs: http://www.redbooks.ibm.com/abstracts/tips0624.html?Open

    Welcome to the i! Most of us that have been on it a while, love it. It certainly sounds like yours is in good hands with you. 

    Cliff

  • B.Hauser
    B.Hauser
    320 Posts
    ACCEPTED ANSWER

    Re: Index Advisor - MTI Used & Times Advised for Query Use - and 2000+ MTIs

    ‏2018-03-09T06:35:01Z  

    How often do you run an IPL?

    The index advice table (SYSIXADV) includes all advices since the beginning of the SQE (in release V5R2M0), if you never cleared or revised this table.

    MTIs are only created if there is no other way to handle the query otherwise the index is only adviced.

    An MTI exists as long as there is an access plan in the plan cache that uses this MTI. If the last access plan using this MTI get removed from the plan cache the MTI disappears.

    Each SQL statement needs a access plan before it can be executed. The access plan for any query is created or validated during the query optimization.

    All access plans are stored in the SQE plan cache. Up to 3 access plans per statement can be stored in the Plan Cache. If the plan cache size reaches the maximum, the oldest i.e. the longest not used access plans are removed from the plan cache.

    An access plan can be removed over the ACS Performance Center. The plan cache can be cleared by executing the CLEAR_PLAN_CACHE stored procedure,

    In either way the plan cache is cleared with the IPL. (Contrary to the index advice table).

    If you never cared about the index advice table, I'd suggest to remove the advices, wait for several day or weeks, and then check the index advisor again.

     

    Birgitta

     

  • Cliff Dowell
    Cliff Dowell
    6 Posts

    Re: Index Advisor - MTI Used & Times Advised for Query Use - and 2000+ MTIs

    ‏2018-03-08T20:29:53Z  

    Hi Brian,

    I'll take a swing at some of these. :)

    Many "Times Advised" but no "MTI Used": Without knowing the details of the table in question, perhaps it might be that the engine determined that while the index would be desirable, the actual creation time of the index would have been cost prohibitive to the overall execution time of the query? Check the MTI Created Column for that row. If it's Zero, one may have never been created. 

    Does it hurt to have all these MTIs around: When I have time, I go out and research the use metrics of the MTIs. Just like other index advice, if they appear relevant with frequent and recent usage, I create the index as a permanent object with a source and bring it under the control of our change management system. If the MTI hasn't been used in months/years (time for an IPL?? Just kidding!), I usually check it against the index advised date and clear the advice for that record if it's not recent. When a matching persistent index is found by the optimizer, the MTI is deleted. Index advice will persist past an IPL. Since our business day is routine, I keep and work with advice for 90-180 days. Since we do our own development, how/what data we access can change over time. It's possible that a heavily advised index may not be advised any longer since the programmer's last change. 

    Bunches of indexes effects DML/Load performance: On the IBM i, things will react generally similar to what you know of the Z. In general terms, I agree that high index counts over a table will impact inserts and updates. There are tradeoffs for different types of performance in differnt types of tables. Rather than digressing into a dissertation on Index Strategies, I'll offer that in an OLTP environment with high volume, keeping indexes limited to the essential is "generally" good. If you're fortunate enough to have an OLAP environment, you'll likely have different and varied indexes for reporting but you'll also be using other tools/strategies to augment indexes to get your performance.

    This may be a bit older (V5R4) but there is some good info in it regarding MTIs: http://www.redbooks.ibm.com/abstracts/tips0624.html?Open

    Welcome to the i! Most of us that have been on it a while, love it. It certainly sounds like yours is in good hands with you. 

    Cliff

  • B.Hauser
    B.Hauser
    320 Posts

    Re: Index Advisor - MTI Used & Times Advised for Query Use - and 2000+ MTIs

    ‏2018-03-09T06:35:01Z  

    How often do you run an IPL?

    The index advice table (SYSIXADV) includes all advices since the beginning of the SQE (in release V5R2M0), if you never cleared or revised this table.

    MTIs are only created if there is no other way to handle the query otherwise the index is only adviced.

    An MTI exists as long as there is an access plan in the plan cache that uses this MTI. If the last access plan using this MTI get removed from the plan cache the MTI disappears.

    Each SQL statement needs a access plan before it can be executed. The access plan for any query is created or validated during the query optimization.

    All access plans are stored in the SQE plan cache. Up to 3 access plans per statement can be stored in the Plan Cache. If the plan cache size reaches the maximum, the oldest i.e. the longest not used access plans are removed from the plan cache.

    An access plan can be removed over the ACS Performance Center. The plan cache can be cleared by executing the CLEAR_PLAN_CACHE stored procedure,

    In either way the plan cache is cleared with the IPL. (Contrary to the index advice table).

    If you never cared about the index advice table, I'd suggest to remove the advices, wait for several day or weeks, and then check the index advisor again.

     

    Birgitta

     

  • B_Benson
    B_Benson
    6 Posts

    Re: Index Advisor - MTI Used & Times Advised for Query Use - and 2000+ MTIs

    ‏2018-03-09T11:24:31Z  

    Hi Brian,

    I'll take a swing at some of these. :)

    Many "Times Advised" but no "MTI Used": Without knowing the details of the table in question, perhaps it might be that the engine determined that while the index would be desirable, the actual creation time of the index would have been cost prohibitive to the overall execution time of the query? Check the MTI Created Column for that row. If it's Zero, one may have never been created. 

    Does it hurt to have all these MTIs around: When I have time, I go out and research the use metrics of the MTIs. Just like other index advice, if they appear relevant with frequent and recent usage, I create the index as a permanent object with a source and bring it under the control of our change management system. If the MTI hasn't been used in months/years (time for an IPL?? Just kidding!), I usually check it against the index advised date and clear the advice for that record if it's not recent. When a matching persistent index is found by the optimizer, the MTI is deleted. Index advice will persist past an IPL. Since our business day is routine, I keep and work with advice for 90-180 days. Since we do our own development, how/what data we access can change over time. It's possible that a heavily advised index may not be advised any longer since the programmer's last change. 

    Bunches of indexes effects DML/Load performance: On the IBM i, things will react generally similar to what you know of the Z. In general terms, I agree that high index counts over a table will impact inserts and updates. There are tradeoffs for different types of performance in differnt types of tables. Rather than digressing into a dissertation on Index Strategies, I'll offer that in an OLTP environment with high volume, keeping indexes limited to the essential is "generally" good. If you're fortunate enough to have an OLAP environment, you'll likely have different and varied indexes for reporting but you'll also be using other tools/strategies to augment indexes to get your performance.

    This may be a bit older (V5R4) but there is some good info in it regarding MTIs: http://www.redbooks.ibm.com/abstracts/tips0624.html?Open

    Welcome to the i! Most of us that have been on it a while, love it. It certainly sounds like yours is in good hands with you. 

    Cliff

    Cliff - thanks for taking the time to type out an in-depth response - lots for me to read and reread.  Much appreciated.

  • B_Benson
    B_Benson
    6 Posts

    Re: Index Advisor - MTI Used & Times Advised for Query Use - and 2000+ MTIs

    ‏2018-03-09T11:26:20Z  
    • B.Hauser
    • ‏2018-03-09T06:35:01Z

    How often do you run an IPL?

    The index advice table (SYSIXADV) includes all advices since the beginning of the SQE (in release V5R2M0), if you never cleared or revised this table.

    MTIs are only created if there is no other way to handle the query otherwise the index is only adviced.

    An MTI exists as long as there is an access plan in the plan cache that uses this MTI. If the last access plan using this MTI get removed from the plan cache the MTI disappears.

    Each SQL statement needs a access plan before it can be executed. The access plan for any query is created or validated during the query optimization.

    All access plans are stored in the SQE plan cache. Up to 3 access plans per statement can be stored in the Plan Cache. If the plan cache size reaches the maximum, the oldest i.e. the longest not used access plans are removed from the plan cache.

    An access plan can be removed over the ACS Performance Center. The plan cache can be cleared by executing the CLEAR_PLAN_CACHE stored procedure,

    In either way the plan cache is cleared with the IPL. (Contrary to the index advice table).

    If you never cared about the index advice table, I'd suggest to remove the advices, wait for several day or weeks, and then check the index advisor again.

     

    Birgitta

     

    Birgitta - thanks for your info-filled response - lots of good things to study.  Much appreciated.

  • FPLAZAVI
    FPLAZAVI
    20 Posts

    Re: Index Advisor - MTI Used & Times Advised for Query Use - and 2000+ MTIs

    ‏2018-03-09T15:36:16Z  

    Maintained Temporary Indexes briefly explained


    Published on January 22, 2018

    https://www.ibm.com/developerworks/ibmi/library/i-manage-mti/index.html?ca=drs-

  • B_Benson
    B_Benson
    6 Posts

    Re: Index Advisor - MTI Used & Times Advised for Query Use - and 2000+ MTIs

    ‏2018-03-09T19:56:08Z  
    • FPLAZAVI
    • ‏2018-03-09T15:36:16Z

    Maintained Temporary Indexes briefly explained


    Published on January 22, 2018

    https://www.ibm.com/developerworks/ibmi/library/i-manage-mti/index.html?ca=drs-

    Thanks for the info - an added benefit, it linked me to other materials