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.

Figure 1: The list of Structure-Identical Statements summarizes Trace Main Records for statements with the same structure, but potentially distinct values of bind variables. By default, the list is sorted descending by Duration. This list is the recommended starting point for an optimization project that focuses on the most expensive communication events triggered by your application.
The subsequent discussions use the marked SQL trace record as an example.

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.

Statement Details

The Statement field in all types of trace lists is always strongly abbreviated. For a trace record representing a statement that you have confirmed as essential for your application, you can display all of the statement’s details by clicking button Statement , available for the Trace Single Records, the Trace Main Records, the Structure-Identical Statements, and the Value-Identical Statements. Alternatively, double-click the Statement field. Fig. 2 shows the Statement Details of the SQL trace record marked in Fig. 1. (The result depends on the type of trace record.)

Figure 2: Statement Details for the SQL trace record highlighted in Fig. 1. The left panel shows the default option With Variable Names of button Statement. Bind variables are shown as question marks with an orange background. (Other database platforms may use different place holders.) Their types and values are listed separately in section Variables. The right panel displays the alternative With Variable Values, where the bind variables have been replaced by the actual values.
Navigation to the Statement Details from an aggregated trace record list (Structure-Identical Statements or Value-Identical Statements) takes specific values (e.g., for bind variables in the case of an SQL trace record) from the first contributing record.

For an SQL trace record, the Statement Details display the SQL statement as it was sent from the ABAP work process to the database server for execution. The statement’s implementation, e.g., as Open SQL, in your ABAP source code may be different (e.g., it will typically not have an explicite WHERE clause condition on the MANDT table field). This is the main benefit of the Statement Details display: It enables you to understand how the database interface in the ABAP work process interpreted your implementation, and what the database really had to do. As the application’s developer you are familiar with its statements from the design time and implementation time perspective. The Statement Details give you the run time view which helps you to verify that the statement sent to and processed by the database fully matches the intended purpose.

No measurement values (e.g., for elapsed time, consumed resources, or number of processed table rows) are included in the Statement Details for an SQL trace record. This also holds for BUF trace records, while details for trace records of the other types do include some measurements.

DDIC Information

A full analysis and understanding of statements needs to consider the declarations and technical settings of the objects affected by them. For trace records of types SQL or BUF, a click on button DDIC Information , or a double-click on the Object Name field, produces a summary of the ABAP Dictionary Information for the table or view (Fig. 3). If the trace record corresponds to an SQL statement that joins several tables, an intermediate popup enables you to select an individual table. From the ABAP Dictionary Information, button Display Table Fields takes you to the ABAP Data Dictionary transaction SE11 to look at the complete declaration, including primary key fields, secondary indexes, and buffer settings. Or you jump immediately to either all available indexes, or to a selected index via buttons Display All Index Fields or Display Fields of Selected Index, respectively. For ENQ trace records, the lock object is shown in transaction SE11. DDIC information does not exist for the other trace types.

Figure 3: The ABAP Dictionary Information summarizes a table’s or view’s declaration and technical settings as maintained in the ABAP Data Dictionary. From this summary, you can navigate into transaction SE11 to see all details.

Based on the DDIC Information, you can judge whether accesses to the object are really required for your application. When you combine the statement (e.g., as seen in the Statement Details) with the available indexes, you may recognize potential mismatches between the WHERE clause and the existing indexes. Such mismatches may cause excessive run time or resource consumption while processing the statement.

ABAP Source Code

As already mentioned, the Statement Details show the SQL statement as executed by the database server. To compare this with how the statement was implemented in your application’s source code, click on button ABAP Call Location . This takes you to the correct position in the appropriate tool of the ABAP development workbench (i.e., Class Builder SE24, Function Builder SE37, ABAP Editor SE38). There you can apply optimizations, locally to the source code or—after forward navigation into the ABAP Dictionary SE11—globally to the accessed object.

ABAP Call Hierarchy

In my previous blog post ST05: Basic Use I have indicated that the immediate caller of the traced communication event may not be the ABAP source code location where you as a business application developer can apply optimizations. Rather, it may be nested inside some software framework called directly or indirectly by your application’s ABAP code. To see the full ABAP call stack (Fig. 4) click button Call Hierarchy .

Figure 4: The ABAP Call Hierarchy for the SQL statement corresponding to the trace record marked in Fig. 1 indicates that only the ABAP events at levels 26 to 30 are specific to the business application. Everything else is framework coding.

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.

Data Access Strategy

For a deep understanding of an SQL statement’s run time behavior—duration as well as resource consumption—you need a very close look at how the database server has executed the statement which you have formulated in a declarative rather than imperative way. Based on a detailed insight you can derive optimization strategies. Ideally, they work locally on the expensive statement so that they cannot have side effects on any other statement. Be very cautious with global changes to the table’s or view’s declaration, indexes or technical settings. They may improve the performance or resource consumption of the current statement, but may negatively impact other statements which also access the table or view.

Execution Plan

The standard option Execution Plan (button ) is available for all supported database platforms. Its upper area shows the SQL statement with variable names, and the lower area indicates, in a database specific format, how the database server has handled the statement. This part is often fairly terse and technical and already for only moderately complex statements not easy to digest.

Figure 5: The Execution Plan for the SQL statement corresponding to the trace record marked in Fig. 1 displays at the top the statement, similar to the option With Variable Names of the Statement Details (Fig. 2). This part is common to all supported database platforms. It is followed by the database specific representation of how the SQL statement was processed on the database server (here SAP HANA 2.00).

The Execution Plan displays the nested hierarchy of the applied operations with their options and conditions and the objects on which they were used. The buttons offered in the application toolbar depend on the underlying database platform and may reveal some additional facts (e.g., estimates on the sizes of the involved tables and the sizes of the operation’s output) which add significant analysis capabilities.

SQL Analyzer Tool for SAP HANA

If your system uses SAP HANA, you are better off with the graphical SQL Analyzer Tool for SAP HANA. This requires that you have Visual Studio Code installed on your local machine and that you have the SQL Analyzer Tool for SAP HANA extension added to it. For a seamless integration with ST05, you also should set parameter HDB_OPEN_STUDIO (Open HANA Studio or SQL Analyzer when a Planviz is created) to X in your user profile, and set Visual Studio Code as the default app for .plv files. Then a click on HANA SQL Analyzer will create an appropriate plan file, download it to your local machine’s hard disk (into your SAP GUI’s documents folder), and from there open it with the extension SQL Analyzer Tool for SAP HANA (Figs. 6 and 7). Before the plan file creation, a popup asks you to specify (either confirm or overwrite) session variables. These are a kind of global variables set in the ABAP user context and forwarded from there to the database server where they control the execution of SQL statements. When analyzing an SQL statement in ST05, and creating its execution plan, the values for the session variables are retrieved from the SET SESSION VARIABLE statements which have defined the values and which are recorded together with the SQL statement that you are analyzing.

Figure 6: The SAP HANA SQL Analyzer extension for Visual Studio Code is the tool of choice for understanding how the SAP HANA database has executed an SQL statement recorded in a trace. The Overview organizes significant information in dedicated cards.

Figure 7: The Plan Graph in the HANA SQL Analyzer represents the execution of the SQL statement as a network graph of the involved plan operators and their connections.

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:
  • Context:
    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.

Summary

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.

References

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.