Performance Suite – Traces – Part 2
Transaction ST05: Performance trace
During program runtime, the following operations can be recorded:
SQL statements of a user, RFC calls, enqueue operations and accesses to the SAP buffer.
On the initial screen, there are start (Trace on), stop (Trace off) and analysis switches. Another user for whom the trace is to be activated can also be predefined here. Your own user is proposed by default.
For standard program analysis, activate the SQL trace, the enqueue trace and the RFC trace. When creating a trace (this also applies to the SQL and ABAP TRACE), note the following points:
- Since the analysis is transparent, the user should only run one activity (no other background jobs or update requests).
- Make sure that Transaction ST05 runs on the same application server as the programs to be monitored. If, for example, you want to record an update request or a background job and you are working in a system with a distributed update or distributed background processing, you must start a trace on all relevant application servers.
- You can use the SQL trace to record SQL statements. There may be considerable differences between the SQL statement formulated from the ABAP and the SQL statement sent to the database. Furthermore, you have to use the buffer trace to activate SAP buffer accesses.
- In the SQL trace, the buffer loading processes are also written during the first program run, therefore first execute the program without a trace, so that reloading processes concerning the R/3 table buffers, program buffers, and so on, are not recorded.
- During the trace, note the following monitors: For the general check, note the work process overview, the operating system monitor for monitoring possible CPU bottlenecks and the database process monitor for directly monitoring the SQL statements. This does not work if the trace is activated under your own user because otherwise the SQL statements of the monitors would appear on the trace and are illegible.
SQL trace analysis
– For analysis, choose List Trace on the initial screen. Use the Trace Mode field to select the subsequent analysis and
then choose Execute:
Open/Reopen The fields specified in the Where condition are the key fields of the table. The result of the request is
exactly one record (Rec=1) or no record found (Rec=0). SQL statements for which all key fields are specified with
‘equal’ are called fully ‘qualified access’es or ‘Direct Reads’. A fully qualified database access should not last any
longer 2-10 ms. However, if the hard disk reloads data blocks, for example, times of 100 ms are acceptable.
If you place the cursor on the OPEN/REOPEN statement, you can navigate to the Explain SQL. When you double-click the table names, the system displays a dialog box that contains statistical information about the table and information about all of the table indexes. If you double-click the index, you receive statistical information about the index used.
You can choose Analyze to refresh the statistical information about a table and its indexes. To define the behavior of the cost-based optimizer, you must refresh the statistical information each time a change is made to a table or its
indexes. Index Statistics provides an overview of the statistical information about all indexes. Choose Explain
with hint to use hints to view the execution plan of a SQL statement.
Fetch The data records are transferred in packages to the application server in one or more fetches. The select clause
of the SQL statement determines the number of records transferred with a Fetch . If the number of fields to be transferred by the database are restricted using the SELECT command, or listed directly, more records fit into a Fetch than if you use a ‘SELECT *’ command. Here the optimal pick/pack times are 10 ms/record.
If a SQL statement was identified as having a long runtime, the trace should be performed when the system load is high and low. If the response times for a database access are only negative at certain times, this implies that there are throughput problems in the network or when accessing the database. If, on the other hand, it is difficult to reproduce the response times for a database access, the SQL statement is probably inefficient.
You can distinguish between two types of SQL statements that perform poorly:
Suitable access path:
The SQL statement checks a lot of data blocks in the database and is time-consuming because a lot of data records are
transferred from the database to the application server. The database performance is then satisfactory if you can see that the SQL statement requires less than 10 ms for each data record transferred. Generally, performance can only be improved by changing the business process or ABAP code.
Unsuitable access path:
The SQL statement checks a lot of data blocks, but only transfers a few of them from the database to the server. The
database performance is less than optimal if you can see that the SQL statement requires more than 10 ms for each data record transferred. You can make it more optimal by changing the ABAP code (unsuitable access strategy – to a complex or incorrect WHERE clause) or by changing the index design (unsuitable access strategy incorrect index or no index).
To recognize network problems between the database and the application server, execute the SQL trace repeatedly, on the one hand on the application server of the connected database and, on the other hand, on an application server that is connected to the database via the TCP/IP network. If the response times on the server, which is connected to the
database via the network, are considerably higher, there is a network problem.
In the trace list, choose the Summary function to obtain an overview of the intensity and time required to access the tables listed in the SQL performance trace.
The SQL statements are sorted according to their runtime. You should optimize SQL statements that have a relatively high runtime in comparison with the total runtime.
You can use the Goto.Identical.selects operation to identify SQL statements that have the same value, that is, the
database reads identical data repeatedly and in quick succession. Identical SQL statements return identical results.
In this respect, the SQL statement results are buffered, for example, in an internal table of the calling ABAP program.
On the trace list screen, you can choose ABAP display to jump to the section in the source code from which the SQL statement was executed. The ABAP display is displayed for the SQL statement on which the cursor is currently positioned in the trace list and it is possible for the PREPARE, OPEN and REOPEN database operations.
The RFC trace records RFCs that were received and sent. By double-clicking a row in the RFC trace or by choosing
Details, you receive, for example, the names and IP addresses of the sender and recipient, the name of the RFC
module and the volume of data transferred. To display the ABAP source code, choose ABAP DISPLAY.
The enqueue trace records R/3 lock requests or releases (including the lock keys and the objects in question).
Transaction SE30 ABAP TRACE
Use this function if you experience problems with high CPU consumption.
Unlike the SQL trace, this function also provides time measurements for operations on internal tables (LOOP, READ, SORT, APPEND, and so on) , as well as the runtime of database accesses (SELECT, EXEC SQL, and so on) and the time required for individual modularization units (MODULES, PERFORM, CALL FUNCTION, SUBMIT, and so on).
Alternatively, if programs have long runtimes, you can call the ABAP debugger from the work process overview (Transaction SM50, see below) and trace the program in debugging.
On the initial screen, you can enter a transaction code, a program name or a function module. To start the trace, you choose Execute. If you exit the program, transaction or function module in the usual way, the trace returns to the initial screen and displays the measurement data file that was just created in the lower area.
You can use the Analyze switch to display the various views for the measurement results:
Hit list (in the menu under’Goto’) displays the execution time for each statement. It is sorted in descending order according to gross times. Sorting the net time provides an overview of statements that
have the highest net times.
The gross time is the total time required for a call. The net time is the gross time minus the time required for the module called (MODULE, PERFORM, CALL FUNCTION, and so on) and separate ABAP Statements.The gross time is the same as the net time for basic statements such as APPEND and SORT.
Hierarchy displays the chronological sequence of the transaction or program.