All posts by eric

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.

How to run SAP code inspector to do static ABAP performance check?

In my post “SAP project – Application Development Performance Control”, I mentioned we can validate SAP ABAP program performance via static performance check and dynamic performance testing. Static performance check and Dynamic Performance testing complements each other to make sure that program developed has good performance.  It is mentioned that we can use SAP code inspector (SAP transaction – SCI) to do static performance check on SAP ABAP program.   SAP code inspector can scan through an ABAP program source code line by line to identify common performance pitfalls.  In this post, I would share how to run SAP code inspector to do this. This post would cover.

  • .     Quick start on how to run SAP code inspector to do static code view,
  •     Details of steps of running SAP code inspector,
  •     Advanced way of running SAP SCI and
  •     Further info on SAP SCI, SQLM and SWLT tools.

1. Quick start on how to run SAP code inspector to do static code view on Single Object

  1.    Run SAP code inspector transaction SCI in any SAP window,

 2.   Go to “Create” inspection function by clicking “create” inspection button on the initial screen of code inspector,

 3.   Specify Single ABAP object or Single transport and a list of performance check attributes on the 2nd screen of code inspector,

4.     Execute the inspection by clicking  “execution button” on the 2nd screen,

5.     Review the inspection results.

2. Details of steps of running SAP code inspector

2.1 Run SAP code inspector transaction SCI.

To start SAP code inspector, I typed in SAP transaction code “/nSCI” in the command window of my SAP screen (as showed in following screen) and  hit “return” key to start the SCI transaction.

Figure 1 Start SAP inspector

2.2 Go to “Create” inspection function

Following the hitting “return” key in 2.1 step, initial screen of SAP SCI appeared.

Figure 2 SAP Code Inspector Initial screen

I clicked “create” button in Figure 2, then following screen appeared.

Figure 3 SAP code inspector – specification screen

Assuming that this was the very first time I run SCI in the system, I do not have any “check variant” created so I need to specify my check options manually by checking related boxes in Figure 3.

2.3 Specify Single ABAP object or Single transport and a list of performance check attributes

I need to do static performance check on an ABAP program. So I entered following data and unchecked “security check” options except “Performance check” in Figure 3 and I expanded “Performance checks” to show a list of performance items checked as Figure 4.

Figure 4 SAP code inspector – check on single ABAP object

2.4 Execute the check

I was glad with my input, then I clicked “execution” button( red circled in Figure 4)  to let the code inspector to do static performance check.

2.5 Review result of SAP code inspection

Upon completion of SCI execution in above step, SAP Code inspector displayed a result screen showed as in Figure 5.

Figure 5 SAP code inspector – checking result

SAP classifies the checking result into 3 classes – error, warning and information. SAP provides several features to help you navigate through the result screen – this is helpful many objects are collectively checked with many errors and warning.

In figure 5, static performance check on the code find no error but 4 warning.  To see details, I clicked “result list” button and I got following screen (Figure 6).

Figure 6 SAP code inspector – result list

I double clicked the program line in Figure 6 to see the corresponding ABAP source line related to  “Nested loops”

Figure 7 SAP code inspector – Detection of Nested Loop

With above information, I can discuss with ABAP developer on the concern and reach the decision on whether this should be fixed or exempted before the change can be moved to production.

So far, what I showed is a quick start to use SAP code inspector to do static performance check on a single ABAP object and review the result. This is good enough for one time check on single object (ABAP or transport request). But there are several limitations/disadvantages to run SCI this way:

  • SCI result would not be saved by SAP. You have to download the SCI result manually before you quite from SAP SCI transaction if you would like to keep the result.
  • You can check only single transport or single ABAP object during each execution,
  • You have to enter object name each time you need to check the ABAP object and
  • You have to specify the list of checks every time manually when you execute the SCI.  You can use existing check variant but you have no control on the check variant which you are not responsible for.

If I would like to refer to the static performance check result in the SAP SCI in the future, or I need another person to review the check result via SAP SCI, or I need to check a lot of objects, or if I always do the same set of checks and hate to click those options one by one every time, or is there any option to execute the check in background to avoid timeout or make execution faster when I have a lots of objects to check? Can SAP SCI allow me to do so? Yes, I called it advanced way of running SAP SCI.

3. Advanced way of running SAP SCI

SAP SCI provides several ways for you to define an object set which contains the ABAP objects to be checked. SAP SCI allows you to define a check variant to contain a list of check attribute you like to have so you can use it for similar check again and again. SAP SCI can save the inspection result if you give it a name during execution. SAP SCI provides “background” options to avoid timeout and “parallel execution” option to speed up the execution.

3.1 Define a check variant for regular checks you do normally

Most of time, I use SCI to do performance check and I normally focus on reading table without index, nested-loop, by-passing SAP buffer etc. I would like to create a SAP SCI check variant to contain the list of performance checks I need so I do not enter them every time in the future. 3 steps:

  1.        Name your check variant,
  2.     Select a list of checks you need and
  3.     Save your variant

I named my variant “ERIC_PERF_DB” (free text for all names in SAP SCI), then I clicked “create” button highlighted in Figure 8.

Figure 8 SAP SCI – name check variant

Following clicking on the “create” button, following screen showed up with a list of checks which I can choose from.

Figure 9 SAP SCI – a list of available check

If you would like to understand more about each checking category, you can click the Info button, SAP would pop up a screen to tell you what the check category is for.

Since I am interested in performance only, I checked following choices by clicking the white square “boxes”.

Figure 10 SAP SCI – choose check category and associated check attribute

I saved the “ERIC_PERF_DB” variant by clicking the “Save” button. You can see that my variant is to focus on SQL statements and targeted for programs which mainly spends runtime on database access. For such programs, it makes no sense for SAP SCI to check “low performance operations on internal tables” etc…

You can display, change, copy and delete an existing variant by clicking correspond buttons in the “check variant” section of SAP SCI initial screen.

3.2 Define a object set for SAP SCI check

If you would like to check a lot of objects repeatedly, it is tedious to enter each object every time you run SAP SCI transaction. If there are many objects in a package or in a function group etc. it is tedious to list each object in a package or a function group etc.  SAP SCI provides several ways to allow you to specify a set of objects to be checked together by SAP SCI transaction. Following are steps to define the object set.

  1.  1.    Name your object set,
  2.       Enter your object sets directly or adopt objects from existing object sets created by you or other people earlier you and
  3.        Save your object set.

I named my variant “ERIC _DB_EXTENSIVE_REPORTS”, then I clicked “create” button highlighted in Figure 11.

Figure 11 SAP SCI – name Object set

Following clicking on the “create” button, following screen (Figure 12) showed up with several tabs to give you a lot of options to allow you efficiently to specify objects.

Figure 12 SAP SCI – options for specifying check objects

Here, I am only going to check several programs. So I uncheck all options first, then I checked “Program” Options and click “arrow” button highlighted in Figure 12 to enter a list of objects.

Figure 13 SAP SCI – Sample ABAP objects

Figure 13 is a list of sample objects I specified, then click “continue” button, and SAP returned me to the Object set screen below.

Figure 14 SAP SCI – Objects specified

At last, I clicked “Save” button circled in red color in above Figure 14 to save my object set “ERIC _DB_EXTENSIVE_REPORTS”.

So far, I have defined check variant “ERIC_PERF_DB” and Object set ““ERIC _DB_EXTENSIVE_REPORTS” using SAP code inspector. It is time to run SAP SCI to do the needed checks specified in check variant against those objects through the defined object set.

3.3 Create SAP SCI inspection with a name for future reference

If you enter a “name” for SCI inspection, the SCI static checking result would be saved in SAP database upon completion so this can be reviewed using the name via SCI in the future.  When you have a big object set and SAP SCI returns a long list of result. You might need more than one person to review the SCI result as well. So naming an inspection has at least following benefits

  1.        Save the result for the future reference,
  2.     Separate SCI execution and result reviewing,
  3.     Allow several people to work on the long list of SAP inspection result.

I named my SCI static check or Inspection as “ERIC_CHECK_DB_EXPENSIVE_REP”, then I click create button highlighted in Figure 15

Figure 15 SAP SCI – advanced usage ‘name the inspection’

Following the click, I got following Inspection screen where I used option “object set” and entered the set I created earlier “ERIC_DB_EXTENSIVE_REPORTS” and Selected “ERIC_PERF_DB” check variant as showed in Figure 16.  You can use pull-down button to choose your options from a list.

Figure 16 SAP SCI – using object set and check variant

With specified “object set” and designated “check variant”, I click “execution” button circled in red in Figure 16 to kick off the inspection. After SAP code inspector finished checking/inspection, I got following result screen.

Figure 17 SAP SCI – result screen for named inspection

In Figure 17, you can click “button” circled in red color to review the inspection result. You can filter the result and sort result as well via buttons showed in Figure 17. The green button before the name of inspection indicates that SCI is executed successfully for the inspection.

3.4 Execute SAP SCI in background or using parallel solution

When there is a lot of objects to be inspected, the execution can be long. SAP SCI provides “execution options” to allow you to execute it online or execute it in background using parallel processing.

Figure 18 SAP SCI – execution options

I clicked the button circled in red in Figure 18.  I need to run the inspection in background job and via parallel solution controlled by the server group defined by RZ12, so I specified input based on my environment as showed in Figure 19.

Figure 19 SAP SCI – selected execution option

Number in red in Figure 19 indicates sequence of operations in executing the inspection via options.  Figure 20 shows the job name SAP SCI created in this case. The job name convention = CODEINSP_ + Inspection name + _Version.

Figure 20 SAP SCI – background job for inspection

Here, Server group and parallel processing is provided as an option to do static performance check. If you are interested, you can refer to my post on how to create SAP server group via RZ12 and refer to my post on general introduction on SAP parallel processing.

4. Further info on SCI, SQLM and SWLT

You properly noticed that static performance check on ABAP code is just one of checks offered by SAP code inspector. You can do other checks in the same fashion I mentioned here for performance check. SAP code inspector is focusing coding technique not logic. So it would not tell you logic/design issue – like you execute a operation at sale order line item level while the operation only need to execute once for one sale order.

ABAP code review for static check can be done manually but manual check has many disadvantages like cost, inconsistent result and painful.

SAP code inspector is integrated with SAP object creation and editor tool like SE11 – data dictionary, SE38 (ABAP program editor), SE37, SE24 (class builder) and etc. As a developer, you can call SAP code inspector in the tool to do static check directly. Such check is done via “Default” check variant defined by the developer. For example, SAP developer can invoke the code inspector to do static check from the same SE38 screen where code changes are made.

SAP code inspector is doing a static check based on a predefined set of criteria. It has no information on actual usage of ABAP code in production environment or how big a table is… Due to business changes, a code which is executed frequently might be obsoleted over time.  You do not spend effort on an  obsoleted codes due to code inspector complaining … for that purpose, SAP has introduced another tool SWLT to integrated code inspector result with SQL usage information from SQL monitor which might help drive priority of fixes some times.  In my understanding, SAP SWLT and SAP SQLM is targeted for Hana migration and Hana environment. I would cover SAP tool SWLT and SQL monitor in the future.

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).

Why would SAP job runtime suddenly jump well above normal range?

Business was complaining that runtime of a periodic SAP background job was suddenly jumped in production environment. There is no code change and no volume change and other jobs and transactions are running well. They were asking why this happened. I was consulted and looked into the situation. Finding the issue was related to Oracle execution plan change – Oracle chose a suboptimal execution plan. The plan changes was triggered by a regular table statistics update. Job run time was back to normal after plan was switched to better one following restoring previous table statistics. This blog would focus on how I trouble this particular SAP job performance issue. It would talk:

  • The performance issue – Job is running up to 10+ times longer than it used to be,
  • The performance trouble-shooting – why this job is long running?
  • Going deeper – Why is the execution plan not efficient?
  • Fix performance issue – action and result,
  • What we learn from this case? and
  • Further clarification.

Please continue read should you be interested in details.

1 The performance issue – Job is running up to 10+ times longer than it used to be

A job which runs every 15 minutes used to take less than 300 seconds finish was taking at least 600 seconds and up to 2,300 seconds to complete. Please refer to following screen shot to get more understanding on our issue.

Figure 1 SM37 job log – runtime history

Figure 1 shows that job run time had a jump since 00:15:02 on Apr 24 2015. There is no code change, no volume change and system has no resource contention… Why would the job run time have such a huge jump?

2 The performance trouble-shooting – why is this job long running now?

Here, performance trace was done on the job to find out where the time is spent by the program. The performance trace shows that job spent most of time on executing one select-SQL statement, and SQL summary for the trace indicates that number of records returned by the SQL is not high, this is unlikely a volume issue, so I examined the SQL execution plan – identify the hash join used by Oracle might be a bad choice, and checked SQL execution plan history and found that the timing of execution plan change is collated to timing when job started to have performance issue. Further I found that table statistics of one of the underlying table was updated immediately before the moment when the execution plan was changed. With that, I concluded that job runtime was due to table statistics update which led to reparse the SQL and Oracle changed SQL execution plan and used a suboptimal execution plan.

2.1 Where is time spent by the long running job?

Performance trace done showed that job was spending 99.9% of run time on database operations as below.

Figure 2 ST12 ABAP trace – why would job runtime jump

99.6% of time is on single SQL statement “Select VTTP” based on above trace. So this SQL is responsible for job long run time. Why was execution of this SQL taking so long ? ( Tried 3 times, can not upload my figure 2…)

2.2 Why is this SQL execution running long?

Is this due to inefficient index or due to a lot of database entries are retrieved? We need to check that with SQL trace. Following is SQL summary from SQL trace.

Figure 3 SQL summary – why would job runtime jump

From above trace, we know number of records fetched from the database by the top expensive SQL is “0”. So it is not due to a lot of records are fetched and the SQL is executed once. Is it due to wrong index used etc.?

Now let’s review the execution plan…

Figure 4 sql execution plan – why would job runtime jump

The index used looks ok in Figure 4…but when I compared number of records (0) returned by the SQL in Figure 3 and number of records(7,971) projected by Oracle during plan generation, I smell something wrong… This looks like an Oracle execution plan issue. Since this job was working well, this must be due to recent Oracle execution plan changes of this particular SQL. Is that true?

2.3 Was the SQL long running due to oracle execution plan changes?


Using Program /sdf/RSORADLD_NEW( there are SQL Scripts available for this purpose), I got SQL plan history as Figure 5 (right side).

Figure 5 TIming of job runtime change and timing of execution plan changes

Figure 5 shows that timing when execution plan was refreshed and timing when the job started to run long are closely related. It could be that Oracle reparsed the SQL statement and refreshed the sql execution prior to the sql was executed. But Figure 5 does not tell what the execution plan was used to execute the job prior to Apr 24 2015.

Following load history data shows that the execution of this SQL were captured on Apr 24

Figure 6 SQL load history – why would job run time jump

The job is executed every 15 minutes and every day. Yet the load history of this particular SQL was only captured on Apr 24. In our SAP system, we follow normal approach – only top expensive SQLs are captured into the snap-shot. So this means that this particular SQL was NOT one of top expensive SQLs prior to Apr 24.

2.4 Why would Oracle plan change execution plan?

Following screen shows that table VTTK statistics was updated at 00:08 on Apr 24, 2015. That timing is exactly linked to the timing when the execution plan was changed.

Figure 7 table statistics update – why would job runtime jump


There are other reasons which can lead to refreshing of Oracle execution plan. But this case, the execution plan refreshing is a result of Oracle table statistics update. And refreshing of Oracle execution plan led to a suboptimal plan in this case – leading to job runtime jump.

2.5 Conclusion on why job is long running

Job was running long after 00:15 on Apr 24 2015 was due to the fact that job is spending significant more time on executing one Select-SQL statement. Execution plan of this particular SQL was changed/refreshed at 00:16:53 in the same day. VTTK table statistics was updated a few minutes earlier at 00:08 in the same day, Table statistics update led to reparse related SQL statement – that is a typical Oracle response. New execution plan as a result of statistics update is not efficient as the original one. That made job run longer.

So why is the new plan not good and what is the better plan? I would try to take a closer look on the execution plan.

3 Going deeper – why is the execution plan not efficient?

In this section, I use sample values for the binding variables, number of table entries and st04 SQL cache data to validate the SQL execution plan which is in question.

In previous section, it is noticed that hash plan is used in several places. Hash operation is normally for data operation on two dataset when a lot of records are returned. Let’s take a peek on Oracle binding variable used in this SQL

Figure 8 Value for Oracle binding variable – why would job runtime jump

A4 is field vttk-shtyp and A5 is vttk-fbgst, following are total entries of VTTK and entry which is meeting selection criteria (Figure 7). Review table definition, FBGST is a field to store overall status of shipment and shipment stage. Value “A” for that field stands for “not processed” status. So the job looks like only interested in “new” shipment. Based on load history and SQL cache, we know that execution of this SQL returned “0” records most of times.

Figure 9 SQL cache – why would job runtime jump

Following screen shots indicates number of records from VTTK table which is meeting the selection criteria..

Figure 10 table size and number of records – why would job runtime jump


In the current execution plan, since hash operation is used, Oracle would prepare two dataset first prior to apply hash operation. When one data set has no or small number of records and the other data set can be read via efficient index, nested-loop operation is more efficient than hash join. So hash join operation at Step 7 in Figure 7 is not efficient as nested-loop. I did check the execution plan of this SQL in our testing environment. The plan was different from our production box. I put both plan side by side as Figure 11.

The plan in the testing box is better, when no records is returned from hash operation using VTTK and VTTP, the reading on lips would not executed via Nested loops operation.. so reading LIPS is spared.

Figure 11 SQL execution plan comparison – why would job runtime jump

Execution plan major difference is highlighted between test box and production box. It is mainly at step 4 where testing box is using nested loop while production box is using hash join. Due to hash join, production has to prepare datasets from LIPS and VTTP tables via another hash operation which is expensive based on selection criteria used and table size. While in the nested loop, step 9 and would be spared when there is no records. You might agree with me, If oracle replaces all Hash operation with nested-loop here, the performance could be even better.

4 Fix the performance issue – action and result

The table statistics was restored to the version prior to the problematic updating… after that, the execution plan in our production box is changed to the plan we are seeing in our testing box. The job performance is back to normalJ

Figure 12 Job runtime back to normal with restored execution plan

Figure 13 plan history after restoring statistics

Plan history shows that restoring statistics changed production plan to the same as what we are seeing in test box.

5 What do we learn from this case?

An unexpected SQL execution plan change could be very likely the reason that a job runtime jumps especially when following conditions meet

  • Other jobs or programs are running as normal.
  • Job spends most of time on database side or database time has a significant increase – STAD or ST03N,
  • Most of database time spent by the job is due to database read operation – STAD
  • There is no code change or job variant or volume change.

You can validate whether there is a plan change by checking plan history and

  • Check whether timing of plan change matches the moment when job runtime starts to jump

Oracle regenerate SQL execution plan when underling table statistics is updated. Table statistics updating is the typical reason which leads to an execution plan refreshing. You can validate the timing of statistics update and timing of execution plan change. SAP application performance issue related to table statistics change can be fixed by playing with table statistics to influence SQL execution plan.

6 Further clarification

Oracle SQL plan management can prevent unexpected plan changes to avoid SAP application performance issue due to accidental execution plan change. Many factors can influence a job run time like inappropriate code change, lock contention with other jobs etc.

In this particular case, a performance trace was done to understand the performance issue. But actually, there is no need to do performance trace to fix the performance issue. SAP tool STAD, ST04 and tools based on Oracle AWR should be enough to trouble-shoot this type of performance issue. I am writing another post on how to use SAP STAD. In that post, I would give some insight on how to get this doneJ

It would be nice for us to know the execution plan of this particular SQL used to execute the job before Apr 24 2015. Apparently this was not possible in our system. ST04 and Oracle AWR tools in our SAP system does not show us complete plan history. Oracle plan history table does not contain data to show us what the plan was before the issue. What could be the reasons? At this moment, I am guessing this might be related to our setting on how many SQL should be captured into AWR. Not quite sure of that.