Topic
  • 6 replies
  • Latest Post - ‏2013-08-29T16:09:59Z by jonesy_burt
jonesy_burt
jonesy_burt
26 Posts

Pinned topic V5R4 upgrade to 7.1 - query optimization and index advice

‏2013-08-28T15:08:22Z |

One of our customers recently performed a migration from a V5R4 system to 7.1

Following the upgrade one of their queries started taking 20 minutes rather than about 2 seconds.

I investigated the query before and after migration and found a few strange things that I hope somebody can explain:

The 5.4 query was optimized by CQE because of a Select/Omit Logical file. The 7.1 query was, as expected, optimized by SQE so I don't understand the huge performance degradation.

The 7.1 query was using a completely different index and access plan to the 5.4 query. Why would this happen, surely the optimizer could always use the existing indexes and access plan.

This problem was reported to IBM and an index was suggested that was not (as far as I can tell) in the index advisor. When this was created the query performed well. Why wasn't the index in the advisor?

Hope somebody can shed some light on these issues.

  • krmilligan
    krmilligan
    450 Posts

    Re: V5R4 upgrade to 7.1 - query optimization and index advice

    ‏2013-08-28T21:02:41Z  

    Ever since SQE was first introduced on V5R2, there have been situations where SQE needs a different index to provide the best performance because SQE query optimizer is different that CQE query optimizer.  Often the CQE query optimizer was getting lucky with a suboptimal indexing strategy.

    I wouldn't expect the Index Advisor to necessarily provide advice in this situation. The Index Advisor is a tool to help identifying indexes, but it doesn't guarantee to flag all missing indexes.  However, it has limitations like all software tools. 

  • jonesy_burt
    jonesy_burt
    26 Posts

    Re: V5R4 upgrade to 7.1 - query optimization and index advice

    ‏2013-08-29T09:27:10Z  

    Ever since SQE was first introduced on V5R2, there have been situations where SQE needs a different index to provide the best performance because SQE query optimizer is different that CQE query optimizer.  Often the CQE query optimizer was getting lucky with a suboptimal indexing strategy.

    I wouldn't expect the Index Advisor to necessarily provide advice in this situation. The Index Advisor is a tool to help identifying indexes, but it doesn't guarantee to flag all missing indexes.  However, it has limitations like all software tools. 

    I don't understand:

    Why doesn't SQE have the capability to produce the same access plan as the original CQE plan? If CQE can produce it, surely the better SQE should be able to produce it even if it is sub-optimal. The definition of sub-optimal is moot however because the 5.4 query was completing in seconds; the 7.1 query in tens of minutes and that's all that matters to the customer, not that the indexes were sub-optimal.

    In this particular situation it would be beneficial to be able to 'migrate' the 5.4 Access Plan to the 7.1 system SQE Plan Cache so that the SQE could replicate it. Is this something that IBM could produce? This would be really helpful.

    I appreciate the Index Advisor is advice and not hard and fast rules. I suppose that the recommended approach to the creation and use of indexes is based just as much upon knowledge of SQL and the database as using the Index Advice tool. THe IBM i appears to need a DBA like other systems.

    It does, however, seem strange that when a customer is upgrading from 5.4 to 7.1 or migrating to better hardware and moving to 7.1 that their database may not actually perform as well without some remedial work. Would it make sense to have a section in the 'Memo to Users' to advise customers of this possibility?

  • krmilligan
    krmilligan
    450 Posts

    Re: V5R4 upgrade to 7.1 - query optimization and index advice

    ‏2013-08-29T14:11:56Z  

    I don't understand:

    Why doesn't SQE have the capability to produce the same access plan as the original CQE plan? If CQE can produce it, surely the better SQE should be able to produce it even if it is sub-optimal. The definition of sub-optimal is moot however because the 5.4 query was completing in seconds; the 7.1 query in tens of minutes and that's all that matters to the customer, not that the indexes were sub-optimal.

    In this particular situation it would be beneficial to be able to 'migrate' the 5.4 Access Plan to the 7.1 system SQE Plan Cache so that the SQE could replicate it. Is this something that IBM could produce? This would be really helpful.

    I appreciate the Index Advisor is advice and not hard and fast rules. I suppose that the recommended approach to the creation and use of indexes is based just as much upon knowledge of SQL and the database as using the Index Advice tool. THe IBM i appears to need a DBA like other systems.

    It does, however, seem strange that when a customer is upgrading from 5.4 to 7.1 or migrating to better hardware and moving to 7.1 that their database may not actually perform as well without some remedial work. Would it make sense to have a section in the 'Memo to Users' to advise customers of this possibility?

    If SQE just produced the same access plans as CQE, then customers would never realize any of the performance benefits of SQE.  There have been numerous customer examples of SQE running SQL queries orders of magnitude faster than CQE.

    Since SQE was introduced in V5R2, there's been numerous articles, papers, and a Redbook that discuss the importance of a good indexing strategy to facilitate a smooth transition from CQE to SQE. 

    For IBM i clients that run heavy SQL workloads, we do recommend a Database Analyst or Database Engineer that's responsible for indexing strategy.  This is NOT the same DBA role as on other systems - they have to do many more low-level configuration tasks and monitoring.  The DB2 for i SQL Performance workshop (http://ibm.com/systems/i/db2/db2performance.html) is in place to help IBM i clients develop SQL performance tuning skills.

  • jonesy_burt
    jonesy_burt
    26 Posts

    Re: V5R4 upgrade to 7.1 - query optimization and index advice

    ‏2013-08-29T15:08:25Z  

    If SQE just produced the same access plans as CQE, then customers would never realize any of the performance benefits of SQE.  There have been numerous customer examples of SQE running SQL queries orders of magnitude faster than CQE.

    Since SQE was introduced in V5R2, there's been numerous articles, papers, and a Redbook that discuss the importance of a good indexing strategy to facilitate a smooth transition from CQE to SQE. 

    For IBM i clients that run heavy SQL workloads, we do recommend a Database Analyst or Database Engineer that's responsible for indexing strategy.  This is NOT the same DBA role as on other systems - they have to do many more low-level configuration tasks and monitoring.  The DB2 for i SQL Performance workshop (http://ibm.com/systems/i/db2/db2performance.html) is in place to help IBM i clients develop SQL performance tuning skills.

    I'm not suggesting that the SQE produce just the same plans as the CQE. A completely different plan is okay if it matches or betters the CQE plan. If it can't, and also can't reproduce the CQE plan doesn't that suggest that there is a problem somewhere?

    That's what I'm trying to establish - is this situation a bug/problem or a lack of understanding and administration.

    In this particular instance the advised index (advised by IBM, not the Index Advisor) appears to be a workaround as we are waiting for further information.

     

    Updated on 2013-08-29T15:24:24Z at 2013-08-29T15:24:24Z by jonesy_burt
  • krmilligan
    krmilligan
    450 Posts

    Re: V5R4 upgrade to 7.1 - query optimization and index advice

    ‏2013-08-29T15:54:53Z  

    I'm not suggesting that the SQE produce just the same plans as the CQE. A completely different plan is okay if it matches or betters the CQE plan. If it can't, and also can't reproduce the CQE plan doesn't that suggest that there is a problem somewhere?

    That's what I'm trying to establish - is this situation a bug/problem or a lack of understanding and administration.

    In this particular instance the advised index (advised by IBM, not the Index Advisor) appears to be a workaround as we are waiting for further information.

     

    SQE has no knowledge of CQE techniques, so it's goal is produce the best performing plan.  However, query optimizers is just a piece of software that can have defects.

    I don' t know the details of the problem that was reported to IBM, so it's hard to say.  If IBM Support suggested the index is a workaround, then it's possible that they are investigating a fix that improves performance based on the existing set of indexes.

  • jonesy_burt
    jonesy_burt
    26 Posts

    Re: V5R4 upgrade to 7.1 - query optimization and index advice

    ‏2013-08-29T16:09:59Z  

    SQE has no knowledge of CQE techniques, so it's goal is produce the best performing plan.  However, query optimizers is just a piece of software that can have defects.

    I don' t know the details of the problem that was reported to IBM, so it's hard to say.  If IBM Support suggested the index is a workaround, then it's possible that they are investigating a fix that improves performance based on the existing set of indexes.

    Yes there could be something that drops out of the support call. I'll report it here if there is anything that may be of interest.

    Thanks for all the time you've spent on this - much appreciated.