Did you know that it is possible to analyze the SELECT statements of an SQL trace with the Code Inspector check tool? You’ll find that ‘not all too well documented’ function of transaction ST05 when displaying the SQL-Trace in the menu under Goto –> Trace Analysis Using Code Inspector.
But how does this go together: a list of SQL statements and the check tool for ABAP source code? Opposite to what one might expect, it is not the source code, that bears the SQL statements which is analyzed – that could be done with the usual check of the static source code. Instead, when clicking the option ‘Trace Analysis Using Code Inspector’, the SQL statements are retrieved from the SQL trace, database specific ingredients are removed, and then the statements are analyzed by slightly modified code checks of the Code Inspector.
This collaboration between ST05 and Code Inspector has some remarkable benefits:
- Normally only important scenarios (reports, applications, UI steps relevant for production use) are captured with an SQL trace. By analyzing the SQL statements from such a trace one can be sure that ‘the right stuff’ is examined. Opposed to that, a standard check of the static code often returns messages for objects that are not relevant at all – for example test reports or code that is only executed in exceptional cases.
- Analysis of SQL traces is normally done by looking at the top 5 or 10 most expensive statements in the list of the ‘structural identical’ SQL statements (aka ‘summarized SQL statements’). As a reference for SQL trace analysis, see http://scn.sap.com/community/abap/testing-and-troubleshooting/blog/2007/09/05/the-sql-trace-st05-quick-and-easy. But often, due to the lack of reasonable test data, some SQL statement might not be very prominent in that list (that is, it does not have a high runtime) though no database index is used to speed up the access. Only if the same SQL statement is executed in a system with a higher amount of data in the table, the runtime might become critical.
Since the Code Inspector check does not use the actual number of lines in a database table, but rather the size category of the table as it is defined in the data dictionary (transaction SE11), the tool will raise a message for a statement that is not index-supported – even if it runs quite fast in a system with no appropriate test data.
- Also dynamic SQL statements can be analyzed. A static code check has no chance if some SQL statement contains dynamic parts. An example is a statement with a dynamic table name:
SELECT * FROM (db_tab) INTO lt_data WHERE keyfld1 = lv_keyfld1.
Since the name of the accessed table is only known at runtime, the static check cannot judge whether this access will be supported by a database index. The tool also does not know whether the accessed table is buffered or not. When, on the other hand, the statement is retrieved from the SQL trace, the variable ‘db_tab’ has been replaced in the database interface with the actual parameter and the SQL statement can therefore be analyzed by Code Inspector.
To illustrate the last point I have created an example report that contains three SQL statements, two of which have dynamic parts, namely one with a dynamic
table name and one with a dynamic WHERE-clause. First I check this report with the static ABAP code check choosing Program –> Check –> Code Inspector:
The check returns only one result message that tells me that “no field of a table index” could be used in the report’s first (and static) SQL statement, an access to table DD02L.
Now I execute the report, and trace the execution with the performance trace ST05. From the display of the trace I choose Goto –> Trace Analysis Using Code Inspector and now get the following result set:
There are now raised five messages (where one is not relevant at the moment, it’s with respect to some technical statement during the report execution). There are also displayed messages for the dynamic statements, for one of them even two messages: the access to the buffered table T005T, which is executed with a dynamic WHERE-clause, is not only bypassing the generic table buffer (“buffered key area not fully specified”). The statement also receives the message that there is “no first field of a table index in the WHERE-clause”.
So my recommendation is to use this Code Inspector integration into ST05. It’s easy to use and can quickly give you valuable information on the SQL statements in your trace.
When working with the ST05 – Code Inspector integration, you should be aware of the following peculiarities:
- Accesses to buffered tables will not show up in the SQL trace unless there has been a buffer load (which should not happen for repeated executions of the tested application) or an ABAP statement that explicitly or implicitly bypasses the table buffer. Now some ABAP Open SQL statement options like ‘BYPASSING BUFFER’ or ‘CLIENT SPECIFIED’ do not end up in the SQL trace and are thus ‘non-visible’ for the check tool. It may therefore happen that the check tool cannot track down the true reason for the bypassing of the table buffer: buffer load, use of native SQL, explicit bypassing in the code with ‘BYPASSING BUFFER’, or others. You will then find a message like “reason for bypassing of table buffer is unknown” in the result.
- Too complex SQL statements (for example very lengthy IN-lists) are not analyzed by the check tool. Also, like in the case of static source code analysis, SQL joins are not analyzed.
- There is no possibility to navigate from a Code Inspector finding directly to the corresponding statement in the SQL trace – that might come in a future release. Also, though navigation to the ABAP source code works from the result tree, the object names and line numbers displayed are not meaningful (they refer to some dummy entry).
- It is not possible to suppress a message from Code inspector which has been created by the check started from ST05. Also, already existing pseudo-comments in the ABAP source code are ignored. The motivation for that is that performance issues that come up during the execution of an application should not be suppressed, but remedied.
- You will find the possible messages of these Code Inspector checks in transaction SCI under Goto –> Management of –> Message Priorities in the check category ‘Internal Performance Tests’.
SQL Trace Check: Analysis of WHERE Condition for SELECT
SQL Trace Check: Analysis of WHERE Condition for UPDATE and DELETE
SQL Trace Check: Accesses to Buffered Tables
When comparing the check messages with those of the static ABAP code checks in check category ‘Performance Checks’, you’ll find that especially the check “Accesses to Buffered Tables“ has a number of additional information messages. From the analysis of the SQL statement it is not only judged whether a table access bypasses the table buffer,there is also the information, whether an accessed table that should be buffered based
on its technical settings could (or could not) have made use of the recommended buffering in this concrete statement.