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 for SAP program/application performance tuning. Here, I would cover how to analyze SAP SQL trace to do SQL tuning to improve 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 based on average time taken by different type of database data operation sequential read, direct read, insert and commit to see whether there is a database performance issue(including network and IO). However network and IO normally have a broad impact on a SAP system performance instead of cherry-picking one program.
1.2 Analyze Top expensive SQL statements
Following are steps I use to analyze top expensive SQL statements captured in SAP ST12 SQL trace for performance improvement:
- 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 happen to 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 statement 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 due to the fact that fields in where-clause of ABAP SQL code do not “match” fields of any existing index. Or ABAP SQL where-clause (selection criteria) has specified index fields, but the program passes no data to those index fields during the execution. 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.
- Modify SQL where-clause in a way so proper index can be used to read table such as adding index fields etc.
- 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 complexity where-clause in SQL statement and similar index which could confuse Oracle CBO. Here, i am not talking about wrong index choice related to table, system statistics or database setting, which can be quickly fixed via updating table/system statistics or change database setting requiring no ABAP effort.
To fix such issue, the where-clause of the SQL statement needs to be simplified. Or you can use corresponding database hints in ABAP code to influence database CBO index choice for this particular SQL.
3. Index is used but other index 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 based on table indexes picture.
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 SQL query for the table 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 a field and selectivity of index might not be necessary the same. Sometimes, an index is not selective but a SQL 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 CBO can make a wrong choice. 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 locations.
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 high organization level (such as sales organization) instead of repeating it at every organization at a low level (such as sales area).
- Retrieve once but keep it for future reference 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 it can point to potential repeated execution on the same business function 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 usage 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 in the future.
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.3 section of this post.
If there are individual “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 help on performance as well. Reducing selected fields can lead to a new execution plan like getting data only from an index. It can reduce round trips between application server and database server as well. Reducing number of records by adding more specific retrieval condition into SQL where-clause instead of do data filtering in the program level can help SAP program performance.
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 index is not being used. It is clear that using index or not 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.
Sometimes, you might need to void aggregated operation in database server such as group and sum etc. But sometimes, you need to reduce database server load by moving “group” or “sort” operation from database server to application server. There is no one rule for all in this situation.
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 to tune SAP program/application 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 status (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 while your program is “consistently” performing wore than normal, this can normally means a code/design issue. If performance is becoming worse and worse due to steadily increased volume, the performance issue should be related to design and/or code of the program. There are “general” criteria to say whether database performance can be a concern.
SAP 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 or SAP SQL tuning.
Should I analyze ABAP trace, SQL trace or both 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 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 if you cannot find a solution to a performance issue especially when standard SAP program/table is involved.