Skip to Content
Have you seen the John Cusack movie High Fidelity?  (or even better, read Nick Hornby’s book)  Like his character in the movie, I’m a fan of Top 5 lists.  Top 5 Dream Jobs; Top 5 Artists; Top 5 Side 1, Track 1 Songs; Top 5 Most Painful SQL Statements…  OK, maybe that last one wasn’t in the movie, but if he had been a DBA or Basis Administrator, it would surely have made it onto the DVD release.

If you are trying to improve database performance for your SAP system, the thing that has been most successful for me over the years is to maintain a running Top 5 list, and always be looking at what you can knock off that list.  Since I’m running ERP 6.0 EhP4 on a DB2 LUW 9.1 platform, I’ll show examples there for how to start building such a list by using the DBA Cockpit.  Very similar procedures exist for all other databases.
Open transaction ST04, and click on Performance (probably already expanded) and double-click on SQL Cache.  You can leave in the default selection criteria, and just click on the green check mark.  This is going to show you the SQL statements that have been executing in your database, and a little something about them.  (Note that there are multiple ways to get to each of these items, I’m only showing one typical way)
By default, this view is sorted by Total Execution Time, which is really not very helpful.  Many times it doesn’t matter how much overall time a SQL statement has spent executing, what matters is how much time it spends each time it executes.  So I typically sort descending by Avg Execution Time.  What I look for are the Top 5 worst offenders – these are the SQL statements that are using the most time, and thus the most resources, each time they execute.  These are what I’d like to address.
 
 
In this example, I see that the first two SQL statements listed have average execution times of over 100 seconds each.  These, however, have only been executed a few hundred times each (since the last restart).  So, I’d like to look at the next one.  It has a slightly lower average execution time at only 79 seconds, but it has been executed almost 7,500 times since startup, for a total of almost 590,000 seconds (>163 hours) of database time.  So, if I highlight that SQL statement and click on EXPLAIN, that will tell me how the SQL statement is executing.
 
 
 
As you can see, this SQL statement is doing an Index Scan of the primary key index, BKPF~0.  However, the details for the explain plan show me that that index scan is only using the MANDT column for range delimiting, and of course all records for this index will contain the same value for MANDT.  The index scan then uses BELNR as a sargable predicate, which basically means that the query will scan for all rows that match the MANDT value, then filter out those which don’t match BELNR, as opposed to searching the index for BELNR itself.
So, what can we do?  Well, we have this nifty tool called the Index Advisor.  So, going back to the previous screenshot, highlight the SQL statement, then click on the Index Advisor button, and then click the button for “Recommend Indexes.”  What you will see is something like this:
 
 
 
This has now come up with a suggest for a new index for us.  It is a non-unique index based on 14 columns (wow!), of which you can see the first two are BELNR and MANDT.  The nice thing about the Index Advisor is we can also now see what effect that would have on our query.  To do this, we click down below on the EXPLAIN button, making sure that recommended indexes are evaluated.
 
 
Now it chugs for a minute, then comes up with a new explain plan, based on this new simulated index:
 
 
And we can see that it has chosen to use the new index, it doesn’t need to go to the table at all because all of the selected column data is in the index, and, according to the explain plan, our cost has been reduced considerably.  Although this does not directly predict the affect it will have on the runtime of this particular query, it does give an idea of the improvement, which can be validated with testing.
There are other considerations of course, for example you want to know ahead of time how many indexes a table already has, as each new index will, to some degree, slow down insert, update and delete operations.  And, as always, the recommendations from the Index Advisor should be evaluated and tested by experienced database professionals.  But, at least this gives you a starting point to evaluate and target database performance issues.
I can tell from past experience that this can make a large impact.  I had one personal experience where adding 2 indexes at once reduced overall database reads by 50%.  While these results are not typical (your mileage may vary, as they say), tuning is a continual process and is virtually guaranteed to net positive results, if done in a logical, methodical manner.
So get started on your Top 5 list today!
 
 
To report this post you need to login first.

21 Comments

You must be Logged on to comment or reply to a post.

  1. Graham Robinson
    Dear “DB Performance Tuning Blog”,

    I am finding you strangely attractive in this light.

    I don’t think it’s what I have been drinking – there is something about you that makes you stand out.

    Could it be that you are informative, concise, valuable and practical?

    So many of the other blogs I have seen lately seem shallow campared to you.

    You remind me of the blogs I used to know in my youth.

    I hope we can meet up again sometime?

    Cheers
    Graham Robbo

    (0) 
  2. Susan Keohan
    Top of the cheesiest movie tag lines, but I can only echo Graham’s sentiments.  Where have you been all my life?

    Great stuff.
    Sue

    (0) 
  3. Lars Breddemann
    First of all: not too bad this blog, really!

    Anyhow, I’m a bit surprised for several reasons:

    1) Maybe this is a DB2/4/6 thing, but the habit of checking the database for statements that make up for the larger parts of the database total workload and tune these is a standard action for Oracle/MaxDB/SQL Server systems.

    That said, I wonder how the other commentators could be so overwhelming.

    2) Although this tuning strategy is often followed it should be clear that it’s very easy to tune the ‘wrong’ queries here.
    ‘Wrong’ are the queries that have only neglect-able impact on the total systems response time and those where the tuning effort is larger than the benefit of the tuning (how much do you cost per hour? how much money does the tuning save your company?)

    3) Adding suitable indexes often is a proper way to improve query performance.
    However, if the query belongs to a standard SAP program it may be better to have a note implemented instead.

    Another point is: often enough an index could help, but would only be the workaround to a ABAP coding bug (e.g. not properly joining two tables)
    or ‘difficult’ selection criteria (e.g. looking for non-existing values or providing negative selection criteria).

    Therefore, before starting any “index advisers” or “tuning wizards” on every statement you find it might be worthwhile to take a step back and think about why this problem exists at all.

    And, yes, please: continue blogging about DB tuning!

    (0) 
    1. Community User Post author
      Thanks, Lars.  Fair points, all.  I am actually working on another blog to give some more insight… there is only so much information you can fit into a blog, after all.

      I think the safest thing to say is, there is not one rule that will fit in all situations.  Poor performance might be due to poor coding, as you point out.  And that poor coding may come from SAP, or from your own in-house developer.  Either source brings a potential difference in resolution – find and apply a note, open a customer message, create a ticket in your in-house system, fix it yourself, etc.

      Or maybe the problem is not due to code, but rather due to a database issue.  If there is a locking issue that causes your runtimes to be much higher, or if disk contention is the culprit, these are things an index will likely not help.

      Since tuning is an ongoing journey, not a goal to be reached, you continue to work at it constantly over time. And this is one tool to keep in your toolbox to decide which is the right choice to make.

      Ultimately, this blog was not about the Index Advisor specifically, but rather about the strategy to maintain a Top 5 list.  If you are always aware of what your worst offenders are, in any given area – not just SQL statements, then you know what is causing your system (and, possibly as a result, your customers) the most pain, then you know what you can do to make the most positive impact.

      Cheers,
      David.

      (0) 
  4. Hermann Gahm
    Hi David,

    well done. Nice presentation of the topic.

    Regarding the sorting of the SQL cache i have a few comments. Looking at those queries with the highest total time is very important as well. Last week i had a query with ~200 microseconds response time (almost all of it CPU time) per execution that was executed so often that it was one of the most expensive statements in the database regarding CPU time. We managed reduce the run time of that query to ~30 microseconds which lead to a tremendous release of database CPU time. If you are interested you can read about it here:

    http://forums.sdn.sap.com/thread.jspa?threadID=1695406&start=0&tstart=15

    So i think total time is important as well as single execution time or rows read or rows changed per execution there is different criteria.
    So more than one top 5 lists by different columns would be the best i think.

    Regarding Wizards they tend to recomend optimal indexes. Most of the time “fat indexes” where no table access is required like in your example. This leads to the best performance for the analyzed query. However such “fat indexes” are not practical if we have many of them. I only create them in exceptional cases (where the application is very important). The automatic wizards unfortunatelly don’t come up with index recommendations that are “good enough” which is more practical i think. And analyzing the application(s) is a different story like Lars already said.

    Kind regards,

    Hermann

    (0) 
    1. Community User Post author
      Thanks, Hermann.  I must admit, I am quite surprised by the forum post you have linked here.  I would like to understand it better, but I will post there, instead of here, with my questions.

      My general philosophy is to not even bother with such queries as the ones you have mentioned, because gains measured in the microseconds (even when executed millions of times) are very small.  This is the reason why I recommend looking at the biggest offenders in terms of time per execution.  I suppose any gain is good, but it is nice if it is noticeable by the end-user or program executing the code, and in such cases as sub-millisecond improvements, I would expect that it is not.

      I do agree with you that wizards don’t always come up with optimal recommendations, which is why I stipulated that they should be evaluated by a seasoned DBA.

      (0) 
      1. Hermann Gahm
        Hi David,

        thanks for your interesting questions. My colleagues in the database CoEs normally start with the total times, then proceed with total ressource consumers (there is overlap of course) and as well evalute the per execution times. For sure it depends on the situation. The per execution times could be very interesting as well if they run in peak times or are part of interesting programs. If they run in non-peak times and are part of not important programs i usually don’t care for high times per execution (if they don’t contribute) significantly to the system load.

        Yes i agree that doublechecking the wizards recommendation by an experienced DBA is important.

        For your case i would like to know if it was standard SAP or customer code? In most cases i know the company code (BUKRS) the field that was missing in your example could be added to the query without a problem. Then, the exisiting indexes would have been good enough i think (not optimal, like the one the wizzard suggested).

        Kind regards,

        Hermann

        (0) 
        1. Community User Post author
          Hi Hermann,

          My apologies for the delay in responding.  The case I presented was not standard SAP code, it was actually generated code from Business Objects.  In such a case, similar in nature to standard SAP code, modifying the ABAP code is not always possible, so I prefer to look at other (easier to implement) methods of tuning, such as adding an index, if possible.

          Cheers,
          David.

          (0) 
          1. Hermann Gahm
            Hi David,

            i agreee: in such a case (generated code from BO) it might not be easy to fix it on source code level. It could be that it depends on a user input. Otherwise one have to find the right people with application knowledge and prepare tests and so on and so on.

            Kind regards,

            Hermann

            (0) 
  5. Hermann Gahm
    Hi David,

    reading it the 2nd time i notices 2 things.

    The estimated cost has been redueced much
    mord, hasn’t it?
    From: 5,070E+06 (5070000,00)
    To: 2,646E+01 (26,46)

    The other thing is the #key columns 0 thing. Is this because we have a covering index or (just) a bug or related to the index advisor? It looks strange, doesn’t it?

    Kind regards,

    Hermann

    (0) 
    1. Community User Post author
      Yes, you are right!  I noticed that as well, and thought I had corrected that in the blog, but for some reason it doesn’t look like the correction saved.  I’ll fix that now.

      Thanks for catching that!  🙂

      (0) 
    2. Community User Post author
      Hermann, just a follow-up.  Sorry I replied too quickly to your post earlier, and didn’t read the last question.

      The #key columns is 1 for the first explain. This means that the query is really only using 1 column (mandt), and the other column is used only as a sargable predicate.

      As for why #key columns is 0 for the second explain, I can only guess that it is due to the fact that the index is not actually used, as it does not truly exist. The reason I am guessing this is that every time I perform an explain plan on a virtual index recommended by the index advisor, I get the same result: “#key columns: 0”. And every time I perform an explain plan on a real index, I get some number greater than 0.

      Again, that is just a guess at this time.

      Hope that helps!

      Cheers,
      David.

      (0) 
  6. Koteswar Rao Arvapally
    Hi David,
    In High performance intensive project like ours, though we are ABAPers ,we do the activity you mentioned almost on daily basis.

    This blog gave us some useful hints using ST04 more efficiently.

    Regards,
    Koteswar

    (0) 
  7. Ananda Swaroop
    Hello David,

    Screen shots have been deleted from your blog.

    Kindly upload them.

    Your blog has helped me in analyzing performance issues in our BW system.

    Thanks & Regards,

    (0) 
    1. Community User Post author
      Hi Ananda, the screenshots are still visible to me. They are coming from photobucket. Perhaps you have an issue preventing you from seeing images from photobucket?

      Regards,
      David.

      (0) 
        1. Community User Post author
          That’s a great idea! And I’ve done some searching and found the ‘Upload Image’ option, which I hadn’t previously been aware of.

          I will go back and modify, thanks for the suggestion, Marilyn!  🙂

          (0) 
    2. Community User Post author
      Ananda, all of the images have been posted on sdn and the blog has been updated.  Please let me know whether you have any issues with them now.

      Cheers,
      David.

      (0) 

Leave a Reply