Case Study – Use ST04 SQL Stats history in Performance analysis

This post covers how I used SQL historical stats from SAP ST04 in analyzing a true performance incident related to SAP system cloud migration. I used SQL stats from “good” and “bad” periods and compare the stats and found where the problem was. This led to resolution of performance issue. You can refer to my post “SAP ST04 Performance Feature – DBA History” on how to get SQL history stats for specific period.

The background

Business users were reporting that a daily transaction was running much longer with no changes on the code/volume after their SAP system was migrated to DXC cloud solution – ESO environment. Migration was completed on Feb 11, 2018. Performance analysis found that the program is mainly spent their time in one SQL-ID 54u3rqzyff94q. Following is the SQL execution stats from ST04 DBA history for post-migration period.

Figure 1 SAP ST04 – SQL stats in post-ESO period

So naturally, the thinking is to how the sql is performing in the past.

What is performance stats for this SQL in pre-migration period

Business said performance was good prior to ESO migration, I got following SQL stats prior to ESO migration via ST04 DBA history feature.

Figure 2 SAP ST04 – SQL stats in pre-ESO period

Figure 2 shows the SQL execution time was about 68 seconds per execution pre-ESO migration vs 278 seconds in post-migration showed in Figure 1. Why? Further analysis of SQL remaining stats like Disc read/execution, Buffer get/execution, Row processed/execution can normally give hints. Longer response time is normally contributed to more disc read, more buffer gets and more records fetched per execution. In this case, you noticed there was no much difference on those metrics pre and post ESO migration. So I looked further to time breakdown of SQL execution, I found that over 90% of SQL time is spent on I/O as reported under column “User I/O Time/Exec”. So the performance issue is related to slow “I/O” performance. Many factors can be contributed to slow “I/O” performance. In this case, our storage expert found out this was related to “IOPS” setting. Action was taken to increase IOPS setting on March 16. After that the SQL performance is back to pre-migration level as showed below.

Performance stats after IO setting change

SAP ST04 DBA history reported following performance stats for this SQL in post-IO setting changes.

Figure 3 SAP ST04 – SQL stats after IO Tuning

You can see that average execution time is 48 seconds which is much better than 278 seconds during the period when the issue was occurring and even better than pre-migration period. And this improvement is mainly due to IO improvement – IO time per execution is down to 48 seconds from 274 seconds showed in Figure 2.

I got relevant performance stats from Figure 1, Figure 2 and Figure 3. Put them into following table for easier comparison.


Pre-ESO migration

Post-ESO migration

Post-ESO IO tuning

Elapse time (Sec)




User I/O time (Sec)




Disc Read




Row processed





And business operation has confirmed that their transaction performance is back to normal ever after. What you can see that User I/O is still a significant part of SQL execution time. This can be improved via Oracle cache technically – this needs to weight many factors which are not scope of this posting.



SAP ST04 performance feature – DBA History

After I posted my blog on how to run SAP ST04, SAP introduced a new feature into ST04 – DBA history which can show history of SQL stats which is only available via separate SQL script before. This is a very helpful feature in SQL performance analysis. In this post, I would talk about the new feature of DBA History of SQL Cursor cache:

  • Where you can access DBA history of SQL cursor cache
  • What you can use DBA history or history of SQL stats for

Where you can access DBA history of SQL cursor cache

DBA history is an option of Selection Criteria for Shared Cursor Cache screen. You can access the screen: ST04 -> SQL Statement Analysis -> Shared Cursor Cache. After you click “Shared Cursor cache”, screen of “Selection Criteria for Shared Cursor Cache” would show up as below

Figure 1 ST04 DBA history – Selection Criteria for Shared Cursor Cache

Figure 1 has two options for SQL stats sources – “Cursor Cache” and “DBA History”. Default option is Cursor Cache. If you click “DBA history” option, the screen would be changed to allow you to select the period. The default period is the total available period depends on a system’s data retention setting.

Figure 2 Figure 2 SAP ST04 – Default input for DBA history

If you execute DBA history with default input, it would get history stats for all SQL statements for the whole available period.

In following example, I chose first 8 days period for a specific SQL-ID 8fu8ych339swj

Figure 3 SAP ST04 – SQL DBA history for specific SQL statement

Execute above selection, I got the same SQL stats data for the SQL statement we normally see with SQL share cursor.

Figure 4 SAP ST04 – SQL history stats

Above screen clearly indicated the period which I selected and SQL stats for the SQL statement from the period.

What you can use DBA history for

In summary, it can be used to compare a SQL performance from two periods. You need to do this to find out whether a SQL performance is changed between two periods and what leads to that changes. Usage case could be performance validation for a SQL improvement or performance trouble-shooting.

ST04 DBA history is useful for performance trouble-shooting when following conditions meet:

  1. The transaction is spending significant more time on database side than it used to be. If database time is back to normal, than your issue would be fixed.
  2. Most of database time is on one or a few SQL statements

If SQL related performance issue is linked to system level change like upgrading or migration, you can compare SQL stats between pre-change and post-change to understand what leads to SQL execution time changes.

You can compare SQL stats from two periods to see possible causes – starting with Elapse Time/Exec, then Disc Read/Exec, followed by Buffer gets/Exe, Rproc/Exec, Wait time/exec etc..

I have used DBA history feature in many incidents involving both performance validation for improvement and performance incident trouble-shooting with or without system-level change. It did help me to quickly identify/confirm root cause and passed it on for resolution. I would give an example to demonstrate this later.

Sometimes, you might not find SQL history stats, this could be due to your system setting on database snap-shot capture.

How to deal with SAP program performance issue due to suboptimal execution plan

It is not often but can happen that a SAP transaction/process is suddenly running abnormal longer due to extracting data from database without known application changes such as volume, setting and logic. This type of performance issue is normally due to fact that database has switched to a suboptimal plan instead from a good execution plan. This is a known issue. This post would talk about several different solutions which I experienced in fixing suboptimal SQL execution plan issue and their pros and cons:

  1. Refresh table stats via SAP database tool,
  2. Restoring table stats via database tool,
  3. Manipulate table stats via database tool,
  4. Use database hints via ABAP program and
  5. Use Oracle plan management tool.

Refreshing table stats via SAP database tool

This tool is used only when you found that there are many changes since last stats updating on the table used in the long running SQL statement. You normally can refresh, then try to increase sample rate. Each time after table stats is refreshed, you can check via SQL command editor to see whether plan for the sql is switched back to the good one. You can use SAP transaction DB20 to check when a table stats was updated last time and an amount of changes happened on the table since then. You can use SAP program rsanaora to updates stats in SAP Oracle environment.

This approach is not always working. but it needs no development, it is a quick action which we can try to fix the issue and has no know negative impact.

Restoring table stats via database tool

If switching to suboptimal SQL execution plan is immediately happening after a table stats is changed. You can restore original table stats to bring back the good plan to fix the performance issue using database tool.

Apparently, this is a very quick and simple fix if it happens to be the case.

Manipulate stats via database tool

This way, you manipulate stats which are used by database to determine execution plan related to selection criteria used in the SQL statement. For example, you manually increase number of distinct values for a selection fields so database might opt for index or reduce number of distinct value for a field so database is more likely to use full table scan.

Manipulating the stats might impact other SQL statements which access the same table. However it is only one ABAP SQL related to the stats, then it is fine.

Use database hints via ABAP program

This way is an ABAP program changes which needs to go through change management process.

Developer will need to modify the long running SQL statement in the program to use specific database hints. You normally modify a program in a development box, move it to quality box then production box involving different level of testing .

The hints is at ABAP level. This means this is effect to all child SQL statements originated from the SQL. So you need to assess how many child SQL statement this SQL would have and whether this hints is true to all child SQL statement. For example, An ABAP program has a SQL statement like SELECT * FROM table1 FOR ALL ENTRIES IN int_table WHERE field1 = int_table.field1 and field2 = Int_table.field2. If ABAP internal table INT_TABLE is empty, the sql statement would have a child statement like “select * from table1”. So index hints is not good for this type of scenario of this SQL statement even it is good for other cases.

We also need to know this approach might not be possible to apply on standard SAP ABAP code. Even so, it would be gone after system upgrading. In the future, if you need to change or remove the hints, it is another ABAP program change.

Use Oracle plan management tool

If your SAP system is using Oracle database, you can use Oracle plan management tool to assign a specific execution plan to a specific SQL statement.

Using SQL plan management avoids changing ABAP code and the issue can be fixed quickly like in a half hour if you are familiar with below steps. You can move oracle execution plan from one box to another box. This means you can move good execution plan for the SQL from testing box to production box and use it to fix the production plan issue. Since changes is done at Oracle level, plan issue on standard SAP code can be fixed as well via this method. Also, this solution is applied at SQL_ID level, other child SQL statements with different SQL ID would not be impacted. If more than one child SQL statements has the same issue, you need to apply SQL plan management for each child SQL. If it is critical to restore the SQL performance as soon as possible, Using SQL plan management is a better than using ABAP hints. You can lock-in execution plan for the critical and important SQL statements or for those SQL statements you think they would change in a system/oracle upgrading.

However since oracle plan management is based on SQL-ID which depends on actual “SQL text”. So any change in the SQL text can lead to different SQL-ID. It happens even it is rare that an ABAP SQL statement has different SQL text which is mapped to different SQL ID after SAP/Oracle upgrading. When this happens, you need to update the Oracle plan management to reflect the change.

Following are six steps on how to use Oracle plan management to fix execution plan issue. Those are from Standard Oracle document. You can google this using key word “Oracle SQL plan management. For convenience, I put hereJ

Step 1 – Find the SQL_ID for the statement in the V$SQL view and use it to create a SQL plan baseline for the statement using DBMS_SPM.LOAD_PLAN_FROM_CURSOR_CACHE.

Step 2 – The plan that was captured is the sub-optimal plan and it will need to be disabled using

DBMS_SPM.ALTER_SQL_PLAN_BASELINE. The SQL_HANDLE & PLAN_NAME are required to disable the plan and can be found by looking in DBA_SQL_PLAN_BASELINE view.

Step 3 – Now you need to modify the SQL statement using the necessary hints & execute the modified.

Step 4 – Find the SQL_ID and PLAN_HASH_VALUE for the hinted SQL statement in the V$SQL view.

Step 5 – Using the SQL_ID and PLAN_HASH_VALUE for the modified plan, create a new accepted plan for original SQL statement by associating the modified plan to the original statement’s


Step 6 – Now if you query DBA_SQL_PLAN_BASELINES you will see two plans in the SQL plan baseline, the original non-hinted plan and the new hinted plan. Only the hinted plan is

accepted, so this will be the plan chosen next time the SQL statement is executed.

Understanding SQL Execution plan in SAP environment

In analyzing a SAP program which spends most of run time in reading data from database, SQL execution plan is often a focus. This post would share my knowledges on SQL Execution plan in a SAP/Oracle environment. It covers following:

  • Oracle SQL execution plan introduction,
  • How to read an oracle SQL execution plan in SAP system,
  • How to assess an Oracle SQL execution plan and
  • Tips on trouble-shooting performance incident related to execution plan.

1 Oracle SQL execution plan introduction

  • Oracle SQL execution plan is a series of steps which oracle takes to execute an SQL to retrieve expected data from database. In the plan, Oracle would tell which table is accessed via which method, in which sequence and how data from each table is joined (when applicable).
  • Oracle query optimizer is responsible for generating the execution plans and choose the best plan which has the lowest cost. Oracle calculates cost of a plan according to predicted I/O, CPU and network resource utilization based on various stats – table stats and system stats and related Oracle parameters setting.
  • Various stats used in cost calculation are dynamic and based on sample data. This means two things: the plan chosen by database might not be the best one occasionally and the execution plan for a SQL can be changed over time. When Database is switching to suboptimal execution plan from good one, the related business transaction/process would take more time to finish.
  • Oracle query optimizer only generates a plan once and reuses it if no stats related to execution plan are changed and the plan is still in memory (Oracle Shared Pool).

2 How to read an Oracle execution plan

A SQL execution plan could be very complex with many steps. SAP makes it easier for you to read Oracle execution plan by numbering steps in the order which a step is executed similar to Figure 1. So you read a SQL execution plan starting with step 1, then step 2 and so on. You can display a SQL execution plan for an executed or being executed SQL via SAP transaction ST04.

Figure 1 How to read a SAP Oracle execution plan

In Figure 1, SAP would execute step 1 to read row-ids(data records) by doing index range scan on index TBTCO~8 via index field STRTDATE(A1) and filter predicates, Then execute step 2 to get needed dataset from Job overview table TBTCO via row-ids from step 1), Then it would get another data set from TBTCP table by executing step 3 and step4 prior to execute step 5 which is hash join on both data set to return the data needed by the SQL. Index range scan is used when more than one entry from index table meet the searching criteria such as STRTDATE (Job start date) field. Many jobs starts in one day typically. So when TBTCO table is searched by job start date etc, it would normally return more than 1 entry. Only Select SQL statement which is using all index fields of a primary index always returns 0 or 1 entry.

TBTCP~8 index is a standard SAP secondary index. Following screen shot shows its’s index fields

Figure 2 TBTCO~8 index

3 How to assess a SQL execution plan

There are two purposes of assessing a SQL Execution plan –

  • Whether the execution plan used by database is correct in current environment and
  • What can be done to improve the performance of the SQL.

Assessing an oracle execution plan could be a very tedious and very demanding task even for an experienced Oracle expert especially when the execution plan is complex. More steps and more tables a SQL execution plan has, more complex the plan is.

When I assess an oracle execution, I would normally check:

  • Whether an index used in accessing table is correct,
  • Whether table is read in right sequence and
  • Whether table join methods used by database is correct.

A right plan does not necessary mean it is a “performance sound” plan. For example, it is using full table scan to get a few records from a big table when no index satisfies the SQL where-clause. The Full table scan is correct in this context but it is not efficient. Since the SQL only returns several entries from the big table, this indicates the fields used in the where-clause are very selective, creating an index using fields from the where-clause should be very efficient.

4 Tips on trouble-shooting performance incident related to execution plan

When there is a performance incident in a production box with a single SAP transaction/program in your system, you have done performance trouble-shooting and identified that program is spent most of time in executing one particular SQL Select statement, the SAP transaction/program has no technical/functional/volume change and other sap transactions/program is working as usual, this is normally result of SQL execution plan switch – suboptimal SQL execution plan is chosen by the database for whatever reason.

In this situation, I would normally do following checks to confirm the plan switching issue and identify right execution plan –

  • Check execution plan history and timing of plan change to see how is that related to timing of performance incident. If yes, then this is likely a plan switch issue – Oracle can switch to suboptimal execution plan for various reasons like system upgrading, stats changes etc. You can use SAP program /SDF/RSORADLD_NEW to check plan history if you prefer or cannot use Oracle AWR.
  • Check execution plan between production box and other non-production SAP Boxes or other SAP production boxes. For example, if production box and testing box has different execution plan and plan in testing box is more efficient, then it is an Oracle SQL execution plan switching issue. The plan in testing box is the expected plan.
  • When above step cannot draw a conclusion on what is right plan, then you need to validate the plan according to those above mentioned checks.
  • Test your idea – If you think one table access path, one table join method, and join sequence might be better, you can always test this out by executing the SQL with proper database hints. . Use this test only when no other ways to validate this. You can always use SAP St04 SQL command editor to execute a SQL in a SAP box even you have no direct-access of database SQL tools.

In my experience with SAP supply chain system, the common pitfalls are:

  • Inappropriate index is used to access a table. Using index is good for performance. But using a wrong index is much worse than a full table scan.
  • Full table scan is used while it is more efficient to do so via an index.
  • One of nested-loop and hash-join operation is incorrectly preferred over the other. Nested-loop is for smaller amount of record from outer table with good join condition (like primary key) to the inner table. Otherwise, it would be hash join for large dataset. Sort merge join is used with large data sets and inequality join conditions.

The good news is that we normally do not need to pay attention to an oracle execution plan except following typical cases:

  1. The SQL is using too much resource like CPU time, high IO time overall in the system and each execution of the SQL is longer like more than seconds – like top expensive SQL in Oracle AWR report or ST04 SQL cache etc. Normally you do not need to assess an execution plan even it is in your top expensive SQLs list if each execution of the SQL is faster like in milliseconds. If a SQL shows up as the most expensive SQL and it is faster, you need to work with function owner to reduce resource usage to focus on reduction of frequency which the SQL is executed.
  2. Execution of the SQL is holding a lock which is blocking other business processes, this is especially true when the SQL is using “SELECT for update” command.
  3. A business transaction cannot meet business performance goal due to long execution of a SQL.
  4. A SQL is taking much more time suddenly than it was while other SQLs are performing normally.
  5. Preparing system upgrading.

Knowledge with different Oracle optimizer access paths is a precondition for an efficient plan review. Click Oracle Optimizer Access Path if you would like to know more.

If you wonder the general approach on performance trouble-shooting, you might find my post “SAP production performance issue trouble-shooting” helpful.

Many SAP work processes were left in “on hold” status with RFC info

I was contacted on one production issue – Almost all SAP background work processes were in “on-hold” status with RFC info in SAP SM50 work process monitor transaction and seemed like not moving. I was asked why? Please continue reading to understand the reason which I identified through analysis via transaction SM50, STAD, RZ12. This post would cover:

  • The issue – SAP work process in “on hold” status with RFC info,
  • Trouble-shooting of SAP work process in “on hold status”,
  • Conclusion,
  • Impact of SAP work process on hold and
  • Further clarification

1. The issue

SAP workload monitor are showing that majority of SAP background work process were in “on-hold” status with status information or reason “RFC response” as showed in below Figure 1.


If you need help on SAP SM50, you can refer to my post on how to run SAP SM50/SM66.

2. Trouble-shooting

SM50 in Figure 1 shows BTC work processes on hold were related to “RFC Response”. Figure 1 also tell us all Background processes were busy, that should be a result of long running BTC due to “RFC Call” performance. Are those RFC calls to external system? Or Are those RFC calls to internal system? Do we have RFC resource issue locally or do we have RFC resource issue remotely?

2.1 System was having No Free DIA WP for handling RFC call

So I checked whether the system has free DIA work processes for handling RFC call via SAP transaction SPBT. You can check it via SAP SMQS as well.

Figure 2 SAP SPBT – RFC resource availabilities check

Figure 2 shows that the system allows up to 25 DIA WP for RFC processing but at the moment, there was no free DIA WP for handling any new “RFC” call because “ALL PBT resources are currently busy”. So you might wonder who was exhausting system RFC resources and what we can do to fix or mitigate the situation.

SAP transaction SPBT is mentioned in section of how to validate RFC configuration in my post how to configure RFC server group via RZ12.

2.2 Who were exhausting system RFC resources?

Who were exhausting the system RFC resources? I checked this via SAP workload monitor SM50. Following is the result

Figure 3 SAP SM50 – DIA WP consumption

Figure 3 told us that most of DIA work processes were occupied by one single user – this is not a normal online user. Then you might wonder origin of those RFC calls.

2.3 Where were origin of RFC calls?

I used STAD to find out what SAP job/program were firing those RFC calls or where were those RFC calls from?

Figure 4 STAD – RFC calls

You can normally find the origin/parent of RFCs via SAP transaction STAD, Display details info and then check “Client” info. In this case, we found that RFC showed in Figure 4 were coming from another SAP system – where many work processes were running under a single online SAP user which needs information from the system I was looking into.

3. Conclusion

The reason that SAP work processes were in “Stop” or “on hold” status with a reason of “RFC response” was due to contention of RFC resources in the system. Available RFC resources (DIA work processes) were occupied by a storming RFC calls from a remote SAP system.

4. Impact of SAP background work process in “On-hold”

Impact is on two areas – SAP background job could not start on time in the system due to shortage of background process created by the on-hold or stop status and job started would run longer if it needed to make RFC calls.

4.1 Long running of back ground processes

Following screen shows processing an idoc took over 1 and half hours while it can normally finished in seconds.

Figure 5 Background process ran longer than normal

Figure 6 SAP STAD – Work process with big RFC time

Based on Figure 6, we know over 99.9% of runtime is spent on RFC call. Further review, you can know that RBDAPP01 is making a RFC call which was supposed to be executed locally and it spent most of RFC time on waiting. Please refer to my post on explanation of STAD statistical data if you would like to know details.

4.2 Long waiting of background processes

“Delay” column in SAP SM37 Screen shows how long a background job has to wait before it is dispatched to an idle BTC work process. Following screen shot is tried to give you an impact of the issue.

Figure 7 SAP SM37 – Job delay due to shortage of BTC work processes created by RFC issue

You can see that job delay is varied – that depends on job schedule time and status of system.

5 Further clarification

Technically, you can fix the issue immediately by terminating the corresponding program/process in the remote system. But in our case, I just added 8 more DIA WPs to RFC resources VIA RZ12 since almost no DIA sap users were online at that earlier AM system time, the issue were gone by itself about half hours after the addition. So in this situation, just waiting and let time cure the issue if there is no business impact some times. Too many RFC work processes can cause SAP work process dead-locks sometimes.

A SAP work process can be in “RFC” status for many reasons like network, remote server issue, expensive RFC call etc. But this case, it is due to local resource issue.

Normally more BTC work processes can be seen left in “on hold” or “stop” status than DIA WP. A DIA WP would be rolled out when it is in “waiting” status after connection is established, so it is available for other online/RFC request. This is a typical SAP design. Interact activities executed by online SAP users are conducted through SAP DIA work process. You normally has much more SAP online users than available SAP DIA work process.

Further follow-up is needed to understand the interface configuration and volume control and end user activities in the remote system, so a system configuration tuning or program design change can be made to avoid the reoccurrence of this issue.

Explanation of SAP STAD Single Statistical Records/data

SAP STAD is one of my frequent used SAP transactions in performance analysis. STAD can show you where the time is spent over a list of technical components involved in a job/program execution, so it is often 1st step I would take to review performance of a program/transaction which finished recently. Following my previous post how to run and navigate through SAP STAD transaction, I would share my understanding of STAD data – that is the basis for you to use STAD to do performance analysis.

  1. Structure of STAD statistical data,
  2. Explanation of STAD statistical data and
  3. How to use STAD for performance analysis.

1 Structure of statistical data of a SAP transaction step

SAP has collected various statistical data to record performance of various technical components involved in executing a transaction step. To facilitate performance analysis, SAP groups statistical data into one main record and many optional sub-records. Whether a specific type of optional sub-records exists depends on context of transaction step and system parameter setting (see my post how to run ST03N) Following are the statistical sub-records I often deal with in my SAP environment:

  1. DB (sub records) – Statistical data related to database operations performance in a transaction step.
  2. DB procedure (sub-records) – Statistical data related to execution of database stored procedure in a transaction step. This is more important for SAP SCM boxes where LiveCache is used.
  3. Table (sub-records) – show most expensive tables accessed in the transaction step according to setting /stat/tabrec.
  4. RFC (sub-records) – show overview of RFC statistics from RFC client, RFC Server, RFC Client destination and RFC server destination sub-records according to setting /stat/rfcrec.
  5. Client Info (sub-records) – show origin of transaction step like job name, program name and system name.

Main record for statistical data of a single transaction step is like header record of a SAP business document. Main record contains statistical data related to time profile of transaction step, task and memory information and data volume involved in the transaction step. Sub-records are like details lines of a business document. One business document can have only one line of header record with 1 or more lines of detail records.

One execution of program can have more than one transaction step. SAP performance analysis normally focuses on the most expensive steps.

Explanation of STAD statistical data

SAP single statistical record has main records and many sub-records. SAP provides “corresponding buttons” in STAD detail screen to help you locate information of SAP statistical main record and sub-records quickly. Table 1 lists some frequent-appeared buttons showed in my SAP environment.


Related statistics


Time profile of transaction.


Database time breakdown according to type of database operations.

DB Procedure

Details on procedures calls to LiveCache.


Show memory usage at the end of step.


Database time – table profiles.


RFC statistics for RFC client, RFC Server, client destination and/or server destination sub records.


Data transferred to application.

Client Info

Origins – user, job, server which the transaction step belongs to.

Table 1 SAP STAD – navigation buttons

When a corresponding STAD sub-record exists, a related button would show up in SAP STAD Detail screen. Other buttons like “HTTP” other than what listed in Table 1 are not covered in this post.

2.1 SAP STAD – Time Profile of a Transaction Step

Top of SAP STAD “Single Statistical Records – Details” screen is the section of Analysis of time in work process section similar to Figure 1. You can back to the time profile screen by clicking “Time” button from other STAD details screens.

Figure 1 SAP STAD – Analysis of time consumption

Figure 1 is related to an individual statistical record of background step. Response time in Figure 1 is actually job duration which you would see in SM37 when the program (RMMRP000) is the only step of a background job. In Figure 1, all time components listed in “Response time” box are independent/exclusive.

Response time
= wait for work process time + Processing time( ABAP) + Generation time + Load times + Roll times for rolling in work data + Database time + Enqueue time for logical SAP locks + Roll wait time (not including task types RFC/CPIC/ALE). When DB procedure is used between application server and liveCache, a new component as “DB procedure” would be added to the Response time.

For dialog task, response time is measured at SAP application server from time when a request is received by the SAP application server to the time application server finishes processing and sending data to the client. Please note – Response time starts to tickle from the moment request is arrived at application server not the moment the request is sent by an user and it stops tickling at the moment when the application finishes processing and the last transfer of data from the application server to client is sent. The time used to send request to SAP application server and time used in sending last info back to client is not part of STAD response time but it is a part of end user’s online experience. Network and client impact on dialog user response time can be measured by GUI time and Net time (Figure 1 – right bottom corner under Frontend).For other tasks, Frontend statistics(GUI and Net time) is not relevant any more.

CPU time is not a separate time components but a sum of CPU utilization of each corresponding operation listed in “Response time” like ABAP processing, Program/screen load and generation, Roll-in and Enqueue operation except Database operation.

RFC+CPIC time: time spent by all RFC calls (as a client) in the transaction step. So this time can be bigger than response time if several long asynchronous RFC calls are initiated in parallel.

Wait for work process – time which the transaction step is put in the queue due to shortage of work process to the time when a free work process is available to execute the transaction step.

Processing time – time used in execute ABAP/IV statements.

Database time – time used by database to execute needed database operations like reading table changing existing table data or inserting new data into table. Database time is measured at application server side so it includes network time between application server and database server when applicable. So network latency can impact transaction performance when SAP server/instance used in executing the transactions is from a different server where database is in. Time used in reading data from buffered table at application server is not a part of “Database request time” but part of processing time. Time used between application server and liveCache (SAP SCM) is not part of Database time here.

For all other fields in Figure 1, you can get SAP online explanation in the transaction by placing the cursor into the corresponding data and click “F1”.

In normal ECC environment, significant time components of response time is processing time, database time and Roll wait time (when Synchronous RFC is used). Load time, generating time should be very minimal or zero—otherwise, this can indicate memory issue either setting or physical limit or there is a CPU contention.

After analysis of response time profile, you can navigate to specific time component/sub-record should you need further details.

Checking time profile of a transaction is often the first step of reviewing a business transaction/job performance. This can quickly point to area you should focus in SAP program/transaction performance analysis.

2.2 SAP STAD – Analysis of ABAP/4 Database Request

Click “DB” button in STAD details screen would bring you to screen similar to Figure 2.

This section breakdowns database time into different database operations so you can see individual database operation’s contribution toward database time and their performance.

Figure 2 SAP STAD – Database time breakdown on type of ABAP request

For details explanation on individual field in Figure 2 screen, please click corresponding data and use “F1” key to get SAP online document.

2.3 SAP STAD – Analysis of Table Access

This is to breakdown database time according to tables involved – showing types of operations on a particular table in a transaction step and how much time the transaction step has spent on the particular table. A program might has accessed many tables. For performance reasons, not all tables accessed in the transaction step but up to value specified by SAP online parameter stat/tabrec. Default setting is 5 which is enough for performance analysis.

Figure 3 SAP STAD – Database time breakdown on tables

Dir reads are referring to read table via full primary key. Other type of table reads are sequential read. Changes includes update, insert and delete database requests.

2.4 SAP STAD – Analysis of Remote Functional Call

When a transaction is a RFC step itself or when a transaction step has initiated at least one RFC call, then at least one RFC sub-records exists with the transaction step by default. You can click the “RFC” button to review RFC sub-records. You might get display similar to Figure 4.

Figure 4 SAP STAD – RFC calls over view con

Figure 4 screen is a RFC overview screen of a transaction step. It has two portions – client and server. Dependent on the step, you might see Client portion or server portion only. RFC Client shows 5 connections involved 3 destinations are having total 98 client call with calling time of 3,150 ms and execution time 2,713. RFC Server portion shows 1 connection to 1 destination has executed only 1 RFC call with call time of 27,368 ms against remote execution time of 27,367ms.




Number of connections between client and server system.


Number of distinct destination from RFC client/server destination sub-records – Destination indicates the system where the call is sent to by the client. Destination is normally an entry which is configured in SM59 in the client system where the RFC is initiated.


Numbers of distinct users from Client/Server destination sub-records.


Sum of number of Calls from Client/Server destination sub-records – For RFC client, this is number of calls made to the server. For RFC server, this is number of calls executed in the box where STAD is run.

Calling Time

Sum of calling time from Client/Server Destination sub-records. The time includes network connection/traffic time.

Remote Execution

Sum of remote execution time from each Destination sub-records. Time spent in executing the RFC in server side.


Sum of idle time between two RFC calls when connection is open.


Sum of Data sent to server for client call or Data sent back to client for server process based on RFC client/server destination records


Client – Sum of Data received from server; Server – Sum of data received from client. All data are sourced from client/server destination sub-records

Table 2 SAP STAD – RFC Overview fields explanation

If you can click each highlighted fields on figure 4, you can see available SAP statistics for RFC client, RFC client destination, RFC server and RFC server destination sub-records.

2.4.1 RFC Client Destination statistics

You click the highlighted number in the “Connections” field of client portion of STAD RFC overview screen, you would see 1 more client destination record similar to Figure 5.

Figure 5 SAP STAD – RFC client destination record details

A RFC client destination have information:

  • RFC type – synchronous(wait) and asynchronous(non-wait). Here is Synchronous.
  • RFC user name – SAP user account used to execute the RFC call. Here is bgomusr.
  • Destination – Where the RFC is going to be executed. Here is CC*USD.
  • Instance and IP address– The server where the RFC call is requested and its’ IP address.
  • Partner instance and IP address – The server where the RFC call is executed and its’ IP address.
  • Calls – number of RFC calls made to the destination at the transaction step.

Please refer to above table for Explanation for other fields like Calling time and etc.

Name of Destination is normally configured in SM59 except some SAP internally reserved destination name. Name of instance is what you can see in SM51 for SAP system. Calls is total number of call made over the connection to the destination. Calling time, Remote Exec time and idle time is sum of corresponding time from each calls over this open connection to the destination. Data send/receive time together with size of sent/received data are sum of corresponding data from each calls over this open connection to the destination as well. You might see SAP “transaction code” field as well in client destination sub-record when applicable.

2.4.2 RFC Client statistics

You click the highlighted number in the “Call” fields of client portion of STAD RFC overview screen, you would see 1 more RFC client sub-records similar to Figure 6.

Figure 6 SAP STAD – RFC client record details

RFC client shows

  • Call number – RFC call sequence. Here is 1 – means FM XIPAY_CC AUTHORIZATION is the 1st call to the destination CC*USD.
  • Destination – where the FM XIPAY_CC_AUTHORIZATION is executed. Here is CC*USD.
  • Function name – Name of Function module which needs to be executed by the server. Here is XIPAY_CC_AUHTORIZATION
  • Calling time – Duration of RFC call from begin to end as seen from the client side. Here is 2,647ms
  • Remote Exec.time – Time needed to execute FM XIPAY_CC AUTHORIZATION in the destination. Here is 2,501ms.
  • Idle time – Sum of idle time between two RFC calls. Here is 0 ms.
  • Data send time – time needed to send data to Server. Here is 980 bytes
  • Date receiving time – time needed to received data from Server. Here is 1,615 bytes.

The Destination might be a different SAP server/system but it could be the same server/system where SAP transaction STAD is executed.

2.4.3 RFC Server Destination statistics

You click the highlighted number in the “Connections” field of Server portion of STAD RFC overview screen, you would see 1 more server destination record similar to Figure 7.

Figure 7 SAP STAD – RFC Server destination sub- record

A RFC Server destination record shows

  • Caller user name – SAP user name which is used to start the RFC call. Here is bgomusr.
  • Caller client number – SAP client number of the system where the RFC call is started. Here is 4*
  • Destination – The system where the RFC call is executed.
  • Instance and IP address – specific server of the destination where the RFC call is executed and its’ IP address
  • Partner instance and IP address – The server where the RFC call is requested and its’ IP address.
  • Calls – number of calls made to the destination at the transaction step.

Other fields have similar explanation to what we have in client destination. You might see transaction code as well. Execution of the transaction led to execution of the RFC call to the destination.

RFC Server destination is to track total number of RFC calls which the server processes. There might be various remote function modules being called. So it does not make sense to track function module in this context.

2.4.4 RFC Server Call statistics

You click the highlighted number in the “Call” field of Server portion of STAD RFC overview screen, you would see 1 more Server destination record similar to Figure 8.

Figure 8 SAP STAD – RFC Server Sub-record

A RFC Server sub-record shows

  • Call number – RFC call sequence. Here is 1 – means FM APPLICATION_IDOC_POST_IMMEDIAT is the 1st call to the destination *01
  • Destination – where the FM APPLICATION_IDOC_POST_IMMEDIAT is executed. Here is *01.
  • Function name – Name of Function module which has been executed. Here is FM APPLICATION_IDOC_POST_IMMEDIAT.
  • Calling time – Duration of RFC call from begin to end as seen from the client side. Here is 27,368 ms.

Please refer to RFC client statistics for explanation on remaining fields.

The destination is one of SM59 configuration in client system which might be the different system from where the remote function module was executed. This indicates that you might not find destination configured in the system where you run STAD.

Last not least on STAD RFC statistics, I would like to mention controls which SAP has in place to limit number of RFC sub-records a transaction step should keep. One transaction step can make many RFC calls to many destinations. For performance reason, SAP does not generate a RFC sub-record for each Function called and for each destination involved. SAP online parameters stat/rfcrec is used to control number of RFC sub-records a transaction is allowed to keep. The Default setting is 5. When more than 5 RFC client calls are initiated from a transaction step, SAP would only keep statistics for the 5 most expensive RFC calls. When more than 5 destinations (actually connections) are involved in RFC calls in a transaction step, SAP would only keep statistics for 5 most expensive client destinations. The same goes with RFC server and RFC server destination statistics. A RFC call is more expensive if execution time is longer. Since the 5 most expensive calls are captured, those statistics are enough for RFC performance analysis. Increasing /STAT/RFCREC parameter could be dangerous to workload collector performance. It might impact system performance due to larger workload resulted from increasing this parameter.

2.5 SAP STAD – Task and Memory information

Clicking “Task/Memory” button, you would see screen similar to Figure 9.

Figure 9 SAP STAD – Task and memory information

Task and memory information sub-record shows

  • Terminal ID –Only available for SAP DIALOG work process. When it is available- it is either a name of an end user device like a computer name, IP address or server name from which the transaction step is initiated. RFC call, HTTP request, ALE and Dialog requests are executed in SAP via SAP dialog work process. Place your cursor at the terminal field, and process F1, you can see following standard SAP document “Name of the terminal or presentation server from which the dialog step was initiated and to which the
  • Terminal In/Out message: SAP online document “Number of Bytes required by the presentation server (terminal) for communication to and from the dispatcher work process to control the dialog.” Apparently, this field makes sense for Dialog request and HTTP request etc. but not make sense for RFC/ALE and background tasks. When dialog transaction is executed over a poor network connection or poor SAP GUI performance, large terminal in/out message can impact performance.
  • Work Process No: The number of SAP work process used to execute the transaction step.
  • Trans. – ID: a SAP internal assigned ID used to locate each specific execution of a SAP transaction or program. All steps of a specific executed program/transaction would have the same transaction-ID. All RFC child processes launched from the same transaction step with the same function module would bear the same transaction id even it is different from the parent program and each of them is having different session-id.
  • Session-ID: a SAP internal assigned ID used to locate a specific session where the transaction/program is executed. For GUI, each SAP GUI window is a session which has its own session-ID, All dialog transaction steps executed in the same SAP GUI window would have the same session-ID.

Terminal ID and session-id is used to locate the device/gui related to a transaction step of an online transaction, for background job, this would be replaced by job name. Transaction-ID is used to identify all steps/activities of an execution instance of SAP transaction/program. When a program is executed, one or business document can be created or changed.

SAP performance analysis normally focus on cutting runtime and increasing throughput. I did work several cases on cutting memory usage of customized ABAP program/transaction. But I found memory information from this tab is less helpful comparing with information stated in previous sections. I encountered cases where memory usage stated in STAD was much smaller than what it was reported by SM50. Apparently, SAP STAD cannot tell you how long a certain memory is occupied by the system etc. I normally use SAP tool /SDF/MON or SM50 to monitor memory usage of a transaction/process which gives a better picture of memory utilization over course of transaction/program execution.

2.6 SAP STAD – Bytes Transferred

This is to show size of data requested by SAP application to run the transaction step.

Figure 10 SAP STAD – Application data volume


2.7 SAP STAD – Client Info or Extended Passport

Here “Client” means the parent which is responsible for generating the transaction step which is different from RFC client which show what RFC call is started by the transaction step.

Figure 11 SAP STAD – Client info

This screen is telling you a lot of information about parent of the transaction step you are reviewing. Above screen shows that transaction step is result of executing a batch job – “S2C-OA_US_REPROC_ST64_ORD_SINGLE” under user “BGOMUSR” from system/server “*00”. All transactions executed by the same user from the same SAP GUI window has the same root context. All steps of a SAP job has the same root context as well.

In my version, system ID and server information are determined during the job creation. When SAP system is migrated, the old server name is still showed unless the business job is deleted and recreated via SAP transaction SM36 etc. after system migration.

3 SAP STAD and performance analysis

At the beginning of the post, it is mentioned that SAP STAD is used to review statistical data of a recent transaction. What can be considered “recent”? This is defined by system parameter stat/max_file. SAP stores stats hourly at OS level which can be overwritten after number of file reach what STAT/MAX_FILE specifies. Where the stat file is stored is controlled by another parameter stat/file.

At this point, hope this post has helped you to get a better understanding of SAP single statistical records. In my future post, I might share tips on how to use STAD for performance analysis and how SAP STAD tools can work with other tools in SAP performance analysis.

Mass VS Individual deletion – Does it matter to SAP program performance?

My previous post – “SAP ST12 Trace – SQL performance analysis” mentions that we can use mass/array database operation to replace many individual execution of the same SQL to improve database access performance. This blog will go through a true business case where a code has been changed to use array/mass SQL deletion to replace a repeated executed single deletion to delete records from database table. After the change, the business job runtime has over 90% improvement. This post would cover

  1. The background of SAP ABAP program performance tuning,
  2. The solution of SAP program performance tuning and
  3. The result of SAP program performance tuning.

1. The background

Volume of a business process is going to increase. To prepare for higher volume, we are requested to check whether the program performance can be further tuned. For that purpose, the business program is tested and being traced via SAP st12 transaction. Following (Figure 1) is a part of SAP ST12 ABAP trace screen showing top hit list based on “gross %”. And Figure 2 is a part of SQL summarized screen of the same execution.

Figure 1 SAP ST12 ABAP trace – many execution of SQL deletions

Figure 2 SAP ST12 SQL Summary

Clearly from above trace, we can see improvement of this program can only come from changes which can reduce database time which counts for 99.4% of runtime. And database time spent by the program is from 3 SQL delete statements which are from functional module /Ortec/TLO_HLP_SSCR_VAR_DELETE which are called twice by ABAP form f_completed (see Figure 3).

Figure 3 ABAP source code

What is the problem here? What is the solution?

The solution for ABAP performance improvement

Based on tips from “SAP ST12 Trace – SQL performance analysis”, an expensive SQL statement can be reviewed from following area to see whether this can be improved –

  • Check database table access strategy.
  • Review identical access.
  • Review table buffer strategy.
  • Review number of SQL execution.
  • Review data returned.
  • Review table data volume management strategy.

In this particular case, we can see the reasons on why those 3 Delete SQL statements are so expensive based on Figure 1 and Figure 2 – it is due to over 3,600 executions. The each execution of sql is efficiency – in average, it took less than 1.2 ms/deletion (based on ABAP trace and SQL summary). There is no identical selection. Database is using the primary index to execute 3 SQL delete statements whose SQL where-clause match primary index. So this is not a table access strategy and identical selection issue. Based on business process, the corresponding table is changed very often, table buffering is not applicable. The table size is not relevant here as well since it is deleting record via primary key and table size is not big. So it looks like that we need to review number of SQL execution to see whether it can be consolidated.

When we review the source code (see Figure 3 above ), it is found that Functional Module has a simple task to delete tables using a single value and the “F_completed” form which calls the FM is called from a loop. The FM is a 3rd party code. So the proposal was given to developer to change the program logic to replace individual deletion with mass/array deletion.

Based on the above input, the program logic was changed. The ABAP form f_completed is not called in the original loop. Record for deletion is collected and stored in a program internal table in the original loop. The ABAP form is being rewritten and it is using mass database deletion based on the internal table instead of calling 3rd party code ( Figure 4 ).

Figure 4 ABAP mass deletion based on internal table

So what is the result of those changes?

The result of ABAP performance tuning

Following Figure 5, Figure 6 and Figure 7 shows when above changes was implemented in our production, job runtime and job table access performance comparison. In this particular case, this simple changes have made up to 98% performance improvement after changes was moved into production on May 25.

Figure 5 ABAP version management – show date when mass change is in

Figure 6 Job runtime comparison – before and after performance tuning

Figure 7 STAD – table access comparison before and after performance tuning

Figure 8 Monthly resource utilization under individual deletion

Figure 9 Monthly resource utilization under mass deletion

Figure 8 and Figure 9 show monthly resource utilization of the same program before the change and after the change – that is over 150 hours database time saving with the mass deletion.

Further clarification

Purely from runtime point view, the job can finished in 4 minutes prior to performance tuning. We tend to think that no much performance tuning opportunity exists for such faster program. This case told us that the fact that a program finishes faster does not mean the program is performance perfect unless it has been gone through performance testing or designed/coded by professional who is expert at performance. That a program runs faster might be due to lower volume or simple processing not attributed from sound performance design/code.

Reward from tuning a quick running program depends on frequency a SAP is executed. Priority of tuning a program depends on business performance requirement and resource footprint of the program. Performance tuning normally focus on a program which cannot meet business runtime requirement and it is using a lot of system resource.

Performance tuning can be an iterated process. If you would like to know more, please click SAP ABAP program performance tuning process.

SAP ST12 Trace – SQL performance analysis

This post is to continue my writing on how to tune a SAP program/application performance based on SAP ST12 traces. My previous post is writing about overall SAP program performance tuning process and how to analyze ST12 ABAP trace to improve SAP program/application performance. Here, I would cover how to analyze SAP ST12 SQL trace to tune SAP program/application performance from development point view.

1 SAP ST12 trace – SQL analysis

You might wonder where you should start to analyze SQL statement performance. My choice is to use “Summarized SQL Statements” of SAP ST12 performance trace and focus on top expensive SQL statements. Please click here for how to navigate to “Summarized SQL Statements” screen from SAP ST12 performance trace.

1.1 Understand data in “Summarized SQL Statements” screen

Following is a portion of SAP “Summarized SQL Statements” screen from a SAP ST12 performance traces. The displayed is sorted by total execution time or duration of a SQL spends during tracing period in default.


Figure 1 – SAP-PERF.CA

Before you analyze SQL performance based on “Summarized SQL Statements” screen, you need to make sure that you understand the data presented in the screen:

– Number of times which a SQL is executed by the program during the trace.

– Number is in percentage which of all table read are identical. Two reads on database table are identical, this means that the same program read the same record two times. So as long as the SQL statement where clause is the same on the same table even they are at different location of the program and different fields are returned, they are still identical. In the figure-1, the second line has a value of “1,200” and “58” in column “execution” and “identical”, this means that 696 of 1,200 times when SQL are executed is identical. 1,200 X 58 / 100 = 696.

– Duration is sum of each individual SQL execution time for number of times under “executions” column. Records are total # of records which database server returns to application server where your program is running.

– Average time per execution for the SQL = duration/Number_of_execution. Average number of records retrieved from database table per execution of the SQL = Records/executions.

– SAP table buffer type. There are no buffer, single record buffer, generic area buffer and full table buffered. Blank means that table is not buffered.

-SAP transparent table/pool/cluster/view name. When it is a cluster table or pool table in source code, “Summarized SQL Statements” would show cluster or pool name instead of the table name. For example, in SQL, you can get change header or item information from table “CDPOS”, in ST12, it would show CDCLS under Obj. name column instead of “CDPOS”.

– This is a “shorten” SQL statement from database SQL cache. You can see full SQL statement by double clicking the SQL statement. You can select the SQL followed by clicking on the in figure-1, SAP would then show you the SQL execution plan which contains full SQL. Several ABAP SQLs can be mapped into one SQL statement in the summary windows. You review the related ABAP source code by clicking in above figure-1 after you place cursor on the line you are interested.

At this moment, I would like to clarify several things further to avoid possible confusion on data displayed in “Summarized SQL Statements” screen showed in figure-1.

Number of execution, identical selection and Number of records returned in “Summarized SQL Statements” is from database server point view not application/program logic point view. For example, Select-for-all SQL statement is just executed once from ABAP program logic point view, but number of execution in “Summarized SQL Statements” screen might be more than 1 depends on number of records in the ABAP internal table and system setting. Also, number of records returned by the database for SELECT-FOR-ALL statement could be more than number of records which the program see since the database interface in the application side would remove duplicated records before data is passed to the program.

Number of records which a program sees could be different from what database server actually processes as well. For example, when database server does a “SUM” operation, it just returns one value/record but database might need to get all needed records to get the total result.

All time here is measured in Microseconds and measured from application server point view, so the database time includes impact from network traffic time and IO system when applicable. There are general guidelines to see whether a database data operation( sequential read, direct read, insert and commit ) is impacted by database performance issue(including network and IO) based on average response time. However network and IO normally have a broad impact on a SAP system performance instead of cherry-picking one specific program.

1.2 Analyze Top expensive SQL operation.

Following is the steps I used to analyze Top expensive SQL statement based on SAP ST12 SQL trace for tuning SAP program performance.

  • Check database table access strategy.
  • Review identical access.
  • Review Table buffer strategy.
  • Review number of SQL execution.
  • Review data returned.
  • Review table data volume management strategy.

All above checks/tests, if you do not like term “steps”, can be needed for analyzing one SQL statement. Or only one check is needed for a particular SQL statement. All depend – your knowledge on the business solution, the program, testing case, and the SQL.

1.2.1 Check database table access strategy

This step is to review SQL execution plan to see whether an index or right index is used to read the table. So we can tune SAP program performance by speeding up data retrieval via an index or more appropriate index.

First, you got the SQL execution plan from “Summarized SQL Statements” screen and index details

Figure 2 – SAP-PERF.CA

You can get index details by double clicking on the index name or get all indexes by double-clicking the table name in the execution plan.

Then, you review and analyze execution plan by cross-checking between SQL where-clause and execution plan. You also need to check SQL statement in execution plan and corresponding SQL code in the ABAP program. You can have following result

1. Index is not used and database is using “full table scan” to read database data.

This could be where-clause of ABAP SQL code has no selections fields matching fields of any existing index. Or ABAP SQL where-clause(selection criteria) has one or more index fields but during the execution, the program passes no data to those index fields. Please notice that field sequence in where clause makes no difference on execution plan for Oracle CBO engine.

To tune SAP program performance in this case, possible solution can be:

  • Use alternative table for the information.
  • Change fields of where-clause to match existing index.
  • Change program to make sure that data is passed to index field(s) in where-clause.

2. Index is used but not correct.

This could be due to complex where-clause in SQL statement. I am not talking about wrong index choice related to table or system statistics or database setting, which requires no ABAP effort to tune SAP program performance.

To tune SAP program performance, you need to simplify the where-clause. Or use corresponding database hints in ABAP code to influence database CBO index choice for this particular SQL.

3. Index is used but other indexes might be better.

This could be due to the fact that table has several indexes and those indexes have common fields where it is referenced in SQL statement.

You need to use corresponding database hints to tell database that what index should be used, you can review and change table index design or change where-clause to let database to use preferred index.

4. Index is used but index itself is not selective.

This is normally due to index design. This happens usually to local table or local developed index.

To fix this, you need to redesign the index using “selective” table fields and arrange index fields properly with consideration of query request and clustering factor. What is selectivity of a field? It is a measurement against two data: Number of distinctive value for a field and number of record in that table. The higher number of distinctive value for a field, the more selective the field is.

Also selectivity of index and selectivity of where-clause might not be necessary the same. Sometimes, an index is not selective but the where clause can be very selective – data histogram can play a difference. For example, if a table has 1,000 records, one field has only two distinctive A and B. If B has 999 entries and A has only 1 entry, then it is very selective to select entry from the table with condition of values equal to “A” assuming the field is the only field of an index. Querying the table using “B” value is not selective.

Now I would like to mention “estimated cost” in the execution plan, the higher it is, the more expensive the SQL is. For CBO (Cost Based Optimizer), the database server always uses an execution plan which has a lower “COST”. But Lower cost does not mean an index is always better than another index. Discussion on this is not focus of this posting.

1.2.2 Review identical access

This is to understand why there are identical accesses in “Summarized SQL Statements” to see whether we can eliminate identical access to tune SAP program performance. You review identical access by reviewing the SQL source code and SQL statement in the execution plan. It is possible one SQL statement in “Summarized SQL Statements” screen can mapped to more than one ABAP SQL statements at different program code location.

1. More than one SQL statements from the ABAP program are mapped to the SQL statement.

2. Only one SQL statement from the ABAP program is mapped to the SQL statement.

To tune SAP program performance, you can consider following options

  • Consolidate the similar SQL statements. If you need two fields from a record at different program location, it is better to retrieve two fields at one location instead of doing it separately at different program location from program performance point view.
  • Move SQL statement to more appropriate location – like move it outside of loop, put it at document head-level instead of line-item level and implement at expected higher organization level like company instead of repeating it at every lower organization level like plant.
  • Use program level buffer – retrieve data then store it for later reference in the same transaction to avoid database table access. There are several SAP ABAP techniques to achieve this.
  • For SELECT-FOR-ALL SQL statement, you can sort the internal table and remove “duplicated records” before the internal table is used in the SQL statement to retrieve data from a table.

Identical access sometimes can represent a significant performance tuning opportunity – because related program unit (or business function ) might only need to be executed once but executed many times due to improper design or coding. In Figure-1, if we assume that time for each access is same, we can make about 58% performance tuning on 2nd SQL by just removing those identical access.

1.2.3 Review Table buffer strategy

This is to review table buffer status based on “Summarized SQL Statements” screen to see whether we can enable buffer or deactivate buffer to tune SAP program performance. You also can review whether the SQL statement has bypassed SAP buffer based on code and whether the business logic needs to bypass SAP buffer.

Which SQL statement would bypass SAP buffer? Following is the list:

  • Any SQL select statement with a keyword “bypassing buffer”.
  • Any Select with a sub query or with joins
  • Any “count” function ( Count, Min, MAX, SUM, AVG)
  • Group by
  • Select DISTINCT
  • Order By
  • Select for update

Click here for SAP document and examples on ABAP SQL statements bypassing buffer.

If SAP program should not bypass buffer when it access table, then you need to change SAP SQL code so data requested is from application buffer instead of database table.

Only SAP configuration table and parameter table are appropriate candidates for buffering if the table is “seldom” changed and has an “appropriate” size. What is “seldom” and what is “appropriate” table size, there is really no hard cut-off line. The general guideline is we can buffer record if record change rate is under 1% and table size does not exceed 5MB. You might need to select “right” buffer mode based on table size, changes and type of query.

If a buffered table is now changed more frequently, this can impact your program performance as well due to buffer synchronization. In this case, you need to disable the buffer.

I might write a post to share my experience on dealing with table buffering. You can click SAP table buffering to know more in this area.

1.2.4 Review number of SQL execution

Here we review Number of execution of SQL in the summary screen to see whether we can tune SAP program performance by reducing number of execution on the SQL. Further by reviewing number of execution, it might be discovered that a subroutine might be executed more than what is really needed! To reduce number of SQL execution, you can consider similar solutions mentioned in 1.2.2 section of this post.

If there are individual Select, “Insert” or “update” on a table, it would help to tune program performance by using mass operation like mass insert and mass update.

1.2.5 Review data returned

This is to review what information each record contains and how many records are returned by the database server to ensure that only data needed by business operation is returned from database not more and not less. Eliminating un-needed fields of each record can result new execution plan like getting data only from an index and reduce round trips between application server and database servers. Reducing number of records returned by database by building more specific retrieval condition into SQL where-clause is better than retrieving records from table then filtering records at program level.

1.2.6 Review table data volume management strategy

Number of entry in a table has a big impact on database time used to search a record when the table primary key is not being used to search a record. It is clear that index or not index would make no performance difference if the table has only several records in the same way that using a calculator or not make no time difference to calculate 1+1. So we need to understand how long a data should be kept in a table to meet business requirement and how long the data is actually kept in the table. If this understanding would result in “significant number” of records being removed from the table, then the SAP program performance would be improved greatly even there is no code change.

Last but not least, you also can review top expensive SQL statement to see whether they are related and can be combined SQL on different tables via join and/or database view – this can help on the SAP program performance as well.

2 Clarification

When I talk about improving SAP program performance, I mainly cover this from application side on how we can improve ABAP program code (ABAP and SQL) and ABAP program/business solution design.

ST12 has combined features from SAP transaction SE30( ABAP trace ) and ST05(SQL trace). So what I stated here is applicable to ST05 trace(SQL) analysis.

Whether you should analyze both ABAP and SQL traces or just one of two traces, this depends on your situation. If your program spends 99% of time on database side, then you should focus on SQL performance analysis to tune SAP program performance.

I am not talking about ABAP program performance from system/network point view like table statistics, table/index storage status(fragment, allocation) etc. So you need to make sure that performance issue is not due to system(Capacity, OS, DBMS, IO, Network etc). Please refer to performance introduction for details on what influences program performance. If all other programs are working fine in a system except your program and performance of your program is “consistent”, this can normally means a code/design issue. If your program performance is becoming worse due to volume increase over a long period, the performance issue should be related to design and/or code of the program. There are “general” criteria to say whether database/storage performance can be a concern.

I am not talking on how to deal with a one-time SAP program performance incident where program runtime is deviated greatly from normal range –That is a performance incident trouble-shooting.

Last not least , you might need to search SAP OSS note for possible solution especially when a standard SAP program has performance issue which is not due to system resource or database decision(like Oracle CBO etc).