Evaluating an SQL Trace

To evaluate a performance trace, select DISPLAY TRACE in the initial screen. A selection screen is displayed, and you can specify in the TRACE TYPE field the part of the trace you want to analyze. In this and the following sections, we will discuss the evaluation of each of the trace types separately. In practice, you can analyze all trace modes together.

Fields in the Dialog Box for Evaluating a Trace

Field	Explanation
TRACE FILENAME	Name of t he trace file. Normally, this name should not be changed .
TRACE TYPE	The default trace mode setting is SQL TRACE. To analyze an RFC trace, enqueue trace, HTTP, trace and buffer trace, select the corresponding check boxes.
TRACE PERIOD	Period in which the trace runs.
USER NAME	User whose actions have been traced.
OBJECT NAME	Names of specific tables to which the display of trace results is to be restricted. Note that , by default, t he tables D0l0*, D020*, and DDLOG are not shown in the trace results. These tables contain the ABAP coding and the buffer synchronization data.
EXECUTION TIME	Restricts t he display to SOL statements that have a certain execution time.
OPERATION	Restricts t he trace data to particular database operations.

Executing trace

Next, click the EXECUTE button. The basic SQL trace list is displayed. 

Fields in an SQL Trace

Field	Explanation
HH:MM:SS.MS	Time stamp in the form hour:minute:second. millisecond.
DURATION	Runtime of an SOL statement, in microseconds. If the runtime is more than 150,000 microseconds, the corresponding row is red to identify that SOL statement as having a "long runtime." However, the value 150,000 is a somewhat arbitrary boundary.
PROGRAM NAME	Name of the program from which the SOL statement originates.
OBJECT NAME	Name of the database table or database view.
OPERATION	Operation executed on the database, for example Prepare: preparing ("parsing") a statement, Open :opening a database cursor, Fetch : transferring data from the database, and so on.
CURS	Database cursor number.
RECORDS	Number of records read from the database.
RC	Database system-specific return code.
STATEMENT	Short form of the executed SOL statement. You can display the complete statement by doubleclicking the corresponding row.

Direct Read

An SQL statement that appears in accesses the table VBAK. The fields specified in the WHERE clause are key fields in the table. The result of the request can therefore only be either one record (Rec = 1) or no record (Rec = 0), depending on whether a table entry exists for the specified key. SQL statements in which all fields of the key of the respective table are specified as "same" are called fully qualified accesses or direct reads. A fully qualified database access should not take more than about 2 to 10 ms. However, in individual cases, an access may last up to 10 times longer, such as when blocks cannot be found in the database buffer and must be retrieved from the hard drive.

Basic Performance Trace List with Entries from SQL Trace and RFC Trace

The database access consists of two database operations, an Open/Reopen operation and a FETCH operation. The Reopen operation transfers the concrete values in the WHERE clause to the database. The FETCH operation locates the database data and transfers it to the application server.

Sequential read

A second access takes place in the VBAP table. Not all key fields in the WHERE clause are clearly specified with this access. As a result, multiple records can be transferred. However, in our example, five records are transferred (Rec - 5). The data records are transferred to the application server in packets, in one or more fetches (array fetch). An array fetch offers better performance than transferring individual records in a client/server environment.

The second access takes place via an efficient index; thus, the duration of execution also remains significantly less than 10 ms. The third access(again in the VBAK table) takes place via a field for which there is no efficient index. Thus, the duration of this statement is significantly longer than that of the previous statement.

Maximum number of records

The maximum number of records that can be transferred in a FETCH operation is determined by the SAP database interface as follows: every SAP work process has an input/output buffer for transferring data to or from the database. The SAP profile parameterdbs/io_buf _size specifies the size of this buffer. The number of records transferred from the database by a fetch is calculated as follows:

Number of records = dbs/io_buf _size I Length of a record to be read in bytes

The number of records per fetch depends on the Select clause of the SQL statement. If the number of fields to be transferred from the database is restricted by a Select list, more records fit into a single fetch than when Select * is used. The default value for the SAP profile parameter dbs/io_buf _size is 33,792 (bytes) and should not be changed unless recommended explicitly by SAP.

Guideline Value for Array Fetch

The guideline response time for optimal array fetches is under 10 ms per selected record. The actual runtime greatly depends on the WHERE clause, the index used, and how effectively the data is stored.

Declare, Prepare, and Open

Other database operations that may be listed in the SQL trace are Dec 1 are, Prepare, and Open. The Declare operation defines what is known as a cursor to manage data transfer between ABAP programs and a database, and also assigns an ID number to the cursor. This cursor ID is used for communication between SAP work processes and the database system.

Prepare operation

In the subsequent Prepare operation, the database process determines the access strategy for the statement. In the STATEMENT field, the statement is to be seen with a variable . To reduce the number of relatively time-consuming Prepare operations, each work process of an application server retains a certain number of already parsed SQL statements in a special buffer (SAP cursor cache). Each SAP work process buffers the operations DECLARE, PREPARE , OPEN, and EXEC in its SAP cursor cache. Once the work process has opened a cursor for a DECLARE operation, the same cursor can be used repeatedly until it is displaced from the SAP cursor cache after a specified time because the size of the cache is limited.

The database does not receive the concrete values of the WHERE clause (Mandt = 100 , and so on) until the OPEN operation is used. A PREPARE operation is necessary for only the first execution of a statement, as long as that statement has not been displaced from the SAP cursor cache. Subsequently, the statement, which has already been prepared (parsed), can always be reaccessed with OPEN or REOPEN.

First and subsequent executions

shows the SQL trace for the second run of the same report. Since the DECLARE and PREPARE operations are executed in the report's first run, our example shows only the OPEN operation.

Network problems

If you have identified an SQL statement with a long runtime, you should activate the trace again for further analysis. It is useful to perform the trace at a time of high system load and again at a time of low system load. If you find that the response times for database accesses are high only at particular times, this indicates throughput problems in the network or database access (for example, an I/0 bottleneck). For further information on these problems, please see Chapter 2, Section 2.2.2, Identifying Read/
Write (I/0) Problems. If, on the other hand, the response times for database access are poor in general (not only at particular times), the cause is probably an inefficient SQL statement, which should be optimized. When evaluating database response times, remember that the processing times of SQL statements are measured on the application server. The runtime shown in the trace includes not only the time required by the database to furnish the requested data, but also the time required to
transfer data between the database and the application server. If there is a performance problem in network communication, the runtimes of SQL statements will increase.