
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.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.
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.
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.
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.
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.
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.
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.
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.
STATS
(refer to my blog post Measure your Application’s Performance and Resource Consumption: Evaluate Statistics Records with Tr... 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 canYou must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
14 | |
12 | |
12 | |
9 | |
9 | |
7 | |
6 | |
6 | |
6 | |
5 |