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.
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.
Click Parameter, then click MENU-Goto-Variant to check the variant this job is using. See next screen->
Here is the program name.
Here you can see all the parameters for the job.
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.
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.
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.
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”
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:
Check Trace Result
Now click the “ABAP trace” button, below screen will show up:
Please check next screen for detail explanation.
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.
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”.
Check Execution Plan
Here you can see the execution plan for this SQL statement:
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.
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.
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
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.
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.
Check with STAD
If can’t reproduce the issue again. We can check the status during job running time via STAD.
You can check the result for “Database request time”.
- SAP Managed Tags:
6 Comments
