ST05: Analyze Individual Trace Records
This is—for the time being—the last blog post in my series Use ST05 to Analyze the Communication of the ABAP Work Process with External Resources. Previous blog posts in this sequence have mostly dealt with the
ST05 tool itself, or with complete traces recorded for an entire end-to-end business process or at least for a whole user interaction. Now it is time to focus on individual trace records, representing statements or communication events triggered by your application. Typically you want to concentrate on those with a long duration or with a high resource consumption. They have the biggest impact on your application’s performance and scalability. You should direct your optimization efforts to them.
The best entry point for such a project is the aggregated list of Structure-Identical Statements (Fig. 1). I have covered it in my post ST05: Aggregate Trace Records. This list sorts the statements descending by their Duration. The overall slowest statements (considering all their aggregated executions) are at the top of the list. If you are more concerned about HANA CPU Time or HANA Memory consumption, re-sort the list according to these values. In any case, you want to work on the list’s first few records.
Before diving into a thorough analysis of how a statement was processed and how you can optimize it, ask yourself whether it is really required. If the statement contributes neither to your application’s business logic nor to the technical frameworks employed by the application, you shall remove it. Elimination of unnecessary statements is always the best optimization strategy—and often the easiest. Statement tuning is meaningful only for essential statements.
ABAP Source Code
ABAP Call Hierarchy
Use the ABAP Call Hierarchy to verify that your application coding invokes the framework which eventually triggers the statement in the best possible way, and to confirm that calls from frameworks to your source code are fully optimized. Click on any entry in this list to navigate to the corresponding source code.
Data Access Strategy
SQL Analyzer Tool for SAP HANA
The SQL Analyzer‘s main section is the analysis view. Its three tabs support different perspectives on the SQL statement and its execution.
- The Overview tab (Fig. 6) summarizes the most important information in several cards. The main cards for performance and scalability investigations are:
Displays the statement with its conditions, variables, and parameters. For more detailed information, go to the SQL tab.
- Execution Summary:
Provides details such as compilation and execution times, peak memory usage, number of operators, and connections.
- Dominant Plan Operators:
Lists the top 5 operators by execution time.
- Data Usage:
Shows the size of the result set and the number of accessed tables.
- The Plan Graph tab (Fig. 7) provides a visualization of the SQL query plan. It shows the data flow from bottom to top: Data retrieval from tables is at the bottom, and the query result is at the top. Along the path, plan operators achieve the transformations to obtain the desired output. Operators working concurrently, i.e., in parallel, are positioned next to each other. Nodes in the Plan Graph represent physical or logical operators, and contain associated information, especially a breakdown of the operator’s execution time. The edges connecting the nodes indicate the number of rows transferred between the two operators. The critical path is determined by the chain of operators with the biggest impact on the query performance. Its edges are shown in red.
- The SQL tab shows the SQL statement with the full session context and the parameters with their types and values. With this information, the SQL query can be re-executed under identical conditions.
The property pane below the analysis view contains 8 tabs where you can focus on small details of the statement’s execution to get a very thorough understanding and to discover optimization approaches.
With a monitoring tool like my transaction
STATS (refer to my blog post Measure your Application’s Performance and Resource Consumption: Evaluate Statistics Records with Transaction STATS for an introduction) you may discover that your application’s performance is insufficient. Maybe your application is too slow or its resource consumption is too high. In either case,
STATS may indicate that the poor performance is caused by communication events triggered by your application. Then you would use my transaction
ST05 to record a corresponding trace. Your application’s most expensive communication events will be at the top of the appropriately sorted trace list—ideally you work with the list of Structure-Identical Statements. With the various tools offered by
ST05 you can
- gain insight into the significance of the event for your application
- inspect the statement’s implementation in the ABAP source code
- recognize the ABAP call stack that eventually triggered the event
- review the database tables or views on which the statement operates
- examine the statement as it was processed
Based on this comprehensive information, you can derive optimization strategies which will improve your application’s performance.
The official documentation for the SAP HANA SQL Analyzer is available at SQL Analyzer Tool for SAP HANA Guide.
The SAP HANA Troubleshooting and Performance Analysis Guide might also be helpful.
The Open SAP course A First Step Towards SAP HANA Query Optimization covers further techniques for SQL query performance analyses and optimizations.