ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

hunting the performance pig with Visual Explain

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • hunting the performance pig with Visual Explain

    OK I got this Visual Explain thing runnnig
    and removed all logicals in my test environment.

    Now I want to know what part of my SQL is the performance pig and what keys should I be indexing.
    Attached Files

  • #2
    Re: hunting the performance pig with Visual Explain

    Why remove all logicals ? Logicals can be used for selection and sequence ( some times)

    From the Visual Explain screen, click Actions / Advisor and see what indexes it suggests.

    Also check for file QUSRSYS/QAQQINI file. Make sure this file contains a line IGNORED_DERIVED_INDEX set to *YES If the file doesn't exist, use CRTDUPOBJ to copy it from QSYS with DATA=*YES. Then modify or add this line.

    Comment


    • #3
      Re: hunting the performance pig with Visual Explain

      I removed logicals and indexes to try to get Visual Explain to tell me detailed exact key structure (eg: CPY/DIV/DAT/PER/WH/LOC/SEQ/) I should be using, not tell me that existing logical CPY/DIV/DAT is good enough. When I check job log, I see that program often uses “next best” logical.

      PHP Code:
      ALLOW_TEMPORARY_INDEXES                 
      APPLY_REMOTE                            
      ASYNC_JOB_USAGE                         
      CACHE_RESULTS                           
      COMMITMENT_CONTROL_LOCK_LIMIT           
      FORCE_JOIN_ORDER                        
      IGNORE_DERIVED_INDEX                    
      IGNORE_LIKE_REDUNDANT_SHIFTS            
      LIMIT_PREDICATE_OPTIMIZATION            
      LOB_LOCATOR_THRESHOLD                   
      MATERIALIZED_QUERY_TABLE_REFRESH_AGE    
      MATERIALIZED_QUERY_TABLE_USAGE 
      If no value is specifed, does this mean value is turned on?

      Comment


      • #4
        Re: hunting the performance pig with Visual Explain

        Not sure in job log. But some have "real" values, no just yes/no. I would check the QUSRSYS/QAQQINI file itself.

        The value I mentioned earlier IGNORE_DERIVED_INDEX can cause the "next best" to be used. If the "best index" is a derived one, then this value tells it to ignore that index (since the new SQL engine can't use it). Otherwise, the SQE engine sends the query back to the CQE for processing (since the older CQE engine CAN handle it). There are 2 problems with this (1) The overhead or analyzing, resubmitting, re-analyzing, etc is often more than what would be saved. (2) If the file has ANY derived index at all, the SQE wouldn't run it. Even LF's using fields you don't care about cause this to happen.

        That said, however, the VE you posted doesn't look very complicated, and has relatively few records. Index scans and probes are usually fast. Is this a long running query ? Did the adviser suggest and indexes ?

        Comment


        • #5
          Re: hunting the performance pig with Visual Explain

          QUSRSYS/QAQQINI is in QSYS for some reason or maybe I can’t see the one in USRSYS. I’ll check.

          Interactive performance is pretty good in test environment but I’m waiting for screams when it hits production with 10 million header files.

          Will throwing indexes at the problem necessarily solve performance issues? At a certain point will too many joins on big files start slowing down the system?

          By the way your advice was dead on. I was close but still lost in the forest.

          Comment


          • #6
            Re: hunting the performance pig with Visual Explain

            You need to run CRTDUPOBJ to create a copy in QUSRSYS. Then modify the QUSRSYS version.

            Indexes will help, but aren't everything. There are a number of variables (joined files, file sizes, avail processor, avail memory, etc). Large queries run infrequently (like once a day), but read often during the day may benefit from being built as MQT's.

            Also, newer releases and current *DB2GRP PTF's can have a big impact.

            Comment


            • #7
              Re: hunting the performance pig with Visual Explain

              There are many cool tools/perspectives here.
              I can see the parms passed into my procedures and which SQL statements were actually run.

              One thing I haven't seen is a view of the SQL run with actual paramater
              values used in the SQL query. With this I can ensure that the right values
              are getting in. Also I could cut and paste the code to test it in STRSQL or Frog.
              Attached Files

              Comment


              • #8
                Re: hunting the performance pig with Visual Explain

                Have you determined how efficient the index advisor is?
                Does it simply report on indexes missing on join and
                where clauses?

                Comment


                • #9
                  Re: hunting the performance pig with Visual Explain

                  It varies. V5R4 is MUCH better than V5R3. However, anytime an index would improve performance, it will be suggested. Sometimes that improvement is minimal.
                  You especially want to look for places where temporary indexes are built. Also, look first to files with larger numbers of records. The system might actually use an index to determine it doesn't want an index. For example, you request all company 02 records selected. The system doesn't know without an index that 75% of the file is company 02. With an index, it may determine this and decide to use a table scan instead (a table scan is faster in this case). So it uses the index for analysis, but not to read the data.
                  I don't build everything suggested, but I consider every suggestion - especially when EVI's are suggested.

                  Comment

                  Working...
                  X