Program runs very long: Performance analysis

A program runs for an exceptionally long time. How can you determine which database access takes too long.

Due to inadequate programming, a database acess can take an extremely long time. There can be various causes: multi-nested SELECT, not using indices effectively, missing or non-selective specifications in the WHERE clause, for-all-entries with empty table, no specifications in the selections screen (incorrect handling of variants), and so on.

Solution

    1. Determine whether the system is slow in total or only the particular program (see Note 15374). For the analysis and solution of general performance problems and for the support of the system administration you should carry out the EarlyWatch service in your system regularly.

    2. Determine which program is affected exactly. (Transaction profile -- see Note 15374). Transaction ST03 can be used for the identification of long-running transactions. With this transaction, the runtime statistics can displayed. Choose the button 'Choose for Analysis', select the computer, select the period; select the workprocess type via the buttons 'Dialog', 'Background' and display the runtimes via 'Top-Time' or 'Transaction Profile'. Potential causes of the problem with extensive database accesses typically have have a DB portion of >80% from the entire runtime.

    3. Creating the SQL trace:

        a) Activate the SQL TRACE immediately before executing the respective transaction. For this, call Transaction ST05 in a new mode. For Releases <= 4.0B, then choose the button TRACE ON and enter the user name for which you want to create a trace. As of Release 4.5A, enter the user name under 'Trace on with filter'.

                       As of Release 4.0A: With the radio buttons 'Trace Mode' you can log the response times via RFCs and Enque locks. We recommend to record this information in the trace.

                       Notes:
Make sure that the program to be examined runs on the same application server on which you started the ST05.
Ensure that you only measure one program at a time. That is, the traced user may have occupied only one workprocess on the application server.
During the first execution of a program, data bank accesses are carried out to fill R/3-internal buffers. A trace for the performance measurement can only be used with the second execution.

        b) After this, restart the transaction to be measured on the old window. When it is finished, switch off the SQL trace again by choosing the button 'Trace Off' and then select 'LIST TRACE'.

                       AS of Release 4.0A: Display all Trace Modes (activate all buttons). In the trace, choose the button 'More Info' or 'extended List', in order to display important details and time stamps and calling programs.

        c) In the displayed listing, you can now analyze all SQL statements with their particular DB delay time - column DURATION. The values are issued in the unit microseconds.

                       You can download the trace with Okay Code% PC. Use 'Not converted' as file format, since this text format can be read easiest and reprocessed, if necessary.

                       You can identify the calling program by doubleclicking on the particular line on the program or choosing the button 'ABAP display'. Note down the program name, the line number, changed by, since this information is required for further processing.

                       With the button 'Explain SQL' you receive an overview on how the database carried out the SQL query. You should also save this information since this information is essential for further analysis.
Under ORACLE: Click the table name(s). Also save the information on the indices defined on this table.

    4. Calls of runtime statistics (statistical records):
    In addition to the SQL trace we recommend to display the statistics records.
    Execute Transaction STAT on the same application server. Enter the name of the user you traced and enter the approximate time interval. On the statistics records, you can see the entire execution time, that is, not only the database time but also other portions which determine the runtime. Example (1) The CPU time which was needed on the application server and (2) times for loading and generating. If one of these response time portions is higher than the database time, generally no SQL problem exists but there are high execution times in the ABAP program (in case (1)) or there are problems with program or table buffers (in case (2)).
    Store this information since it is essential for further analysis.


Important: long access times are by no means always caused by unfavorable programming. There may be other reasons such as corrupt indexes, database tables blocked by other users, incorrect parameter setting of the database, bottleneck situation on the hard disks.

If you identified a long-running SQL query:
Do not carelessly change indexes. In particular, indices of the SAP standard should not be changed without SAP consultation.
If it is a long-running statement in the SAP standard system, open a message to SAP and enter the above-mentioned information in the message (program part, execution plan, index definitions, runtime statistics (statistical record)).
If the problem occurs due to program changes made by you, ask your consultant to solve the problem or request an EarlyWatch express (see Note 61781), in order to receive additional support by SAP.