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