All posts by eric

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)

68

278

48

User I/O time (Sec)

64

274

44

Disc Read

112,830

75,099

106,883

Row processed

1.2

1.5

1.6

 

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

SQL_HANDLE..

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.

Figure 1 SAP SM50 – WORK PROCESS IN “ON HOLD” STATUS WITH “RFC”

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.

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

Button

Related statistics

Time

Time profile of transaction.

DB

Database time breakdown according to type of database operations.

DB Procedure

Details on procedures calls to LiveCache.

Task/Memory

Show memory usage at the end of step.

Table

Database time – table profiles.

RFC

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

Bytes

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.

Fields

Explanation

Connections

Number of connections between client and server system.

Destination

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.

Users

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

Calls

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.

Idle

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

Sent

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

Received

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.