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

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
    445 Posts
    ACCEPTED ANSWER

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

    ‏2013-08-28T21:02:41Z  in response to jonesy_burt

    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
      ACCEPTED ANSWER

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

      ‏2013-08-29T09:27:10Z  in response to krmilligan

      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
        445 Posts
        ACCEPTED ANSWER

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

        ‏2013-08-29T14:11:56Z  in response to jonesy_burt

        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
          ACCEPTED ANSWER

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

          ‏2013-08-29T15:08:25Z  in response to krmilligan

          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
            445 Posts
            ACCEPTED ANSWER

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

            ‏2013-08-29T15:54:53Z  in response to jonesy_burt

            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
              ACCEPTED ANSWER

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

              ‏2013-08-29T16:09:59Z  in response to krmilligan

              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.