Performance Problem Guide Tips and Tricks

Purpose

Performance problem always happen during CO period end closing.

It’s always not clear what to do when we meet performance problem.

This blog will guide you what to do with detailed steps.

Initial Analysis

Necessary Information

After you get a incident of performance issue, first you need to know below information:

–What is the program/T-code used?

–What’s the status of the job? Is it still running? How long have it been running? What’s the normal finish time?

–Have this issue happened before? If yes, how was it solved?

–Is there any DB update, Kernel update, support package update recently?

–Is the performance problem for this job only or system level?

Check Job with SM37

First of all, of course, you need to log on to the system.

Step1. T-code: SM37 to check the job

The easy way to find the job is to use Job name.

You can also select the job according to status.

Sometimes the job names are identical, we need to find the exact job by select the start condition.

1.PNG

Step1. T-code: SM37 to check the job

The Duration time is the most important figure in this page.

Click this button to show the log. You can check whether there are any error messages.

2.PNG

Click Parameter, then click MENU-Goto-Variant to check the variant this job is using. See next screen->

Here is the program name.

3.PNG

Here you can see all the parameters for the job.

4.PNG

Background Trace with ST12

Background trace with ST12

If the performance issue is reproducible, this will be the best. Since we can start a ST12 trace for the job. The performance data collected will be more accurate and easy to analyze.

You need to make settings for ST12 first.

5.PNG

Check current system time

Then check the current time for the system (sometimes the system you are using is with different time zone)

Click MENU-System-Status..

Here is the current system time.

6.PNG

Schedule the job

Open a new session, and go to the T-code you want to trace and schedule the job.

Remember to use background processing.

Set the execution time. (According to current system time)

Click Confirm.

7.PNG

Schedule ST12 trace

Go back to ST12

Click MENU-Utilities-Schedule trace-For Batchjob or workprocess

Input the job name in the last page.

If the job name is not identical, you can use “user name” and program name to

We need to set the “From” time according to the job start time. “To” time, set it to one hour later.

Change Interval to 10 sec

Input a meaningful Comment

At last, press “Schedule trace”

8.PNG

Schedule ST12 trace

If the trace is successfully collected, you should be able to see below status in the trace list:

Go back to the initial screen of ST12, on the bottom of the page, you should be able to select and check the trace result:

9.PNG

Check Trace Result

Now click the “ABAP trace” button, below screen will show up:

Please check next screen for detail explanation.

10.PNG

Advanced Analysis

Check Trace Result

First, please check the time percentage bar.

–If ABAP takes the most time, it should be program problem, which should be analyzed by the team that owns the program. But for performance issues which take long time, 90% of the issues are caused by DB.

–So most of the time Database will take the most time. Normally Database will show above 75% of the total time. In fact, if you get this result.

If you want to continue the analysis:

–First you can sort descending by Net time, then you can see the first line shows the Call which takes the most time. Normally this should be the cause for the performance problem.

–As you can see in the example, the time accessing table KEKO takes the most time. So first you can try to search Notes with key words “Program Name” + “Table Name” +”Performance”.

–You can click “DB->” to check more detail.

11.PNG

Check Execution Plan

You can see here are the list of relevant statements accessing table KEKO.

You can select the most expensive one and click button “Explain”.

12.PNG

Check Execution Plan

Here you can see the execution plan for this SQL statement:

13.PNG

Index

First we need to understand what is Index:

–When you look up businesses in a Yellow Pages telephone book, you probably never consider reading the entire telephone book from cover to cover. You more likely open the book somewhere in the middle and zero in on the required business by searching back and forth a few times, using the fact that the names are sorted alphabetically.

–One way for the database to deal with an unsorted table is by using a time-consuming sequential read- record by record. To save the database from having to perform a sequential read for every SQL statement query, each database table has a corresponding primary index.

Then we need to understand the difference between Primary Index and Secondary Index:

–We can consider the key fields combination of a table is the Primary Index. You can check with SE11 for each table to find it’s key fields.

–A primary index is always unique; that is, for each combination of index fields, there is only one table entry.

–Sometimes the Primary Index is not sufficient. We need to define a Secondary Index that contains the fields we need. You can also check Secondary Index with SE11 by clicking button “Indexes…”.

–For example, Material Number is not a key field of table KEKO. If we want to search by Material Number, we can define a Secondary Index on the field Material Number. We can check in SE11, KEKO~A is the Secondary Index we talked about.

14.PNG

Root Cause

We have known how to check the execution plan and the use of indexes. Now let’s discuss the causes of performance issue.

Basically there are 2 kinds of issues:

–Index is missing.

–Wrong Index is used.

(Testing can be performed via ST05 – Enter SQL Statement)

•Index is missing.

•For example, when search KEKO with ZIFFR, since it’s not in Primary Index and no Secondary Index created, system need to make full table scan.

•If the field ZIFFR contains a lot of unique values, this full table scan will be very time consuming.

15.PNG

Solution: Define new Secondary Index for the table.

Wrong Index is used.

–Sometimes, although the correct index exists, due to some reasons, the optimizer still chooses the wrong index.

oThis might be cause by obsolete table statistics. When there are changes to the table indexes, table statistics need to be updated manually.

oWith some database (e.g. MSSQLServer), the execution plan doesn’t change per each call, sometimes it follows the last call’s execution plan. So there the wrong index might be used.

Solutions:

–Update table statistics.

–Create DB hints (Reference Note 2006946)

How to update table statistics

T-code: DB02

16.PNG

17.PNG

When Job is still running or not reproducible

Job is still running.

Analyze with SM50

–Normally issue happens on one or two tables, when you keep refresh the screen, the table remains the same. Then you should find out the statement and check the execution plan.

–Via MENU-Administration-Program-Debugging, you can start debugging and find out the statement which is currently being read.

18.PNG

Trace with ST12

If the job is running for a long time and is still running. You can do ST12 trace for the job for a period like we did in part I.

You can get the job details from SM37.

19.PNG

Check with STAD

If can’t reproduce the issue again. We can check the status during job running time via STAD.

20.PNG

You can check the result for “Database request time”.

21.PNG

To report this post you need to login first.

4 Comments

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

  1. Owen Liu Post author

    Welcome your comments.

    If you like it, please rate this blog or click “Like”.

    You involvement is my best motivity to create new resources.

    Thank you all!

    (0) 
    1. Surya Varma

      Dear Owen Liu,

      This indeed is really a very good document. I wish i had it a little earlier (4 months before 🙂 ), where a change (COPA Derivation Rule) that was done by me has caused a devastating performance problem during COPA TDD. I had no clue as to how to analyze the reason. I felt like standing in front of a firing squad who had all their guns blazing at me. Thanks to my Team Lead and our Basis team, who helped to salvage the situation for  me that day.

      Thanks to your document, god forbid but if i am in such a situation, i will be able to put up a better and a brave face.

      Thank you once again

      Best Regards

      Surya Varma

      (0) 
      1. Owen Liu Post author

        Dear Surya Varma,

        Thank you for your reply! I also felt the same before when I meet performance issue. As you know, we sometimes have performance issue especially during period end closing of Controlling. Normally, the codings are stable, so most of the time it’s DB relevant issue. I always need to check with Basis guys for the same. But after I have some studies from them, I feel it’s not that difficult to make primary analysis. At least, I can solve many performance issues with this blog on my own.

        I’m glad this blog can help you.

        Cheers,

        Owen

        (0) 

Leave a Reply