Skip to Content

PURPOSE OF THIS DOCUMENT
STEPS FOR PROCESSING INCIDENTS
1) Gather general information
2) Reproducible issues with a specific list of steps
2.1 Take traces ( or let the customer take traces)
2.2 Check if you find a solution

2.2.1 Check ABAP trace

2.2.2 Check SQL trace and related transactions

2.2.3 Hanging programs. Check SM50

EASY EXAMPLE ( REPRODUCIBLE ISSUE)
Issue description
Take traces with ST12
Check ABAP trace

Sort by net time

Sort by gross time

SQL Trace

Initial screen of SQL trace

Summarize by SQL Statement

Check detail of SQL statement with parameter values

Check calling program for SQL Statement

Check Execution plan of SQL Statement

Check existing indexes in relevant table in SE11

Possible recommendation for our example

Change query of our example: Execution plan where index is used

Example with hanging program and SM50 check

Take ST12 traces and stop before program finishes

Repeat execution and check SM50 while the program is running

You have a performance problem and before creating a SAP Support incident, you would like to analyse the issue and see if you can find a solution or a related SAP note by yourself.

You can follow the recommendations in this page in order to find a solution or at least send us enough information to speed up incident processing. The guidelines given here are related to performance in ABAP based systems. The information is structured in two parts:

  • First part,STEPS TO FOLLOW contains a guideline with the list of steps to perform. Some of these tasks are not explained in detail, but they contain links to the second part, which has more detailed information including screenshots.
  • The second part, EASY EXAMPLE ( REPRODUCIBLE ISSUE) contains a sample case of a performance issue, quite simple, and screenshots of the analysis using ST12 that you can use to check and provide SQL trace information. Some small variations in the ABAP coding of the example are also used to explain different cases (table indexes)
STEPS TO FOLLOW

–> Go TOP

1) Gather general information
  • Does the performance issue affect many processes in the system – bad performance in general – or is it only one process with certain steps?
  • Does the performance issue always occur, or it happens only at certain times of the day ( e.g., during peak load , with high number of users in the system)
  • Does the problem affect all users, or only a few of them? If it is only a few users, ask which users
  • Does the problem affect only one application server or all the application servers?

Cases where the issue is not always reproducible with a specific list of steps, would be complex to analyse and exceed the scope of the guidelines given here. In case you have such a performance issue and need help from SAP, you can create an incident in component SV-PERF or any of its related subcomponents (SV-PERF*).

If the problem affects a single process and can be always reproduced with a specific list of steps, then go to the next point (Reproducible issues with a specific list of steps)

2) Reproducible issues with a specific list of steps

–> Go TOP

2.1 Take traces

At this point, we normally would have a problem that is reproducible anytime for a particular sequence of steps.

When we analyse these kind of issues in a customer incident, we usually take a trace with ST12 transaction , which has the advantage that it contains both ABAP trace and SQL trace and can be used for HTTP requests as well.

In a similar way, you can take the ST12 traces. Public information about ST12 trace can be found in: http://scn.sap.com/docs/DOC-44143

See also in our example,Take traces with ST12.

Also, KBA 2436955 – Step by step instructions on how to use ST12 trace for analysis

The following point Check if you can find a solution” will give you some hints to find a solution to the issue from the information in the ST12 trace.

In case you cannot get to a solution to the problem and you need to create a SAP incident, the trace you have taken can be useful for us. You can add in the incident the data to identify your trace in ST12 – user, trace time and comment -, so that we can find the trace in the system and analyse it.

NOTE: The ST12 transaction for customer systems is delivered using the ST-A/PI add-on (see related. SAP Note 69455). If your system does not have the possibility to take an ST12 trace (ST-A/PI Add-on not installed yet) then you can take the following traces instead:

  1. ABAP trace with transaction SAT
  2. SQL trace with transaction ST05
2.2 Check if you can find a solution
2.2.1 Check ABAP trace

–> Go TOP
Check function modules or programs where most time is spent

Possible options to find a solution from the ABAP trace information

  • If most time is spent in “Z” programs or in a standard program that has been called by a Z program. Then these “Z” programs should be checked by your developers. (Using sorting by both columns net and gross can be useful to give you a quick overview)
  • If most time is spent in a standard program (not called by a Z program). -> Search notes by program/ function module…

For more details, see chapter in our example Check ABAP trace

2.2.2 Check SQL trace and related transactions

Important checks

1) Check the SQL trace

  • Normally the option “Trace –structure identical statements” (option “Summarize trace by SQL statement in old systems) is the best to start our analysis, because it shows in one single row all the calls with the same query (query parameters may change) and it sorts all the rows by time. See more details in our example in chapter Summarize by SQL Statement.
  • Check the complete SQL query ( parameter values used) See more details in our example in chapter Check detail of SQL statement with parameter values
  • Check the execution plan ( button “Display execution plan”) to see the indexes used in the query and whether the index statistics are recent See more details in our examples in chapters
    Check Execution plan of SQL Statement
    Change query of our example: Execution plan where index is used
  • Check the program where the most expensive query is located ( button “Display ABAP location” See more details in our example in chapter Check calling program for SQL Statement

2) Check in SE16 the number or records in the involved table(s)

3) Check the indexes of the involved table(s) in SE11

SE11 -> Table name

  • Primary key index can be see directly in first screen ( fields where checkbox “Key” is marked)
  • For other indexes, use button “Indexes” and select each of the indexes to see their fields ( For an index to be available, it must be “Active” and it must show message in its details like Index <Index name> exists in database system ORACLE

See more details in our example in chapter Check existing indexes in relevant table in SE11
Possible options to find a solution from the SQL trace information

Considering all the previous checks, you can use these options (one or several):

  • Search for notes in the programs where the expensive queries where launched
  • Search for notes that could recommend an extra database index in the table(s)
  • If there are many records in the involved table(s) and archiving solution is possible for that kind of data, you should evaluate if this is feasible considering your business requirements.
  • In case of tables related e.g. to log entries, it could be even possible to delete entries
  • Update of index statistics
  • Creation of an extra index (not standard), even in a standard table. It can happen that the users in your system choose search criteria that are not typical or you have a very high number of entries in the table, so an index that has not been created in the standard could be very useful for your system.
  • You should think it carefully before creating a new index, as this measure has some risks, for example:
    • Change operations to tables take longer because the index must be changed accordingly
    • Having too many indexes increases the risk that the optimizer chooses the wrong index.
  • Nevertheless, in some cases, it is the only possible solution to the problem and performance can improve dramatically after creation of the index. In production systems, the creation of index must be done when there is less activity in the system
2.2.3 Hanging programs. Check SM50

–> Go TOP
In case of programs that take too long to be executed or seem to be hanging, you might need to stop the traces before the control returns to the user (still seeing the hourglass) in the traced program.

In these cases, the results in the traces might not be always reliable. For example, we noticed in a program test that the ABAP trace was missing but the SQL trace was available, but both traces were available when we repeated the test of the same program. Therefore, it is worth testing it twice if you find similar problems.

In case you are not sure about the results of the traces, checking the program being executed for the relevant user in transaction SM50 can be useful

See more details in:

Example with hanging program and SM50 check

Inside that chapter, check specially the second point for the SM50 check:

Repeat execution and check SM50 while the program is running

EASY EXAMPLE (REPRODUCIBLE ISSUE)
Issue description

–> Go TOP
It is taking about 4 seconds to save an order in a CRM system, even if the order has almost no data.

We will see in the traces (both ABAP trace and SQL trace) that the bad performance is mainly due to Z code (BADI implementation) and a query to table T100 in the BADI.

Take traces with ST12

–> Go TOP
The screenshots for this chapter do not correspond to our example, but the steps would be the same.

Also, KBA 2436955 – Step by step instructions on how to use ST12 trace for analysis

Check ABAP trace

–> Go TOP
Se see a total time of 4.087.734 microseconds -à About 4 seconds

Most of the time is in the database (79,9%) (3,2 seconds)

At first sight, we see that 69% of net time is in “SELECT T100”

Nevertheless we will sort by net time

Sort by net time

–> Go TOP
Trace sorted Order by Net ( we again see select T100 is the guilty) 69% of net time ( 2,8 seconds) are spent in the select T100.

“Select T100” is called from a Z program, in this case it is class ZCL_IM_LR_ORDER_SAVE

We display the source code

The statement that contains the “select T100” is highlighted in the source code

Sort by gross time

–> Go TOP
We ensure that the calling program is displayed and we sort by Gross ( as it was initially)
Normally, if there is a clear guilty call that takes most of the time, sorting by gross time will give us a rough idea of the call stack when the guilty program is called . All the programs and events (function modules, methods) that we see over the guilty call in the ABAP trace would appear below in the call stack (Calling programs).

To confirm the previous assumption, if we debug and set a breakpoint in the guilty program, in class ZCL_IM_LR_ORDER_SAVE and method CHECK_BEFORE_SAVE, we see several programs which are the same that we have already seen in the ABAP trace

In our case the guilty method is already in a Z class, so the previous check was not so relevant. It is clear that it is customer’s responsibility to adjust the code of the method (coding of check_before_save).

However lets assume a that we had a case where the guilty method is a standard one. Does it necessarily mean that the issue is SAP’s responsibility? … Not always. It depends.

If all the methods in the call stack are standard methods, then the issue would be most likely SAP’s responsibility

If there are Z methods or function modules in the call stack, then in principle it should be customer´s responsibility to adjust the code, so that the call that causes the performance issue is avoided.

SQL Trace
Screen of SQL trace

–> Go TOP
In the initial screen of the SQL trace we have the SQL Statements sorted by the time when they were executed . In case of old systems, the view will also include all the operations for the same SQL statement (e.g. PREPARE, OPEN, FETCH).

However, this is not the best view to start our analysis. Better, go to next point “Summarize by SQL Statement)

Summarize by SQL Statement

–> Go TOP
We summarize by SQL statement.

This combines the times for all the executions of a SQL statement in a certain point in the program ( e.g. a loop)

This is usually the best option to start our analysis

In recent system versions, the option “Summarize trace by SQL Statement” is called “Trace –structure identical statements

In this view, initially the entries are automatically sorted by Duration. We see the statement we had seen before, in the ABAP trace, at the top (most time consuming). We can also see:

  • Column Object name ( table name) T100 à So it fits to what we have seen in the ABAP trace
  • Column Duration 2.730.360 microseconds à About 2,7 seconds. It basically fits with what we have seen in the ABAP trace, a select to T100 taking 2,8 second

In case of JOIN statements where there are several tables involved, in column “Object name” we would see only one of the tables.

Check detail of SQL statement with parameter values

–> Go TOP
Double click on first statement on the list (most time consuming)

Then we see the SQL statement and the parameters used on it.

The parameters have names

A0 -> Would replace the first question mark “?“ in the sql statement

A1 -> Would replace the second question mark “?” in the sql statement ……………..

In our case, there is only one parameter, with value B%. The SQL statement was select   SPRSL, ARBGB, MSGNR, TEXT from T100 where TEXT like B%

(Selects the rows from T100 whose field TEXT starts with B)

Check calling program for SQL Statement

–> Go TOP
Select SQL statement
Click on button “Display call positions on ABAP programs”

We see the calling program and the SQL Statement is highlighted

Check Execution plan of SQL Statement

–> Go TOP
The execution plan will give us information about the indexes used to perform the query. It is the most difficult topic, because the execution plan is different for each database type and even the screen where the execution plan is displayed is completely different To go to the execution plan:

Select SQL Statement –> Button “Explain”

(In recent versions, to display the execution plan we must use button with icon   but there is no text in it)

In the execution plan, we see it is a Clustered Index scan.

We have a SQL Server database. In this database type, “Clustered Index scan” means that in order to get the records, there is a scan over the whole table. No index is used at all

It makes sense because as we see in the next point, the where clause in our query contains just field TEXT, which is not part of any of the existing indexes for table T100

For the same program in a system with an Oracle database, the execution plan for the SQL statement is as follows

Table Access Full à It also indicates that no index is used

Check existing indexes in relevant table in SE11

–> Go TOP

  • Fields of Primary key index (unique index) can be see directly in the first screen (fields where checkbox “Key” is marked). We can see below the primary key (SPRSL, ARBGB, MSGNR)
  • For other indexes, use button “Indexes” and select each of the indexes to see their fields

To check other indexes button Indexes

There is another index, we click on it

In some cases, it could happen that the index exists in the data dictionary but not in the database.

For an index to be available, it must be “Active” and it must show message like:

Index <Index name> exists in database system ……

Our index contains two fields, ARGB and MSGNR

As you should know, sequence of the fields in the index is also very important In this case, first field is ARBGB and second field is MSGNR

Possible recommendation for our example

–> Go TOP
In this case, the issue is in a Z code so in principle it is not SAP responsibility. We have seen that most of the time is spent in ABAP statement with SQL query:

select * from t100 into table t100_tab where text like ‘B%’.

And there are no indexes that contain field TEXT in table T100.

A possible solution would be to create a new index with field TEXT as first field in the index.

Change query of our example: Execution plan where index is used

In our previous example the query was

select * from t100 into table t100_tab where text like ‘B%’.

As we have seen before, the execution plan shows that no indexes are used by the database to improve performance of that query.

If we change the query in our example to a query for which there is an index (Field ARBGB instead of TEXT):

  select * from t100 into table t100_tab where arbgb like ‘B%’.

And we test it in a system with ORACLE database

Details of the query in the SQL trace would be:

Execution plan
Index T100-001 is used in this case, which contains fields ARBGB and MSGNR (same that what we could see in SE11).

The index with these two fields, ARBGB and MSGNR, basically means that the table entries are first sorted by ARBGB and afterwards by MSGNR.

It makes sense that this index is used because even though our where clause in the query does not contain field MSGNR, the field ARBGB is the first field in the index (so the table entries are first sorted by ARBGB). Hence using this index improves performance of the query.

Example with hanging program and SM50 check

We will change the query in our example in order to have a high response time (more than 10 seconds), so that we can perform similar steps as we would do in a hanging program.

Take ST12 traces and stop before program finishes –> Go TOP

We reproduce the issue and we observe that the program is taking quite long and seems to be hanging. We stop the ST12 trace before the control of the traced program returns to the user

We open the SQL trace. In the initial screen, which shows the entries sorted by execution time, we see that the last entries correspond to report ZCL_IM_LR_ORDER_SAVE executing a query to table T100.

In this case the initial view splits all the operations of a query (FETCH, PREPARE, OPEN, etc.) and it shows one entry per operation. To display the query details and execution plan we must first go up in the list of entries and select the entry for table T100 (object name = T100) with operation “OPEN”.

NOTE

In recent versions you may initially see all the operations that belong to the same query in one single row, so the previous step would not be needed.

By double-clicking on the select statement, we see its details.

It is selecting all the rows of table T100

If we go to Trace — summarize by SQL statement, we see that it was indeed that last statement being executed that was taking most of the time (6 seconds when we stopped the trace).

We also click on button “Display call positions in ABAP programs” to see the calling program

In the program (class) ZCL_IM_LR_ORDER_SAVE we see that the ABAP statement        select * from t100 into table t100_tab.

is the reason for the high response time.


Repeat execution and check SM50 while the program is running –> Go TOP

 

In case the program takes too long to be executed and you had to stop the traces before the control returns to the user (still seeing the hourglass) in the traced program, the results in the traces might not be always reliable.

In case you are not sure about the results of the traces, checking the program being executed by the relevant user in transaction SM50 can be useful. We will do the same for our program

We reproduce the issue again. While the program is running, we go to transaction SM50 and we check the sessions for the relevant user.

We refresh the screen several times and we see that work process number 9 is always executing report ZCL_IM_LR_ORDER_SAVE, and there is a query to table T100.

Hence, in this case the check in SM50 confirms the results observed in the traces so we are sure that the guilty ABAP statement is the one we had seen before:

select * from t100 into table t100_tab .

in program (class) ZCL_IM_LR_ORDER_SAVE.

Please feel free to make questions and give your feedback directly on this blog.

 

To report this post you need to login first.

Be the first to leave a comment

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

Leave a Reply