To access the ABAP code that triggered the issue of the SQL statement, from the trace list screen, use the ABAP Display button or choose Trace ® ABAP Display from the menu. The ABAP coding is displayed for the SQL statement on which your cursor is currently positioned in the trace list. Explain SQL is only possible for the database operations PREPARE, OPEN, and REOPEN.
The SQL statement in the ABAP code, which is normally formulated in OPEN SQL, may differ considerably from the SQL statement processed by the database. The following list indicates how the R/3 database interface converts certain SQL terms:
Ÿ A WHERE clause is used for each SQL statement that accesses a client-dependent table (except for SELECT … CLIENT SPECIFIED)
Ÿ SELECT … FOR ALL ENTRIES => Several SELECT statements with corresponding OR clauses (details later)
Ÿ MODIFY => INSERT or UPDATE
Ÿ Accessing pooled and cluster tables
Ÿ EXPORT/IMPORT TO/FROM DATABASE => INSERT/SELECT on INDX-like tables
TRACE SUMMARY:
In the trace list, choose Goto ® Summary to display a summarized form of the list. The list shows the SQL operations, the number of affected data records, and the processing time required.
The summarized list is particularly useful for identifying loop constructs. The list can be compressed even further to show the most frequently accessed tables by using the Summarize button.
In the trace list, choose Goto ® Statement Summary to find out how many structure-identical SQL statements there are, and which tables or views they access (a structure-identical strategy is an identical access strategy with different variables). The displayed list is sorted by the time taken with time indicated in microseconds.
The list also indicates the percentage of value-identical accesses (identical SELECTs) and the total number of returned data records. You should avoid all value-identical accesses.
From the trace list, choose Goto ® Identical Selects to see an overview of the value-identical SQL statements in a trace (a value identical statement has the same access strategy and the same variables).
Identical SELECTS deliver an identical quantity of data records. Therefore, it makes sense to buffer the results in, for example, an internal table of the calling ABAP program.
From the number of executions of SQL statements that are similar in value, you can determine the optimization potential. For example, if there are 4 value-similar executions on table VVBAK, buffering the resulting data in an internal table makes 3 executions unnecessary. This represents an optimization potential for this SQL statement of between 60% and 75%.
The goal of using an SQL Performance Trace is to find SQL statements with a high optimization potential. Use three user sessions. One user session is for the trace list, one for the compressed summary, and one for identical selects.
From the trace list, you can access Explain SQL or the ABAP code. An expensive SQL statement is indicated when a database operation takes longer than 200, 000 microseconds or requires more than 10 FETCHes.
In addition, a series of SQL statements that are similar in structure usually indicate nesting that can be optimized considerably. If the structure-identical SQL statements in a loop or nesting require in total more than 200, 000 microseconds, they can be regarded as expensive.
The display of identical SELECTs tells you which of the statements are similar in value and are, therefore being executed unnecessarily. You should always avoid unnecessary execution of SQL statements.
No comments:
Post a Comment