Category Archives: SAP support

sap production operation performance support

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

What types of SAP performance analysis can we do using SAP ST03N transaction?

In my previous post, I talked about how to do navigate through various analysis views of SAP ST03N transaction. In this post, I would give an introduction on typical types of performance analyses which I have used SAP ST03N for in my work.

  1. Who is placing workload into a SAP system and when/where/how is SAP workload generated?
  2. What is SAP system/application performance trend?
  3. Are there any potential resource bottle in processing workload?
  4. Trouble-shooting SAP performance incident.

1 SAP system workload – who, who, where and how

SAP ST03N cam tell you – who are generating workload in a SAP system, when/where/how are those loads generated in the system for the selected period – last minutes, current period (current day/week/month) and past period( a day, a week or a month). Where here means which SAP instance when a SAP system has multiple instances. Using those information, you can do a lot of performance analysis.

1.1 SAP workload distribution among different SAP tasks

In a SAP system, there are different tasks – Dialog, Background, RFC, ALE, Update task etc. If you are wondering how significant each task is in terms of CPU utilization? ST03N can answer such question. You can pull data from SAP ST03N workload Overview and visualize SAP workload distribution among different tasks via a chart similar to following Figure 1. The pie chart reflects load distribution of a SAP S2C production system.

Figure 1 SAP system application server CPU utilization distribution

The above chart clearly indicates that who (background process) accounts for 60% of CPU utilization in the elected period. In the similar fashion, you can get chart to see database time distribution among different tasks. So you can quantify level of resource which is consumed by each of SAP SAP tasks. This type of information is available at system level or server/instance level in the selected period.

1.2 SAP workload distribution among different SAP instances/servers

In a SAP system which you have multiple instances or servers, you can use ST03N to find out what is their share of processing SAP workload. You can pull data from ST03N Instance Comparison view and visualize SAP workload distribution among different SAP servers/instances via a chart similar to following Figure 2.

Figure 2 SAP workload distribution among SAP Servers

Figure 2 clearly shows SAP workload distribution among different SAP instances. So you can see whether there is a load balance issue overall in the elected period. It is expected that every server in a system should process similar amount of workload assuming all servers has identical capacity and configuration. Figure 2 shows Server 2 is handling significant more load than other servers. So next step of analysis is to see understand SAP tasks and their CPU consumption in server 2.

1.3 SAP workload distribution among different hours

If you are wondering how workload is distributed hourly in a 24-hours window, SAP ST03N time profile can help you. This can tell you overall when the system is most busy and when the system is not. This might be helpful if you need to find a time window to let your system to handle some unplanned workload or you would like to check whether you can move an unnecessary peak load to an off peak period to protect SAP performance.

Figure 3 SAP workload – hourly distribution

SAP ST03 or ST03N provide workload hourly distribution on three intervals – daily, weekly and monthly period. This would give you a better picture on SAP workload distribution.

Level of workload in a SAP system is related with business operation which is often dynamic and keeping changes, so it is normal that workload would vary from hour to hour. However if type of workload analysis detect that there is a clear pattern – for example, one particular server at one particular hour is always having much higher workload than other hours and other server, then it worth next step to look into this in details to see whether we easy the workload “hot” spot.

1.4 SAP workload distribution among different transactions and programs

Based on data from SAP ST03N EarlyWatch profile, you can know how system load is distributed among different sap transactions and programs and know what are most expensive transactions and programs in your system.

Figure 4 SAP workload distribution among different transactions and programs

This would be value input to help to choose which transaction we should focus on for general performance tuning. SAP ST03N can help you to find out whether a transaction/program has been ever executed in the interested period.

This type of SAP workload analysis can show you a list of top work load contributors. This should help you if you would like to identify a list of a programs/jobs for performance tuning.

1.5 SAP workload distribution among different sap users

Based on data from SAP ST03N User profile, you can visualize top expensive workload users and produce chart like Figure 5.

Figure 5 SAP workload – CPU time distribution among users

This would provide workload distribution at SAP user level regardless of individual business process and application. You need to review this type of data if you suspect that particular user might occurs more load than it should…

SAP ST03N can help you to find out whether a user is “active” in the interested period and what transactions and programs are executed by a SAP user in an expected time window.

1.6 RFC work load and load from external analysis

SAP ST03N provides detailed data to show you what programs/transactions are generating RFC load in the system, what users are generating RFC load in , what are those external systems which are placing RFC load in the system. So if RFC load is a significant portion of your system and you would like to analyze this, you can use data from SAP ST03N RFC profiles and “Load from External Systems” view to do further analysis to identify top contributors. Based on that, you can decide whether there is a need to take further action like performance trace and analysis etc.

Figure 6 SAP workload From External System

1.7 Load profile of individual SAP transaction/report

Performance tuning is normally to cut time, so it is important to know where time is spent by a transaction/program/job. SAP ST03N can show where time is spent by a transaction/program/job – it is spent on most of time on application server side – ABAP logic operation, spent on database operation etc. Please pay attention that this is an average data for the specified period you choose when you run ST03N. In the period, the transaction/program might be executed many times. So it might be different from time statistics of a specific execution.

Figure 7 SAP workload – transaction time statistics

Data similar to Figure 7 gives a general direction on where you should focus on tuning application performance. If a program spends 99% of time on CPU, you might need to focus on the ABAP logic instead of tuning SQL statement of this program. In Figure 7,1,024 ms out of 1,355 average response time is spent on GUI side. Since VA03 is a sales order display transaction, this could indicate a network issue or front-end issue if user is complaining of performance issue with VA03 which is normally performing normal.

2 SAP system performance trend – system, transaction

If you wonder how system performance/application performance evolves over time or you wonder how a SAP system is performing after a significant change to the SAP system like upgrading, migration, added resource, SAP ST03N can help you with this.

2.1 What is performance trend of a system

You can compare average response time per transaction step for main task type in several periods to see overall system performance trend.

Figure 8 SAP system performance trend after migration

Figure 8 is a true production data after an ECC system migration. In this particular case, the corresponding SAP system is moved from Itanium/HPUX to x86/Linux Server. Based on Figure 8, a performance improvement are seeing across all major SAP tasks after X86 migration. Correspondingly, all key business jobs we checked in our SAP environment are showing a significant performance improvement after migration. When SAP system performance trend analysis shows a trend of deterioration, then further review on runtime’s component like CPU time, database time etc. are needed to identify further analysis action.

2.2 What is performance trend of database system?

Average time taken for database access – like sequential read, direct read and change operation etc. can be a meaningful indicator for overall database performance. You can visualize database operation by producing chart to similar Figure 9.

Figure 9 ST03N workload – database operation performance

Figure 9 is a true example to show the SAP MDS operation’s impact on background task in our production in our X86 migration project. We were seeing slowness in many transactions and programs in MDS drill 1 period. One particular process was so badly impacted resulting in missing service level agreement due to implementation of SAP MDS migration solution. For more details, you can refer to my post on SAP MDS Migration and system performance.

Need to know more? check book – Expert Oracle Database Architecture

2.3 What is performance trend of a transaction/program/job?

Transaction profile view of SAP ST03N shows average response time per transaction step in 3 type of interval – daily, weekly and monthly. You can review average response time per transaction step for a specific transaction over a long period. This could give you an indication of SAP transaction/program/job performance evolution – could be stable, or be running longer and longer, be varied but within a specific scope etc. If program is running longer and longer, then action might be needed to see what drives growth of runtime.

Figure 10 SAP workload – performance trend of individual transaction/program

Resource bottleneck analysis

3 Resource bottleneck analysis

3.1 SAP work process utilization analysis

Several views of SAP workload monitor ST03N contain a column named as “Average wait time per dialog step”. Following screen shows is from ST03N workload overview

You can check wait time at system and instance level for a specific day, week and year. You also can check “wait time” in a 24-hours fashion. Normally, wait time should be around a few millisecond. Wait time is to measure how long a job or transaction has to wait until the system can find a free SAP work process to run it. So consistent high wait time can indicate that no enough SAP work processes is configured in system or there is no enough CPU or application has occupied work processes too long due to application design if no issues like storage, network, database issue etc. put SAP work process into “abnormal” or “error” status. You can refer to my post on
SAP work process monitor
for more information on status of a SAP work process.

A great book on SAP performance – SAP Performance Optimization Guide: Analyzing and Tuning SAP Systems, SAP Basis, SAP Administration

3.2 Other resource bottle-neck analysis

SAP workload monitor calculates “Average Processing Time” and “Average CPU time” per transaction step. This can be used to monitor “wait” situation – such as wait for free system resource – CPU, SAP memory, programmed wait/sleep due to data object lock, wait for establishing communication channel e, wait for completion of RFC etc.

CPU time is consumed during the processing time. So length of “Avg. CPU time” is related to length of “Avg. Proc. Time”. If a transaction is making “no RFC” call and no program coded “sleep” or “wait” statement, then “Avg. Proc. Time” should be closer to CPU time and should be less than 2 times of “CPU time”. If this relation is broken, then this indicates a shortage of CPU normally.

SAP ST06 can be used for CPU load analysis as well, you can check my post – How to use SAP transaction ST06 for SAP performance analysis for details.

4 Performance incident analysis

SAP ST03N can be used to trouble-shoot system/application performance issue by comparing system/application performance:

  • between the period when performance is bad and other periods when the performance is good.
  • between the user(s) who is complaining of bad performance and who is having good performance.

This performance comparison can help you to pinpoint why a program/transaction is having performance issue or help you to identify the direction of performance trouble-shooting.

Our ECC system is feeding SAP “BW” system. Once there were many idocs piled up in ECC side waiting to be sent to BW system. Using ST03N, comparing the period when performance was good with the period when performance was bad, it was found that BW side was slow in loading ECC IDOCs. Further investigation found that this was due to Oracle compression bug.

5 Further information

This post is mainly from my understanding and my work experience. It is impossible for me to list all types of performance analysis we can do with SAP ST03N… Someone might find response time distribution view of ST03n is useful, one of SAP system performance key indicator is to measure percentage of dialog step which are able to complete in single seconds. My next post on SAP ST03N is to talk about ST03N RFC profiles, for example, how to understand information of various ST03N RFC profiles.

.

Frequent operations On SAP tables in SAP performance analysis

In performance analysis, we often use different sap transactions to get information related to a SAP table like table attribute, table structure, index, table size, storage quality, column statistics, table content and etc. involving SAP transactions SE11, SE16, SE14, SE13, DB02, DB05, db20, TAANA, RSANAORA, RSORAISQN and /SDF/RSORADLD_NEW. In this post, I would talk about frequent used SAP transactions and operation related to SAP table based on my experience.

  1. SAP SE11 – SAP data object definition

    SE11 can be used to display ABAP dictionary objects definitions like tables (transparent, clustered/pooled table), views, data type, application lock objects and etc.. Here, I would list some frequent checks

1.1 How to check whether a table is buffered or not and type of buffer?

Buffer is one of SAP table technical setting. It is normally for small and seldom changed configuration/parameter tables not for transactional table.

If you have a table and would like to know whether the table is buffered and buffering type, please follow the path : Execute transaction SE11-> Enter table name and then click display “button” -> Click “Technical Settings” button

Figure 1 SAP table technical settings

You can access table technical settings screen (Figure 1) as well via SAP transaction SE13 -> enter table name -> click display key. From SAP table technical setting screen, you know whether data change logs has been enabled for a table.

Search ST10 table call statistics, you can know whether a table is buffered or not.

1.2 How to check table technical setting change history

SAP provides version management for technical settings, so you can know who, when, what changes are made to technical settings by version comparison.

Technical setting version management can be accessed from technical setting screen via menu path: Goto ->Version management.

Figure 2 SAP table Technical settings – change history

In my customer system, we did have a job performance issue due to technical setting changes – turning on buffering option.

SAP has version management for other SE11 changes as well like table structure change history and index change history. Depends on your need, you need to switch to related SAP SE11 screen to see it’s change history.

1.3 How to find out a pool or cluster which a pooled/clustered table belong to?

Run SE11 -> enter name of the pooled/clustered table -> click the display button -> delivery and maintenance tab

Figure 3 SE11 Delivery and maintenance – Pool/cluster name

A001 is a pooled pricing table in Pool “KAPOL”.

1.4 How to find out existing table indexes?

Run SE11 -> enter the name of expected table ->click “display” button -> click “index” button (or menu path Goto -> indexes). When there is no index, the menu path or “Index” button might be disabled by SAP.

Figure 4 SAP SE11 – a list of ABAP indexes for a table

If you would like to know details of a specific index, you can double click the entry.

Figure 5 SAP SE11 an index of a table

Please notice an index defined in SAP ABAP dictionary might not exist at database level. How to know whether an ABAP index exists at database side? You can tell this by the text under “status” field in Figure 5. A text, “Index does not exist in database system”, indicates that the ABAP index does not exist in database system otherwise index exists in database side. Normally an index defined in SAP ABAP level exists in database level.

1.5 How to find where a SAP table is used

Run Se11 -> click radio button “database table” -> Enter table name -> click “where-use list”. Following is a where-use list sample for SAP A871 table.

Figure 6 where-use list of a SAP table

Where-use list is showing all program which is referring to a table in the system. This is different from the questions – what sqls are executed to access a specific table since system is started. This question would be answered bellowed.

 

1.6 How to identify SAP table relationship/linkage

Run SAP SE11 -> Enter table name -> click display -> click “graphic” command in icon bar (ctrl+shift+. SAP has built-in tool to show table relation in graphic

Figure 7 SE11 – SAP Table relationship

I personally find this difficult to navigate. Having no idea on the criteria which SAP used to select tables. There are many online documents on SAP table relationship which is much easier to read. Just google it.

Knowing SAP table relationship is important so you can use right table when same information exists in several table. I have seen cases where inappropriate table is chosen and resulted in poor application performance.

2 SAP SE14 & ST04 –Database object consistence check

Consistency checks whether a table is defined exactly in the same way in both SAP ABAP data dictionary and database schema. If it is the same, then it is consistent. From performance point view, we focus on index definition. In my experience, missing index in database did happen due to different reasons and it did impact SAP job/program performance when needed index is missing.

2.1 How to check individual table consistency?

SAP transaction SE14 is used to check single object consistency. TO check, you run SE14 -> enter table name (make sure radio button for table is select) -> Extras(menu) -> database object ->check. Figure 7 and Figure 8 are two sample screens showing two typical inconsistent scenarios – index in SAP but not in database and index in database but not in SAP.

Figure 8 Table inconsistency – index not in Database

Figure 9 Table consistency check – index in database but not in SAP ABAP

I had seen performance case where an index defined at SAP ABAP level did not exist at database level.

2.2 How to check table/index consistency in a SAP system?

If you would like to know overall picture on table/index consistency picture in a SAP system, you can get Figure 9 screen via path: SAP transaction ST04 -> Diagnostics -> Missing tables and indexes…

Figure 10 SAP consistency check – Missing tables and Indexes

If you have access to SAP transaction DBACockpit or DB02, you can get a report related to data object consistency at database level, if your table is not in this list, then the table is in consistency – database object exists both in ABAP level and database level. If your individual table does not show up in Figure 9, your table is consistent as well.

3 SAP SE16/DB02/DB20 – How to check number of entries in a table?

There are different SAP transactions SE16, DB20 and DB02 which can tell number of table entries. I would recommend to use DB20 or DB02 for such check, they would give a good enough information in this regard with minimal additional system cost.

3.1 Use SAP transaction SE16 to check number of entries

Run SE16 -> enter table -> hit return key -> enter a value for a specific fields -> click “number of entries” button

Figure 11 SE16 – number of SAP table entries

If you do not enter data to any field, you would get total number of table entries. SE16 gives you up-to-date information but it is the most expensive method to get number of table entries and it might take long for a big table since database needs to scan through whole table to count the number of entry.

3.2 Use SAP DB02 to get number of table entries.

Run DB02 -> click Segments ->double click Detailed Analysis -> Enter table name under “Segments/Object” field -> storage tabe

Figure 12 DB02 – number of rows in a SAP table

Figure 12 shows that material master table MARA has 2,667,535 entries. This number might be different from latest number which you got via SE16 depends on when the table statistics is updated and how frequent the table is changed.

3.3 Use SAP DB20 to get number of table entries

Path: Run DB20 -> Enter Table name like “VBAK” in field Table -> click “update info” command.

Figure 13 DB20 – number of SAP table entries

Figure 13 shows table row change activities since last statistics calculation. It shows how many new rows are inserted to the table and how many rows are changes since then.

4 SAP DB05 – How to check number of distinct value and their distribution?

Run SAP transaction DB05 –> Enter table name -> Enter specified fields,

Figure 14 DB05 – distinct column values and their distribution

When you create an index and evaluate an index performance, you often need to know how selective an index field is. DB05 can be used to check number of distinct fields and their distribution

 

5 SAP TAANA – how table entries are exactly distributed across values of specific fields

SAP DB05 can tell number of distinct fields and their distribution of a table field. Figure 14 told you that there are 1 -10 entries for 4 distinct VKORG field. But what are those sales organizations and how many rows/entries for each of sales organization, those questions can be addressed by TAANA transaction.

Figure 15 SAP TAANA – table content analysis

Figure 15 can help to answer questions – why would SQL selection can be executed much faster sometime using the same field. For example, If SOrg is an index field, selecting a specific “US16” entry should be much faster than selecting a specific “US51” entry.

6 ST04 – how to find out all SQL statements executed to access a specific table?

You can use ST04 -> SQL cache -> filter display by table name. But this would be slow. In Oracle environment, there is another way to do this: Run /SDF/RSORADLD_NEW -> click “table name” and enter specific table name under “Restriction Rules” section -> click “execute” button. Please note /SDF/RSORADLD_NEW have many other features. For example, /SDF/RSORADLD_NEW is more efficient to check top expensive SQL operation than ST04 in an Oracle environment.

7 SAP ST05/ST12 – How to find out what tables are accessed by a SAP program?

Similar questions are how to find out where a SAP program/transaction is getting data from and which table are updated.

You can do a SQL trace via SAP transaction ST05/ST12. SQL trace would show what tables are accessed during the program execution.

There is a standard SAP table D010TAB which is showing transactions and their associated tables and structures. A structure showed can be a part of showed table.

You can get which tables are accessed by a particular program via SQL cache analysis tool like ST04 transaction or /sdf/RSORADLD_NEW program.

8 ST10 – how to find out SQL data operation activities on a table?

This is called as table call statistics

Figure 16 ST10 – SAP Table call statistics

9 SAP transaction DB02 – Table and index monitor

SAP DB02 can be used to display many information related to a SAP table and its’ indexes like size, storage quality, number of distinct values for table column, growth history. Those information can be used in program and system performance analysis.

9.1 SAP DB02 – how to check size of a table/index

Path: Run SAP DB02 -> Click “Segments” -> Double Click “Detailed Analysis” -> Enter “table/index name” -> Return.

Figure 17 DB02 – table size

Figure 18 DB02 – Index size

Figure 19 DB02 – table and index size in one screen

9.2 SAP DB02 – how to check index storage quality

Path: DB02 Index size screen -> Click “Storage” -> Click “Storage Quality”.

Figure 20 DB02 – index storage quality

9.3 SAP DB02 – How to check table size growth history

Path: Figure 14 -> click “History” tab.

Figure 21 DB02 – table size growth history

9.4 How to check number of distinct value of a specific SAP table fields

Path: Figure 14 -> Table columns

Figure 22 DB02 – number of distinct values for table fields

10 SAP RSANAORA – How to update table statistics or rebuild index?

You can use SAP program RSANAORA to update table statistics and rebuild table/index in an Oracle Environment. This program offers no parallel solution but it can allow you to specify sample size during the statistics updating as well as histogram option during the runtime.

11 SAP RSORAISQN – How to update table statistics, check storage quality and rebuild index?

You can use RSORAISQN to update table statistics, review index storage quality and rebuild table/index as well in an Oracle environment. To enhance performance, it introduces parallel processing capabilities and allows you to specify number of parallel processes in index/table rebuilding process or analyzing process.

Figure 23 RSORAISQN – check index storage quality

Values of Variant is Saved for specified screens only

There is a business need to update one of SAP program RBDAPP01 variants to enable parallel solution to cut job runtime. When we tried to “save” our changes – packet size, server group etc., a window was popped up saying “Values of variant … saved for specified screens only”. Reviewing the variant again, our changes were partially saved- Changes on Idoc selection screen were saved but changes on screen of “Parallel Proc.” were not.

What does the message “Values of variant … is saved for specified screens only” means ? How to fix this?

If you are seeing a similar message during program variant change, this means the program has several selection screens, and you are entering data into at least one of selection screens which has NOT been selected for the variant. So data for the un-specified screen is not saved. To fix this, you need to

  1. Find out screen number of the specific screen ,
  2. Change the variant attribute to select the needed screen using the screen number and
  3. Then you can go ahead to update the data in the screen and your data would be saved.

If you would like to know the details, please continue reading.

1 The message – Vales of variant saved for specified screens only

Changes were made in IDOC selection screen and “Parallel Proc.” Screen, following information pop-up window was showed when I tried to save the changes.

Figure 1 ABAP variant – Values of variant saved for specified screens only

When you created a variant, you can specify selection screens needed for a variant. To fix the issue, you need to change variant attribute to include the needed screen. In this case, it is “Parallel Proc.” screen.

2 Find out screen number of selection screen

We need to verify screen number first. You can find screen number of your current sap screen via menu System -> status in the SAP window. Following screen shows that screen number is “1200” for “Parallel Proc.” Screen.

Figure 2 ABAP variant – find out screen number of a SAP screen

3 Change attribute of program variant to include/select the needed screen

You can change program variant via S38 or directly from job step list screen of SM37. Figure 3 shows that the screen 1200 is not selected. That is why data on this screen cannot be saved.

Figure 3 ABAP Variant – specify selection screens for a variant

Click the check-button prior to 1200 and save your screen attribute changes

4 Update the program variant in the new enabled selection screen

Since the Parallel Proc. Screen of RBDAPP01 is now included as part of the variant, we can enter and save our changes without any issue now. The pop-up window is gone and status message is saying “vales of variant * saved”- Issue resolved!

Figure 4 ABAP variant – change variant

 

If your ABAP report program has only one selection screen, you would not run into the message when you are trying to change an existing variant. This post assumes that you are familiar with program variant creation. If not, you can refer to SAP training document.

 

How to find a parent job/program of a SAP update work process

You spot a long running SAP update work process via work process monitor (SAP SM50 or SM66) or /SDF/MON utilities in a SAP system, you would like to know the parent job/program which is generating the update process. So you can work with corresponding owner to tune the corresponding job/program to make updating more efficient. This can be straightforward when update process is belonged to a SAP user and the user executes only one transaction. Or this can be a little tricking when an updating process is belonged to a SAP user which is shared by many concurrent SAP jobs. This post would show you how to use STAD to identify the parent of an update work process via an example.

1 long running updating process showed in /SDF/MON

I found a long updating processes under SAP user BGOMUSER which is common background user-id used by hundreds of SAP background jobs in our system. The updating started after 03:16 and finished before 03:33 using work process 209 based on /SDF/MON snapshots which were take every minute in our system. The duration of updating lasted about 17 minutes ( 1,080 seconds) Who is the parents job of this long running update process?

Figure 1 a long running SAP update process in /SDF/MON

2. How to use STAD to identify the parent job/program of an updating process?

Identify transaction steps, then you can use “client record” of the transaction step to identify the parent job/program. If no client record is available, then change STAD display mode from default mode to display “business transaction total/summary” to find parent transaction step and the parent job.

2.1 Identify associated SAP STAD statistics record

I ran STAD to pull out SAP statistics records related to update tasks for the interested period from the server where update work process ran. Following is a small part of the STAD result screen.

Figure 2 STAD records for SAP update tasks

Each line is for statistics from a transaction step. The 1st line on Figure 2 is the transaction step related to long updating process showed in Figure 1 based on time when the transaction step was started, WP( *9 ), duration(1,007 seconds), and useriD(BGOMUSR). Here, WP “*9” is displayed instead of 209. this is due to the fact that length of WP field length is 2 – in old version a SAP system is assuming that work process number is between 0- 99. But in our system, our work process number is already over 100, so there is overflow in STAD for work process number when a system has over 100 SAP work processes configured.

2.2 Identify a parent of an update work process via client info of a transaction step statistics

Here, I would find out the parent of 2nd update transaction step QA11 under user “*9120” in Figure 2 first. I double clicked 2nd line and I got statistics details screen for the 2nd step. Figure 3 is a part of STAD details screen

Figure 3 STAD details screen – Client Info button

Figure 3 shows a button “Client Info”. I clicked it once then I got Figure 4 screen which shows the parent job in field “Initial action” among other information.

Figure 4 STAD details screen – client info details

Figure 4 tells us that the parent of the updating process is program “RQEEAM10” which was executed by an SAP user online. So when client record of STAD is available, we can identify the parent of a transaction step.

Following the same approach, I double clicked the 1st line of Figure 2, I got figure 5 screen which has no “client info”, so where can I find its’ parent in this case?

Figure 5 STAD details screen – NO client info

2.3 Identify parent of an update process via STAD display mode – business transaction total/sums

Since the details record has no “client info” for me to identify the parent program, I changed STAD display mode from the default mode “Show all Stats records, Sorted by time” to “Show Business transaction Total”, then I loop through the record to identify the parent which contains the updating record, Please see the figure 6.

Figure 6 STAD Business transaction total screen – long update process

Now, we know that the parent of this long update process is a SAP background job and the job name is “Invoice_140320*”.

If you do not see the job name or program name displayed, you can try to use “client-info” of the parent line as showed in Figure 7 and Figure 8

Figure 7 STAD business transaction total – no parent job showed

 

Figure 8 Client info of a parent line for long update process

3. Further information

Here I talked on how to find parent of a completed updating process. If you are sure that an user id is used exclusively to execute a job, then you can be sure that update process under that user should be related to the batch job and you can use SM37 to find the job and so on.

RFC call is normally handled via SAP Dialog Work process. It is normal that those RFC are under a common user which can be used by many concurrent processes. RFC call can be from the program/job which is running in the same system or remote system. The same system means RFC client and RFC server are in the same system. Remote means that RFC call is from different system. You might wonder how to find a parent of RFC process in those cases. In this context, parent is actually the client which is making the RFC call. In short, you can use STAD client info to find a parent process of RFC call as well.

 

 

 

SAP Lock Monitoring and lock performance issues analysis

In my previous post, I talked about how to run SAP SM12 and understand SAP SM12 functions. In this post, I would cover

  1. What is SAP lock and how is SAP lock different from database lock?
  2. SAP lock/enqueue solution architecture overview
  3. SAP lock operation health monitoring
    1. Monitoring various SAP lock fill levels
    2. Monitoring SAP lock rejected rate
    3. Current top users which is holding most of SAP locks
    4. Monitor old/stuck SM12 lock entry
  4. SAP lock issue trouble-shooting
    1. SAP lock issue overview
    2. Delete Stuck/orphan SAPSM12 lock entries
    3. Application program lock collision – causes and fixes
    4. SAP Lock table overflow – causes and fixes

1 What is SAP lock and how is SAP lock different from database lock?

SAP lock is an application protocol to ensure that one business object is modified/changed by one user or one execution of one transaction or one job at one time. Application would place request to lock an object like sales order, the application can proceed with the change logic only lock request is granted, after changes is completed, the lock on the business object would be released. A granted SAP lock request is held in an in-memory central lock table with a configurable size via SAP transaction RZ10. A lock request can only be granted when there is no existing lock related to the business object in the central lock table.

SAP lock is different from database lock. The SAP lock is on business object level. One business object can be stored in many tables. SAP lock is an application protocols – which means an application can still change a business object which is locked by another application if its’ design is to ignore the protocols. So it is kind of “soft lock”. Database lock is at entry or table level which is a “hard lock”. No SAP application can change a table record if there is a database lock on it.

2 SAP Lock/Enqueue solution architecture overview

Understanding the SAP lock solution architecture can help us to do performance trouble shooting performance issue related to SAP lock.

A typical SAP system consists of one or more application instances/servers and Central Instance. In this case, SAP lock communication path is: the lock request from the program is sent to local dispatcher which passes it to Message server which is on the CI. Then message server engages the dispatcher of CI, which forwards the request to the Enqueue work process where SAP lock request is executed.

Figure 1 SAP enqueue/lock solution architecture

Please take note that I have not listed backup file used to keep locking entries owned by updating process. Also, Figure 1 is based on a classical SAP system which consists of a central instance and several dialog instances. SAP work processes on central instance which have direct access to lock table would bypass dispatcher, message server and lock server.

3 SAP Lock Operation Health Monitoring

SAP Lock operation – health monitoring is to make sure that there is no risk of lock table overflow and no lock entry remains in table beyond reasonable duration. You monitor those areas via SM12 built-in features – Enqueue statistics and “Top capacity usage”.

Please refer to below figure 2 for explanation of SM12 Enqueue statistics. You can click here to know how to run SAP SM12 in full details.

Figure 2 SAP SM12 Lock Statistics

3.1 Monitor various SM12 Fill levels

We need to make sure that there is enough “Idle” lock capacity defined by the gap between Maximum number of “Lock Entries”/”Lock Arguments” and Maximum Fill level, so there is no risk of lock table overflow. I would prefer a maximum 90% utilization as a threshold for further investigation. We also need to pay attention to current fill-level. If current fill-level is around maximum fill level, then it might be a time to closely monitor the lock operation. Maximum Fill level is only meaningful after system has been up and business operation has gone through a full cycle.

3.2 Monitor SAP lock Rejected rate

Rejected rate is calculated as Rejected/”Enqueue operations” X 100%. You need to be aware of normal accepted rejected rate of your system. In my system, normal rejected rate is around 1%. If rejected rate goes significant higher, this can indicate a system wide SAP lock issue. The number of enqueue operations and the number of rejected is the accumulation data since latest start of the system. To see current rejected rate – you can take two snapshots of SAP lock statistics at two different moments to calculate the rejected rate for the period.

Following chart is an example I used to verified whether SAP lock rejected rate is normal. There was an application enqueue issue which impacts several business function areas for several days. The issue was resolved but someone was questioning why rejected rate was still high. I explained that data showed in Rejected field was an accumulated data – then I produced a rejected rate for a latest period to show “current” rejected rate which was at 1.5% – that calmed concern whether we were still having lock issue.

12:26:00(EST) 10:52AM(EST) Period defined by two time
Enqueue operations 698,445,905 697,164,953 1,280,952
Rejected 255,946,432 255,927,063 19,369
Rejected rate 36.6% 36.7% 1.5%

3.3 Monitor current Top SAP lock capacity user

Any single user/program should not generate more than 5% of maximum number of “lock entries”/”lock arguments” normally. We need to check who are placing most of locks when fill level is close to quotas like Maximum number of lock entries allowed. Then we can review whether number of lock entries can be reduced via changing program code or the way which the program/job is executed. There is no one-size-fits-all approach to decide what is acceptable number of locks which can be placed by one program…The bottom-line is that program logic has been designed properly in terms of SAP lock operations – lock as late as possible and release as soon as possible to avoid lock table overflow.

3.4 Monitor old/stuck SM12 lock entry

This is to make sure that old entries should be removed promptly. This has not been significant in my experience from system/application performance point view. But it could be important f number of entries stuck are significant or old lock entry is related to a frequent access object.

4 SAP lock issue trouble-shooting

SAP lock issue can cause SAP application program/job/process termination due to failure of securing a lock. Or it can be slow down a business process due to a lot of time spent in getting lock. It can cause impact all users and business operations due to shortage of SAP work process when many SAP work processes are stuck in SAPLSENA program.

4.1 SAP lock issue overview

Based on SAP lock architecture showed in Figure 1, we can know theoretically lock performance issue can be due to ill function of following components:

  1. Program
  1. Dispatcher
  2. Message server
  3. Lock server
  4. Lock table
  5. Application server
  6. Network

You can use SM12 diagnosis function to validate whether a SAP lock problem is due to dispatcher, message server, lock server, application server or network:

  • SAP SM12 -> Goto/Extra -> Diagnosis/Diagnosis in Update and
  • SAP SM12 -> “test” in OK/command window -> Error Handling -> Diagnosis Environment.

You can use SAP transaction SM21 to review system log for SAP lock issue or use ST22 to review core-dump of a program due to lock failure.

In reality, most of performance issues related to SAP locking operation are due to application logic, slowness of SAP lock release, stuck/old SAP lock entries and lock table overflow.

A great book on SAP performance – SAP Performance Optimization Guide: Analyzing and Tuning SAP Systems, SAP Basis, SAP Administration

4.2 Delete stuck/old SAP SM12 lock entries

It often happens that SAP lock entries can be left in the lock table due to malfunction of SAP system operations. So what is a big deal if this happens? How critical and how urgent should we deal with such stuck SAP lock entries? This really depends on particular situation:

  • Volume of stuck entries
    – If a huge number of lock entries are stuck
    can lead to SAP lock able overflow. If a huge number of lock entries from many different users, this can indicates Malfunction of SAP system.
  • Lock attribute of stuck entries – share lock, exclusive lock, lock at table level and lock at single entry level has totally different meaning to follow SAP lock operation on the object. For example, lock on the entry level only impact subsequent SAP lock request which need to lock the same entry. Lock at table level would impact any subsequent SAP lock request which requests a lock on any entry of that table.
  • Business nature which lock object represents – a lock on infrequent access object and a lock on a frequent access object would have different impact to normal business operation which needs the lock.
  • Nature of business operation – a lock entry for a single business document related to archiving process is left in system. Since normal business application has no need to change the business document again, the impact to business operation in the system is “zero”. However if a lock entry is related to open customer rush order which is subject to change in subsequent business process, then a stuck entry would stop subsequent activities which needs to update the order. In case, this is an urgent/rush customer order, then it is time critical to address the stuck entry.

A stuck SAP SM12 entry can cause subsequent SAP lock request failure if the subsequent lock request needs to lock the same object. So it should be removed to avoid business process fallout/delay. But how do we know a SAP lock entry can be safely deleted? How old is old enough? Following is my tips to safely delete a stuck SAP SM12 lock entry:

  • SM12 Lock entry is not owned by updating task( backup flag is not checked)
    • Orphan SAP lock entry can be deleted – Orphan means owner of this lock is not active in the SAP system. You can find owner information from SAP lock entry detail – especially, SAP host name, user name, SAP work process#. SAP work process# has a process type (background, Dialog etc.).
      • If the lock is issued from a background work process on a server but that “BGD” work process is handling request from different user now or that “BGD” work process is in idle status, then the lock is an “orphan” lock and can be deleted. So on.
      • If the lock is issued from a dialog work process on a server and the related SAP user is not logon in SAP system( SAP SM04 and AL08 to check logon users). Then the lock is an orphan entry. Please pay attention that a SAP “DIA” work process is designed to be shared among online users which might execute different SAP transactions/programs – an user task would be rolled out of a SAP dialog work process during the online user’s “think”/”pause” time. For that reason, it is normal that an online user have locked a sales order, but you do not see any work processing running under that user in SAP SM50 and the process from which the lock is issued is in “waiting” status. So you cannot say since the work process is idle now, then the sap lock entry is “orphan”. But you properly can use “date time ” of lock entry details to say whether a sap lock entry is orphan if your system has a timeout setting for inactive online user and process.
  • SM12 Lock entry is owned by updating task( backup flag is checked or shadow/blue entry)

    In General, you do not delete an SM12 lock entry whose backup flag is checked. The SAP lock entry will be removed automatically after related updating task is completed. However in following situation, a lock entry can be deleted safely.

    • Any SAP SM12 lock entry older than updating task retention period can be removed
    • A SAP SM12 lock entry can be deleted if no outstanding updating task exists in SAP updating task monitor SM13. For example, SAP SM12 lock entry is under user A, but SAP SM13 update backlog contains no entry from user A. In this case, this SM12 entry can be deleted.

Improper deleting SM12 lock entry might cause data integrity issue for related table or business object. Make sure that you have checked SAP system, job and user to reach a conclusion that it is safe to delete a SAP lock entry.

4.3 Do trouble-shooting on SAP lock/enqueue collision – SAPLSENA

4.3.1 The SAPLSENA issue description

SAP lock/Enqueue issue related to program SAPLSENA is the most often-seen and tricky SAP locking issue. When this issue happens, one or many Sap work processes are continuously running with report SAPLSENA in SAP work process monitor SM50 screen( refer to Figure 3 to see sample screen) and SM66 screen( refer to Figure 4 to see sample screen ). In worst case, all SAP work processes are occupied by report SALSENA program for a period. Those SAPLSENA work processes are keeping requesting to get a SAP lock. So in nature, this issue would impact program/process which needs to change business object when SAP system still have enough “idle” work processes.

This issue itself is an application issue since SAP lock is an application protocols implemented in business application/program. In some cases, the issue can disappear by itself without any human interaction if you wait “enough” time. It could be normal that some of SAP work processes are running with program SAPLSENA for a brief moment. It becomes an issue when a SAP process is seeing running with SAPLSENA for a significant time and it has a material impact on runtime of a business transaction.

Figure 3 SM50 – many processes are showing SAPLSENA under report

Figure 4 SM66 – Many work process with a ENQ status and SAPLSENA program

4.3.2 Why does this issue happen?

In online transaction, if you are going to use SAP transaction va02 to modify a customer order which someone else is changing it via the same transaction or other standard SAP transaction, your transaction would be aborted by SAP and telling you that another user else is changing it. Then you can only change the order again after the user’s change on the order is completed. In situation like this, what you have seen in figure 3 or figure 4 would never happen since lock request duration is under a few milliseconds, you normally do not see program SAPLSENA displayed in SM50/SM66 screen.

In background processing, SAP application can have a logic of keeping trying until lock is secured assuming whoever holds the lock would release the lock quickly. This type of design is to avoid interruption of background process execution.

If the program who holds the lock cannot release the lock quickly for some reasons or there are huge number of requests to lock the same object, so the lock request keeps retrying, that makes program SAPLSENA visible in SM50/SM66 like figure 3 or figure 4 would be observed in SM50/SM66 and many business transactions/processes would be put on hold due to this.

Figure 5 is a standard SAP code, Program would execute the DO-ENDDO loop until the lock request is successfully placed.

Figure 5 DO-ENDDO loop for lock requests

Please note that I am explaining the technical reason why lock requestor is seen

4.3.3 How to do trouble-shooting when such issue happened?

When this issue happens, it is important to understand what business object is involved and who is holding the lock. To find out such information, you have two ways

  1. Log enqueue operation via SAP transaction SM12 – you can find out business object (lock object) involved in collision based on SM12 log. Please remember to switch off logging after you are done to avoid disc space issue. Several minutes log should be enough. Please refer to SAP Note 125041 to know SAP enqueue log format.
  2. Do an enqueue trace via SAP transaction
    ST05/ST12 – This is my normal way to verify what is collision and who is holding the lock which block the process being traced. You can base on collision owner to find out server/instance and work process related to lock holder process. Collision object is a lock object – you can use SAP SE11 transaction to check lock object “description” to find related application area. You can contact the SAP user to find out more about the application related to the lock holder. Based on those information, you might identify the offender, review possible solution or next step. Please refer to Figure 6. Figure 6 is an enqueue trace related to one SAPLSENA performance issue where all SAP transactions which related to order creation and order changes were stuck. Based on the trace, I was able to identify and later confirmed that SAP CIF model activation job was the root cause.

    Figure 6 – Enqueue trace statement details

In our production box, our users were complaining slowness of deleting workflow item via SAP transaction SBWP. In SM50, many processes are stuck with program SAPLSENA. This was caused by an application setting which was causing lock contention. You can refer to my post “SAP SBWP workflow item deletion performance issue“.

4.4 SAP lock table overflow

4.4.1 What is SAP lock table overflow?

In what is a SAP lock section, it is stated that a granted SAP lock is held in an in-memory table whose size is specified via SAP parameter enque/table_size. So there is a limit on how many lock requests can be hold in the lock table. When that limit is reached and a program is making a new lock request, the SAP system would send “lock table overflow” error message to the program.

SAP lock table overflow is really a message that SAP lock table has been full and cannot accept new lock request anymore.

4.4.2 What are possible reasons for lock table overflow and how to fix SAP lock table overflow issue?

When SAP lock table is overflow, then no lock can be granted since no empty slot in the lock table to hold the lock entry. All programs/jobs who need to place the table would fail. Apparently this happens rarely to a well-tune SAP system.

Lock table overflow can happen due to one or more of following reasons:

  1. Small SAP Lock table is too small – due to increasing of business volume and activities, the lock table size might need to increase. SAP lock table size is controlled by SAP parameter enque/table_size.
  2. Slow release of SAP lock – SAP lock entries are not released timely due to system/application performance issue. For example, if SAP updating function is slow or stopped, all entries which belong to updating tasks would remain in SAP lock table until corresponding updating tasks are completed. In this case, increasing lock table size is just addressing the symptom not root cause.
  3. Improper application design on SAP lock – Application design of top SAP lock capacity user should be carefully reviewed especially if it is using high % of SAP lock capacity like 20%. When I worked on an archive project, a program used over 50% of SAP lock capacity due to faulty design and caused overflow. Please refer to my post – SAP job cancelled on error “The document cannot be blocked” to know more on this case.
  4. Improper job/application schedule/deployment – If two jobs which would place a lot of locks and run at the same time can create lock table overflow, then these two jobs should run separate time windows if business logic allows.
  5. Inappropriate execution – This is referring to the situation where a user is executing a transaction with abnormal volume due to accidental operation or process a huge volume in a time-window which is not allowed.
  6. Program performance – If a program is not coded/design properly, it could hold a lock unnecessary longer. When the program is holding a lock on a common accessed business object, this would create lock issue. Solution in this case is to tune the program performance.

4.4.3 How to know which one is true cause?

You need to use information provided by SAP SM12 SAP top capacity used – history screen which captures top offender when overflow happens. And start to do investigation with that. Please refer to Figure 7 for the history screen.

Figure 7 SAP SM12 Top capacity used – History

Being familiar with your system and the normal fill-level and high-water mark for various fill levels, this would help you to do trouble-shooting as well. If NR_of_locks held by top offender is reasonable, then your lock table size might be too small. You need to increase it. If your system is really slow or overloaded when the lock table overflow happens, then you have to focus on system performance issue first – in my experience, this has not been a case so far.

4 Further clarification

Typical SAP lock performance issue described in this post is based on my performance experience. Situation in your system might be difference. I would like to highlight again that you should take caution to delete an SAP lock entry and switch off SAP enqueue logging. My personal preference is to use SAP ST12/ST05 enqueue trace, I found myself often lost in the huge number of enqueue log entries and cannot find lock holder.

When SAP updating is slow for whatever reason, an end business user cannot change a document which they have changed “not long ago”, you cannot delete a SAP lock entry to make business “happy” – this create data integrity issue. Right direction is to understand why updating is delayed and wait for completion of updating.

When you have a “system wide” lock issue with many SAP work processes running with report SAPLSENA and your SAP system is not overloaded, this “system wide” lock issue is caused by application/program/job instead of system.

In my previous post, I talked about how to run SAP SM12 and understand SAP SM12 functions. In this post, I would cover

  1. What is SAP lock and how is SAP lock different from database lock?
  2. SAP lock/enqueue solution architecture overview
  3. SAP lock operation health monitoring
    1. Monitoring various SAP lock fill levels
    2. Monitoring SAP lock rejected rate
    3. Current top users which is holding most of SAP locks
    4. Monitor old/stuck SM12 lock entry
  4. SAP lock issue trouble-shooting
    1. SAP lock issue overview
    2. Delete Stuck/orphan SAPSM12 lock entries
    3. Application program lock collision – causes and fixes
    4. SAP Lock table overflow – causes and fixes

1 What is SAP lock and how is SAP lock different from database lock?

SAP lock is an application protocol to ensure that one business object is modified/changed by one user or one execution of one transaction or one job at one time. Application would place request to lock an object like sales order, the application can proceed with the change logic only lock request is granted, after changes is completed, the lock on the business object would be released. A granted SAP lock request is held in an in-memory central lock table with a configurable size via SAP transaction RZ10. A lock request can only be granted when there is no existing lock related to the business object in the central lock table.

SAP lock is different from database lock. The SAP lock is on business object level. One business object can be stored in many tables. SAP lock is an application protocols – which means an application can still change a business object which is locked by another application if its’ design is to ignore the protocols. So it is kind of “soft lock”. Database lock is at entry or table level which is a “hard lock”. No SAP application can change a table record if there is a database lock on it.

2 SAP Lock/Enqueue solution architecture overview

Understanding the SAP lock solution architecture can help us to do performance trouble shooting performance issue related to SAP lock.

A typical SAP system consists of one or more application instances/servers and Central Instance. In this case, SAP lock communication path is: the lock request from the program is sent to local dispatcher which passes it to Message server which is on the CI. Then message server engages the dispatcher of CI, which forwards the request to the Enqueue work process where SAP lock request is executed.

Figure 1 SAP enqueue/lock solution architecture

Please take note that I have not listed backup file used to keep locking entries owned by updating process. Also, Figure 1 is based on a classical SAP system which consists of a central instance and several dialog instances. SAP work processes on central instance which have direct access to lock table would bypass dispatcher, message server and lock server.

3 SAP Lock Operation Health Monitoring

SAP Lock operation – health monitoring is to make sure that there is no risk of lock table overflow and no lock entry remains in table beyond reasonable duration. You monitor those areas via SM12 built-in features – Enqueue statistics and “Top capacity usage”.

Please refer to below figure 2 for explanation of SM12 Enqueue statistics. You can click here to know how to run SAP SM12 in full details.

Figure 2 SAP SM12 Lock Statistics

3.1 Monitor various SM12 Fill levels

We need to make sure that there is enough “Idle” lock capacity defined by the gap between Maximum number of “Lock Entries”/”Lock Arguments” and Maximum Fill level, so there is no risk of lock table overflow. I would prefer a maximum 90% utilization as a threshold for further investigation. We also need to pay attention to current fill-level. If current fill-level is around maximum fill level, then it might be a time to closely monitor the lock operation. Maximum Fill level is only meaningful after system has been up and business operation has gone through a full cycle.

3.2 Monitor SAP lock Rejected rate

Rejected rate is calculated as Rejected/”Enqueue operations” X 100%. You need to be aware of normal accepted rejected rate of your system. In my system, normal rejected rate is around 1%. If rejected rate goes significant higher, this can indicate a system wide SAP lock issue. The number of enqueue operations and the number of rejected is the accumulation data since latest start of the system. To see current rejected rate – you can take two snapshots of SAP lock statistics at two different moments to calculate the rejected rate for the period.

Following chart is an example I used to verified whether SAP lock rejected rate is normal. There was an application enqueue issue which impacts several business function areas for several days. The issue was resolved but someone was questioning why rejected rate was still high. I explained that data showed in Rejected field was an accumulated data – then I produced a rejected rate for a latest period to show “current” rejected rate which was at 1.5% – that calmed concern whether we were still having lock issue.

12:26:00(EST) 10:52AM(EST) Period defined by two time
Enqueue operations 698,445,905 697,164,953 1,280,952
Rejected 255,946,432 255,927,063 19,369
Rejected rate 36.6% 36.7% 1.5%

3.3 Monitor current Top SAP lock capacity user

Any single user/program should not generate more than 5% of maximum number of “lock entries”/”lock arguments” normally. We need to check who are placing most of locks when fill level is close to quotas like Maximum number of lock entries allowed. Then we can review whether number of lock entries can be reduced via changing program code or the way which the program/job is executed. There is no one-size-fits-all approach to decide what is acceptable number of locks which can be placed by one program…The bottom-line is that program logic has been designed properly in terms of SAP lock operations – lock as late as possible and release as soon as possible to avoid lock table overflow.

3.4 Monitor old/stuck SM12 lock entry

This is to make sure that old entries should be removed promptly. This has not been significant in my experience from system/application performance point view. But it could be important f number of entries stuck are significant or old lock entry is related to a frequent access object.

4 SAP lock issue trouble-shooting

SAP lock issue can cause SAP application program/job/process termination due to failure of securing a lock. Or it can be slow down a business process due to a lot of time spent in getting lock. It can cause impact all users and business operations due to shortage of SAP work process when many SAP work processes are stuck in SAPLSENA program.

4.1 SAP lock issue overview

Based on SAP lock architecture showed in Figure 1, we can know theoretically lock performance issue can be due to ill function of following components:

  1. Program
  1. Dispatcher
  2. Message server
  3. Lock server
  4. Lock table
  5. Application server
  6. Network

You can use SM12 diagnosis function to validate whether a SAP lock problem is due to dispatcher, message server, lock server, application server or network:

  • SAP SM12 -> Goto/Extra -> Diagnosis/Diagnosis in Update and
  • SAP SM12 -> “test” in OK/command window -> Error Handling -> Diagnosis Environment.

You can use SAP transaction SM21 to review system log for SAP lock issue or use ST22 to review core-dump of a program due to lock failure.

In reality, most of performance issues related to SAP locking operation are due to application logic, slowness of SAP lock release, stuck/old SAP lock entries and lock table overflow.

4.2 Delete stuck/old SAP SM12 lock entries

It often happens that SAP lock entries can be left in the lock table due to malfunction of SAP system operations. So what is a big deal if this happens? How critical and how urgent should we deal with such stuck SAP lock entries? This really depends on particular situation:

  • Volume of stuck entries
    – If a huge number of lock entries are stuck
    can lead to SAP lock able overflow. If a huge number of lock entries from many different users, this can indicates Malfunction of SAP system.
  • Lock attribute of stuck entries – share lock, exclusive lock, lock at table level and lock at single entry level has totally different meaning to follow SAP lock operation on the object. For example, lock on the entry level only impact subsequent SAP lock request which need to lock the same entry. Lock at table level would impact any subsequent SAP lock request which requests a lock on any entry of that table.
  • Business nature which lock object represents – a lock on infrequent access object and a lock on a frequent access object would have different impact to normal business operation which needs the lock.
  • Nature of business operation – a lock entry for a single business document related to archiving process is left in system. Since normal business application has no need to change the business document again, the impact to business operation in the system is “zero”. However if a lock entry is related to open customer rush order which is subject to change in subsequent business process, then a stuck entry would stop subsequent activities which needs to update the order. In case, this is an urgent/rush customer order, then it is time critical to address the stuck entry.

A stuck SAP SM12 entry can cause subsequent SAP lock request failure if the subsequent lock request needs to lock the same object. So it should be removed to avoid business process fallout/delay. But how do we know a SAP lock entry can be safely deleted? How old is old enough? Following is my tips to safely delete a stuck SAP SM12 lock entry:

  • SM12 Lock entry is not owned by updating task( backup flag is not checked)
    • Orphan SAP lock entry can be deleted – Orphan means owner of this lock is not active in the SAP system. You can find owner information from SAP lock entry detail – especially, SAP host name, user name, SAP work process#. SAP work process# has a process type (background, Dialog etc.).
      • If the lock is issued from a background work process on a server but that “BGD” work process is handling request from different user now or that “BGD” work process is in idle status, then the lock is an “orphan” lock and can be deleted. So on.
      • If the lock is issued from a dialog work process on a server and the related SAP user is not logon in SAP system( SAP SM04 and AL08 to check logon users). Then the lock is an orphan entry. Please pay attention that a SAP “DIA” work process is designed to be shared among online users which might execute different SAP transactions/programs – an user task would be rolled out of a SAP dialog work process during the online user’s “think”/”pause” time. For that reason, it is normal that an online user have locked a sales order, but you do not see any work processing running under that user in SAP SM50 and the process from which the lock is issued is in “waiting” status. So you cannot say since the work process is idle now, then the sap lock entry is “orphan”. But you properly can use “date time ” of lock entry details to say whether a sap lock entry is orphan if your system has a timeout setting for inactive online user and process.
  • SM12 Lock entry is owned by updating task( backup flag is checked or shadow/blue entry)

    In General, you do not delete an SM12 lock entry whose backup flag is checked. The SAP lock entry will be removed automatically after related updating task is completed. However in following situation, a lock entry can be deleted safely.

    • Any SAP SM12 lock entry older than updating task retention period can be removed
    • A SAP SM12 lock entry can be deleted if no outstanding updating task exists in SAP updating task monitor SM13. For example, SAP SM12 lock entry is under user A, but SAP SM13 update backlog contains no entry from user A. In this case, this SM12 entry can be deleted.

Improper deleting SM12 lock entry might cause data integrity issue for related table or business object. Make sure that you have checked SAP system, job and user to reach a conclusion that it is safe to delete a SAP lock entry.

4.3 Do trouble-shooting on SAP lock/enqueue collision – SAPLSENA

4.3.1 The SAPLSENA issue description

SAP lock/Enqueue issue related to program SAPLSENA is the most often-seen and tricky SAP locking issue. When this issue happens, one or many Sap work processes are continuously running with report SAPLSENA in SAP work process monitor SM50 screen( refer to Figure 3 to see sample screen) and SM66 screen( refer to Figure 4 to see sample screen ). In worst case, all SAP work processes are occupied by report SALSENA program for a period. Those SAPLSENA work processes are keeping requesting to get a SAP lock. So in nature, this issue would impact program/process which needs to change business object when SAP system still have enough “idle” work processes.

This issue itself is an application issue since SAP lock is an application protocols implemented in business application/program. In some cases, the issue can disappear by itself without any human interaction if you wait “enough” time. It could be normal that some of SAP work processes are running with program SAPLSENA for a brief moment. It becomes an issue when a SAP process is seeing running with SAPLSENA for a significant time and it has a material impact on runtime of a business transaction.

Figure 3 SM50 – many processes are showing SAPLSENA under report

Figure 4 SM66 – Many work process with a ENQ status and SAPLSENA program

4.3.2 Why does this issue happen?

In online transaction, if you are going to use SAP transaction va02 to modify a customer order which someone else is changing it via the same transaction or other standard SAP transaction, your transaction would be aborted by SAP and telling you that another user else is changing it. Then you can only change the order again after the user’s change on the order is completed. In situation like this, what you have seen in figure 3 or figure 4 would never happen since lock request duration is under a few milliseconds, you normally do not see program SAPLSENA displayed in SM50/SM66 screen.

In background processing, SAP application can have a logic of keeping trying until lock is secured assuming whoever holds the lock would release the lock quickly. This type of design is to avoid interruption of background process execution.

If the program who holds the lock cannot release the lock quickly for some reasons or there are huge number of requests to lock the same object, so the lock request keeps retrying, that makes program SAPLSENA visible in SM50/SM66 like figure 3 or figure 4 would be observed in SM50/SM66 and many business transactions/processes would be put on hold due to this.

Figure 5 is a standard SAP code, Program would execute the DO-ENDDO loop until the lock request is successfully placed.

Figure 5 DO-ENDDO loop for lock requests

Please note that I am explaining the technical reason why lock requestor is seen

4.3.3 How to do trouble-shooting when such issue happened?

When this issue happens, it is important to understand what business object is involved and who is holding the lock. To find out such information, you have two ways

  1. Log enqueue operation via SAP transaction SM12 – you can find out business object (lock object) involved in collision based on SM12 log. Please remember to switch off logging after you are done to avoid disc space issue. Several minutes log should be enough. Please refer to SAP Note 125041 to know SAP enqueue log format.
  2. Do an enqueue trace via SAP transaction
    ST05/ST12 – This is my normal way to verify what is collision and who is holding the lock which block the process being traced. You can base on collision owner to find out server/instance and work process related to lock holder process. Collision object is a lock object – you can use SAP SE11 transaction to check lock object “description” to find related application area. You can contact the SAP user to find out more about the application related to the lock holder. Based on those information, you might identify the offender, review possible solution or next step. Please refer to Figure 6. Figure 6 is an enqueue trace related to one SAPLSENA performance issue where all SAP transactions which related to order creation and order changes were stuck. Based on the trace, I was able to identify and later confirmed that SAP CIF model activation job was the root cause.

    Figure 6 – Enqueue trace statement details

In our production box, our users were complaining slowness of deleting workflow item via SAP transaction SBWP. In SM50, many processes are stuck with program SAPLSENA. This was caused by an application setting which was causing lock contention. You can refer to my post “SAP SBWP workflow item deletion performance issue“.

4.4 SAP lock table overflow

4.4.1 What is SAP lock table overflow?

In what is a SAP lock section, it is stated that a granted SAP lock is held in an in-memory table whose size is specified via SAP parameter enque/table_size. So there is a limit on how many lock requests can be hold in the lock table. When that limit is reached and a program is making a new lock request, the SAP system would send “lock table overflow” error message to the program.

SAP lock table overflow is really a message that SAP lock table has been full and cannot accept new lock request anymore.

4.4.2 What are possible reasons for lock table overflow and how to fix SAP lock table overflow issue?

When SAP lock table is overflow, then no lock can be granted since no empty slot in the lock table to hold the lock entry. All programs/jobs who need to place the table would fail. Apparently this happens rarely to a well-tune SAP system.

Lock table overflow can happen due to one or more of following reasons:

  1. Small SAP Lock table is too small – due to increasing of business volume and activities, the lock table size might need to increase. SAP lock table size is controlled by SAP parameter enque/table_size.
  2. Slow release of SAP lock – SAP lock entries are not released timely due to system/application performance issue. For example, if SAP updating function is slow or stopped, all entries which belong to updating tasks would remain in SAP lock table until corresponding updating tasks are completed. In this case, increasing lock table size is just addressing the symptom not root cause.
  3. Improper application design on SAP lock – Application design of top SAP lock capacity user should be carefully reviewed especially if it is using high % of SAP lock capacity like 20%. When I worked on an archive project, a program used over 50% of SAP lock capacity due to faulty design and caused overflow. Please refer to my post – SAP job cancelled on error “The document cannot be blocked” to know more on this case.
  4. Improper job/application schedule/deployment – If two jobs which would place a lot of locks and run at the same time can create lock table overflow, then these two jobs should run separate time windows if business logic allows.
  5. Inappropriate execution – This is referring to the situation where a user is executing a transaction with abnormal volume due to accidental operation or process a huge volume in a time-window which is not allowed.
  6. Program performance – If a program is not coded/design properly, it could hold a lock unnecessary longer. When the program is holding a lock on a common accessed business object, this would create lock issue. Solution in this case is to tune the program performance.

4.4.3 How to know which one is true cause?

You need to use information provided by SAP SM12 SAP top capacity used – history screen which captures top offender when overflow happens. And start to do investigation with that. Please refer to Figure 7 for the history screen.

Figure 7 SAP SM12 Top capacity used – History

Being familiar with your system and the normal fill-level and high-water mark for various fill levels, this would help you to do trouble-shooting as well. If NR_of_locks held by top offender is reasonable, then your lock table size might be too small. You need to increase it. If your system is really slow or overloaded when the lock table overflow happens, then you have to focus on system performance issue first – in my experience, this has not been a case so far.

4 Further clarification

Typical SAP lock performance issue described in this post is based on my performance experience. Situation in your system might be difference. I would like to highlight again that you should take caution to delete an SAP lock entry and switch off SAP enqueue logging. My personal preference is to use SAP ST12/ST05 enqueue trace, I found myself often lost in the huge number of enqueue log entries and cannot find lock holder.

When SAP updating is slow for whatever reason, an end business user cannot change a document which they have changed “not long ago”, you cannot delete a SAP lock entry to make business “happy” – this create data integrity issue. Right direction is to understand why updating is delayed and wait for completion of updating.

When you have a “system wide” lock issue with many SAP work processes running with report SAPLSENA and your SAP system is not overloaded, this “system wide” lock issue is caused by application/program/job instead of system.

How to validate SQL performance tuning via SAP ST04 SQL cache

Sometimes, you have to tune SQL to improve SAP program performance. After SAP SQL tuning is done, is new SQL statement having better performance than original SQL statement? This post would show you an example on how to verify this via SAP ST04 SQL cache.

1 The background

ST04 SQL cache showed that one SQL was a top expensive SQL statement based on CPU utilization.

2 Tuning on the SQL in SAP ABAP program

To improve the performance, SQL is tuned in the way that source table was swapped from /SAPAPO/MATKEY to /SAPAPO/MATMAP since primary key of /SAPAPO/MATKEY matches existing selection criteria and new table returns same result under the same selection criteria. Please refer to Figure 1 to know the new code and old code—

Figure 1 Old code and new code

The change is quite simple and done in SAP SCM system. So would the new SQL have better performance than the old SQL?

3 Performance validation on the tuned SQL via ST04 SQL cache

Since all executed SQL statements would be kept SQL cache with many performance metrics like response time, CPU time etc. So both old SQL and new SQL execution history data is available in the system. That data is pulled and showed in Figure 2.

Figure 2 ST04 SQL Cache

Figure 2 shows that each execution of new SQL accessing /SAPAPO/MATMAP table is spending about 13,006,591 microseconds against 21,495,315+ microseconds spent by old SQL statements accessing /SAPAPO/MATKEY. That is at least 39% performance improvement on CPU time consumption per SQL execution.

Total CPU time consumed by a SQL is the times of two numbers – CPU time per SQL execution and number of times which the SQL is executed. Improvement on individual SQL execution is not a guarantee for overall improvement if number of SQL execution is increased.

In this particular case, Figure 1 shows the change is just replacing the old table with a new table without logic changes. So number of times which the SQL should be executed would not be changed after tuning. Also Figure 2 shows number of rows retrieved (column Rows/Exec) per SQL execution has no big difference between new SQL and old SQL and it also shows 43+% improvement on SQL elapsed time/execution. So the SQL/ABAP performance tuning is successful based on ST04 SQL cache performance analysis.

So far, ST04 SQL cache performance analysis helped us to achieve our goal – whether the SQL/ABAP tuning is good/bad for performance. You might wonder why it helped to improve performance by a simple table swap? The developer replaced the old table with the new table since EXT_MATNR is a primary key of /SAPAPO/MATMAP (Figure 5). Reading a table via primary key is faster – is this the reason for the performance improvement? If you would like to know more, continue readingJ.

4 Why changes make a performance improvement

A tuned SQL can make performance improvement due to many reasons – execution plan, table size etc.

4.1 Execution plan comparison

Figure 3 is the SQL execution plan with original table before the change. Figure 4 is the SQL execution plan with the new table.

Figure 3 SQL Execution plan before change

Figure 4 SQL execution plan after change

From the above execution plan, we can see that primary key is not used to read the new table. It is using the “Full table scan”. Index /SAPAPO/MATKEY~MAT has only “MATNR” field. Comparing cost of SQL execution plan between old table and new table, you can see “Estimated Costs” with the new table is 788 against 1,223 with old table.

Figure 5 Table structure

Based on the code and new table structure, the primary key could be used to read the table normally. Why not? We need table size information to understand this.

4.2 Review table size

Using ST04 SQL command editor, We can enter SQL and execute it to get following information from Oracle table DBA_TABLES .

Figure 6 – Number of table records

So number of rows is similar but average row length is different – this is approved by the table size from DB02 transaction (Figure 4)

Figure 7 – Table storage size

So the new table is almost 1/3 of old table in size.

4.3 Performance improvement

When an index is used to access a table data record, Oracle would need to read index block to get address for the data block and then it would fetch data block. When number of records needed reaches a threshold, it would be more efficient to bypass index and read data block directly. In this particular case, whenever the sql is executed, it would fetch 247K record out of 250K records. So the full table access is used by Oracle with the new table. So the performance improvement is coming from a much smaller table and full table access not due to primary index with the new table.

5 Other methods for validating a SQL performance tuning

There are other ways to determine whether a SQL tuning in SAP program is good to performance.

  • Based on program runtime difference – Get program runtime before and after changes against same testing data.
  • Based on analysis of program execution statistics – statistics for program execution before changes and statistics for program execution after changes.
  • Based on performance trace analysis – Do a before and after trace.
  • Based on technical analysis like where-clause and table index.

Each of above method has their own pros and cons. You need to choose the best one fit for your case. For example, when you measure the performance by program/job runtime or execution statistics, you need to be sure that runtime is not impacted by many factors like volume, application lock and database lock, resource contention etc. If you changed many SQL statements in a program to tune performance, it might be a tedious work to find out how each change is performing.

Someone might notice that the cost in the execution plan with a new table is lower. You cannot say for sure that lower cost means faster access when different table are involved. Even with the same table, an execution plan with a “lower” cost can be a bad plan – that is why Oracle provides Oracle hints for program to influence execution plan. So using cost is not an error-proof method to measure performance of two different SQL statements.

In this post, I have used ST04 SQL cache to validate performance improvement from SQL tuning. You can click How to run ST04 for more information.

Would a proposed SAP code change improve program performance?

Would a proposed SAP program code change improve program performance? The approach is normally to get a before and after picture and do the comparison. But based on before the picture and planned changes, you can tell whether the planned change has potential to make a performance improvement some times. This is what I did to evaluate whether an SAP OSS note can help to improve SAP program performance. My conclusion is that the OSS note would not make noticeable performance improvement on the SAP program in the business environment. This post would walk you through the steps I used to reach this conclusion:

  1. Understand business scenarios which have performance concerns,
  2. Review SAP OSS note to know the proposed changes,
  3. Work with function owner to prepare test cases for the business scenarios,
  4. Execute scenarios with ST12 trace,
  5. Analyze ST12 trace against SAP recommendation and
  6. Conclusion.

1 Understand business scenarios which have performance concerns

Our customer needs performance improvement with their back order process in SAP GATP box. Back order process is done via standard SAP program /SAPAPO/BOP. BOP function owner explained that when volume was high that GATP BOP program /SAPAPO/BOP ran slow.

SAP OSS note system was searched by functional owner and an OSS note 1778556 -” Performance improvement while reading requirement items”, which is quite promising to our situation, was identified.

Figure 1 SAP OSS note on BOP

Would this OSS note improve our customer’s BOP program performance?

2. Review SAP OSS note to know the proposed changes

Let’s understand what changes is proposed by OSS note 1778556 . The program and correct instruction is 1580485 in our situation

Figure 2 Oss note 17785556 correction

The change details are

Figure 3 Oss note – change details

The code changes proposed by SAP are on method SET_FROM_INTERFACE in SAP class /SAPAPO/CL_APT_REQ. The code changes looks promising using “BINARY SEARCH” etc.. All code changes are not related to database operations.

Would the changes give us “significant performance improvement”?

3 Work with function owner to prepare test cases for the business scenarios

To answer the question, we need to know how expensive the SET_FROM_INTERFACE is during the current BOP execution. We need to run test with SAP ST12 trace to analyze this.

BOP function owner was asked to prepare two test cases to test BOP program for the business scenario which is referred by the OSS note.

  1. Single document
  2. Big volume

The single volume is used to check and verify the code touched by OSS note would be actually executed by the business scenario before the big volume of scenario is executed. If the code touched by SAP OSS note is not executed by the business scenario, then your scenario is not correct, any assessment based on wrong business scenario is meaningless.

4 Execute scenario with SAP ST12 performance trace

At this step, Run GATP BOP program for single order with ST12 trace, then run GATP BOP program for the big volume.

5 Analyze SAP ST12 performance trace against OSS recommendation

We know SAP OSS note has proposed to change method SET_FROM_INTERFACE. Since we have trace now, we can see how expensive is executing that method. Following are two ST12 Trace screen related to big volume.

Figure 4 BOP Volume trace 1

Based on Figure 4, we know that the BOP program spent 71% of runtime on Database side not on ABAP logic side. The FM is not show up in the top of the list. So the display was sorted based on “Call”, then the display was search based on “SET_FROM_INTERFACE”. Figure 5 shows all possible SET_FROM_INTERFACE calls.

Figure 5 BOP Volume trace 2

The method is executed from two places with 1 and 10,478 times respectively. Runtime is 2.5% and 0.4% of runtime in two cases with a total 2.9% of runtime.

6 Conclusion

The method SET_FROM_INTERFACE which is changed by the OSS note is executed in customer BOP volume execution but the BOP program only spent 2.9% of total BOP runtime in that method. So implementation of this OSS note in our customer environment would not bring noticeable performance improvement. The implementation of OSS note was stopped.

7 Clarification

What this post covers is how to validate whether a proposed change from a SAP OSS note is good for performance. The key is to understand what subroutines are proposed for changes and check how expensive those subroutines are before the changes via ST12 trace. This is not a typical SAP program performance tuning process. To tune a SAP program performance, you can follow the process I stated in my previous blog. Core of the process is to conduct test with performance trace first and then identify the improvement opportunity through analyzing performance trace.

If proposed change has potential to improve performance and you would like to be sure about this, then get a after change trace and do a trace comparing between the before and after changes would help.

 

Would a proposed SAP program code change improve program performance? The approach is normally to get a before and after picture and do the comparison. But based on before the picture and planned changes, you can tell whether the planned change has potential to make a performance improvement some times. This is what I did to evaluate whether an SAP OSS note can help to improve SAP program performance. My conclusion is that the OSS note would not make noticeable performance improvement on the SAP program in the business environment. This post would walk you through the steps I used to reach this conclusion:

  1. Understand business scenarios which have performance concerns,
  2. Review SAP OSS note to know the proposed changes,
  3. Work with function owner to prepare test cases for the business scenarios,
  4. Execute scenarios with ST12 trace,
  5. Analyze ST12 trace against SAP recommendation and
  6. Conclusion.

1 Understand business scenarios which have performance concerns

Our customer needs performance improvement with their back order process in SAP GATP box. Back order process is done via standard SAP program /SAPAPO/BOP. BOP function owner explained that when volume was high that GATP BOP program /SAPAPO/BOP ran slow.

SAP OSS note system was searched by functional owner and an OSS note 1778556 -” Performance improvement while reading requirement items”, which is quite promising to our situation, was identified.

Figure 1 SAP OSS note on BOP

Would this OSS note improve our customer’s BOP program performance?

2. Review SAP OSS note to know the proposed changes

Let’s understand what changes is proposed by OSS note 1778556 . The program and correct instruction is 1580485 in our situation

Figure 2 Oss note 17785556 correction

The change details are

Figure 3 Oss note – change details

The code changes proposed by SAP are on method SET_FROM_INTERFACE in SAP class /SAPAPO/CL_APT_REQ. The code changes looks promising using “BINARY SEARCH” etc.. All code changes are not related to database operations.

Would the changes give us “significant performance improvement”?

3 Work with function owner to prepare test cases for the business scenarios

To answer the question, we need to know how expensive the SET_FROM_INTERFACE is during the current BOP execution. We need to run test with SAP ST12 trace to analyze this.

BOP function owner was asked to prepare two test cases to test BOP program for the business scenario which is referred by the OSS note.

  1. Single document
  2. Big volume

The single volume is used to check and verify the code touched by OSS note would be actually executed by the business scenario before the big volume of scenario is executed. If the code touched by SAP OSS note is not executed by the business scenario, then your scenario is not correct, any assessment based on wrong business scenario is meaningless.

4 Execute scenario with SAP ST12 performance trace

At this step, Run GATP BOP program for single order with ST12 trace, then run GATP BOP program for the big volume.

5 Analyze SAP ST12 performance trace against OSS recommendation

We know SAP OSS note has proposed to change method SET_FROM_INTERFACE. Since we have trace now, we can see how expensive is executing that method. Following are two ST12 Trace screen related to big volume.

Figure 4 BOP Volume trace 1

Based on Figure 4, we know that the BOP program spent 71% of runtime on Database side not on ABAP logic side. The FM is not show up in the top of the list. So the display was sorted based on “Call”, then the display was search based on “SET_FROM_INTERFACE”. Figure 5 shows all possible SET_FROM_INTERFACE calls.

Figure 5 BOP Volume trace 2

The method is executed from two places with 1 and 10,478 times respectively. Runtime is 2.5% and 0.4% of runtime in two cases with a total 2.9% of runtime.

6 Conclusion

The method SET_FROM_INTERFACE which is changed by the OSS note is executed in customer BOP volume execution but the BOP program only spent 2.9% of total BOP runtime in that method. So implementation of this OSS note in our customer environment would not bring noticeable performance improvement. The implementation of OSS note was stopped.

7 Clarification

What this post covers is how to validate whether a proposed change from a SAP OSS note is good for performance. The key is to understand what subroutines are proposed for changes and check how expensive those subroutines are before the changes via ST12 trace. This is not a typical SAP program performance tuning process. To tune a SAP program performance, you can follow the process I stated in my previous blog. Core of the process is to conduct test with performance trace first and then identify the improvement opportunity through analyzing performance trace.

If proposed change has potential to improve performance and you would like to be sure about this, then get a after change trace and do a trace comparing between the before and after changes would help.