SQL TRACE

Use

Initial Screen

Starting the Trace   Stopping the Trace

Analyzing Performance Data

Display Filter  Displaying Trace Records

SQL Trace

Use
The SQL Trace function is an on-demand log of selected SQL statements that are issued against the database through the Open SQL Engine. The SQL Trace can be switched on or off dynamically. The log format is database independent. Besides the SQL statement text, each log record contains information about the point in time when the statement was executed, its duration, its input parameters and results (where applicable) as well as context information.

Features
The SQL Trace is especially useful for:
Development

SQL Trace can help JDO, enterprise beans, servlet and JSP developers to learn which kind of database accesses their code produces.

1.      Performance analysis

Typically, performance issues are caused by inefficient database accesses. In this case SQL Trace can be used to show the issued SQL statements and their duration, thus helping to identify inefficient SQL statements.

Activities

Typically, you should use the SQL Trace when you need to check the behavior of a particular application. This is the following scenario:

• You launch the SQL Trace application.
• You activate the SQL Trace.
• You run the application that you want to trace.
• You deactivate the SQL Trace.
• You set filters optionally.
• You evaluate the trace.

You can also evaluate a trace that already exists.

The SQL Trace also provides functions for file administration.SQL Trace is less suitable for detecting general performance issues.Therefore, we do not recommend that you keep it constantly activated, as it consumes additional resources.

Initial Screen

Calling
You can call the initial screen of the test tool using transaction code ST05 or by choosing Test ® Performance Trace in the ABAP Workbench.

Functions

The following functions are available on the initial screen:

Select trace:

• Select the trace mode SQL Trace, Enqueue Trace, RFC Trace, or Table Buffer Trace. You can select mutliple trace modes simultaneously.

Select trace function:

• Start the trace recording.
• Stop the trace recording.
• Branch to trace list, detailed list, or time-sorted list.
• Branch to Explain SQL to analyze an SQL statement without an explicit trace file.

Trace files are managed by the system. Thus they can be saved, like any other object; saved trace files can be displayed and deleted.

Trace Status

A trace can only be activated once on any application server. The Trace Status display informs you whether another user in the system has already activated a particular trace.

Starting the Trace

Prerequisites

You can only switch on the Performance Trace for a single instance. You should already have decided the scope and targets of your performance analysis.

Procedure

To analyze a trace file, do the following:

Choose the menu path Test ® Performance Trace in the ABAP Workbench.

• The initial screen of the test tool appears. In the lower part of the screen, the status of the Performance Trace is displayed. This provides you with information as to whether any of the Performance Traces are switched on and the users for which they are enabled. It also tells you which user has switched the trace on.
• Using the selection buttons provided, set which trace functions you wish to have switched on (SWL trace, enqueue trace, RFC trace, table buffer trace).
• If you want to switch on the trace under your user name, choose Trace on.
  If you want to pass on values for one or several filter criteria, choose Trace with Filter.
  Typical filter criteria are: the name of the user, transaction name, process name, and program name.
• Now run the program to be analyzed.
  • You will normally analyze the performance trace file immediately. In this case, it is a good idea to use a separate session to start, stop, and analyze the Performance Trace
  • If you are shown trace kernel errors on the initial screen (for example, not enough storage space available), you must first remove the errors or have them removed by your system administrator.
  • The selected trace types can be changed as required during a performance trace interval (time between switching on and off the trace). The user (user group) must remain unchanged.

Result

The results of the trace recording are written to a trace file. If trace records are overwritten during the trace interval, the system displays a message to inform you when you analyze the trace file.

The results of the trace recording are stored to ten trace files. Overwriting trace records, however, cannot be entirely excluded in this case either.

The Performance Trace records all database access calls, table buffer calls, remote calls, or calls for user lock activity. These measurements can affect the performance of the application server where the trace is running. To preserve system performance, you should therefore turn off the trace as soon as you finish recording your application.

Stopping the Trace

Prerequisites

You have started the trace and finished running the program that you want to analyze.
For performance reasons, you should switch off the traces as soon as you have finished recording.

Procedure

To deactivate the trace:

• Choose Test ®Performance Trace in the ABAP Workbench.
  The initial screen of the test tool appears. It contains a status line displaying the traces that are active, the users for whom they are active, and the user who activated them.
• Select the trace functions that you want to switch off.
• Choose Deactivate Trace.
  If you started the trace yourself, you can now switch it off immediately. If the performance trace was started by a different user, a confirmation prompt appears before deactivation-

Result

The results of the trace are stored in one or more trace files. You can then analyze the performance data stored in the trace file. See also, Analyzing Performance Data.

Analyzing Performance Data

Prerequisites

Once you have switched off the performance trace, you can analyze the data  The data is analyzed, even repeatedly, until its trace records are overwritten in the trace file. The trace files are managed by the SAP system. You can therefore keep a trace file for editing and delete it later through the menu Performance Trace->Save Trace, Display Saved Trace, and Delete Saved Trace.

Procedure: Overview

Before you start analyzing the trace records, you must first switch off the Performance Trace. (It is also possible to display a trace without switching it off beforehand. In this case, however, the display procedure is also recorded in the trace file, in accordance with the set filter criteria for the trace.

Before displaying the trace records, you can use a display filter to specify the records to be edited and the information that you want to look at.

When you display the trace records, you can choose between a basic list and an extended list. Both lists display an overview of the logged actions and performance data. In addition, you can display a trace list sorted by time.

In both lists – the simple trace list and the extended list – you have the same range of functions for analyzing the listed statements and other performance data.

The time-sorted trace lists is only slightly different from the trace lists mentioned above.  For more information on the transaction, process type, client, and user, call up the function Display->User, Transaction and so on…… in the Goto menu.

Other analysis options depend on the trace types that you are using. See also:
SQL Trace or
Enqueue Trace or
RFC Trace.

Display Filter

Prerequisites

You have switched off the performance trace and have opened the display filter after choosing Display Trace or the corresponding menu entry from the initial screen of the performance trace.
Use
You can use the display filter (Set Restrictions for Displaying Trace) to restrict the number of logged trace records that are displayed on the basic list (Trace List), detailed list, or time-sorted list.

Features

If you do not enter any selections, all of the trace records are selected.
Specifying the Trace Type to Display
The SAP system also initializes this parameter from the system environment. The default trace type is SQL trace. If you start the display filter directly after the recording, the trace type appears as it was last configured.
Specifying the Trace Interval
The system initializes the trace interval from 00:00:00 to the current system time on today’s date. However, if you start the display filter directly after the recording, the trace interval is set from the start time to the end time of the recording.
Note that if you are working on a distributed system, where the clocks on the database server and the application servers are not synchronized, any times determined automatically by the system may be inaccurate, which in turn may mean that not all trace records are displayed.
Enter Further Selections

Update Selection Criteria
With this button, your display filter entries are updated. You can also use the ENTER key for this.
Select Operator for Selection Conditions
With this button, you can change the default option and select and operator. To do so, place the cursor on the relevant parameter.
Initialize the Selection Criteria
If you repeat the performance trace during a session, the default filter parameters are set to your last selections. Use the Initialize All Selection Criteria function (right mouse button) to restore the original defaults.
Delete Selected Selection Criterion
Choose this button to reset restrictions for individual parameters (see also Other Selection Options).

Other Filters

You can also filter the trace record display using the following parameters:

User

Objects

• SQL trace: Table name that the SQL statement refers to
• Enqueue trace: Lock object that the statement refers to
• RFC trace: Instance on which a function is executed
• Table buffer trace: Name of the table that the access is limited too

Duration

Operations

• SQL trace: Database operations
• Enqueue trace: Operation for the lock object
• RFC trace: Characteristics of the execution (client/server)
• Table buffer trace: Name of the buffer operation

The default filters are:

• For User, the current user
• For Objects, the display of the system tables is suppressed. Currently, these are the tables D010*, REPOLOAD, REPOSRC, REPOTEXT, DYNPSOURCE, DYNPLOAD, DYNPTXTLD, DDLOG.

The Duration and Operations parameters are not initialized

You can specify a numeric value for the Duration. This is measured in microseconds. For all other parameters, you can enter a pattern or name. In addition to alphanumeric characters, you can also use the wildcard characters ‘*’ and ‘+’ in patterns.

You can also change the operator in a specification for further filtering. To do this, position the cursor on the relevant parameter and choose Selection Options. A dialog box then appears in which you can specify whether trace records satisfying the condition should be included (green traffic light) or excluded (red traffic light) from the selection.

For the duration, restricting to an interval of 1000 to 5000 seconds may be of use.

Displaying Lists of Trace Records

Prerequisites

You have chosen the Display Trace function and specified the value range for the display in the Display Filter, where you have also displayed the trace list, detailed list, or time-sorted list.
Lists
The detailed list contains the complete trace list. The detailed list merely contains three additional columns for the display.
In the time-sorted list, you can display information about the transaction, process type, client, and user by choosing Display -> User, Transaction, and so on, in the Goto menu.

There is a range of analysis functions that you can use both on the trace list and on the detailed list. All lists have the same functions. You can switch between the trace and detailed lists via a button.

The first line of the list contains a subheader, which remains unchanged for all trace records of a program to be analyzed. It contains the following information:

– Name of the transaction, process identification number, process type, client, and user name.

The next line contains the following headers:

Duration   Execution time of the statement. The time is displayed in the format milliseconds.microseconds.

Object name

• SQL trace record: Name of the database table
• Enqueue trace record: Name of the lock object
• RFC trace record: Shortened name of the instance on which the function module was executed
• Buffer trace record: Name of the buffered table

Oper

• SQL trace record: Name of the operation to be performed on the database. See also: Measured Database Operations
• Enqueue trace record: Name of the lock operation

· RFC trace record: Client | Server. (If a function is specified as the Client, this means that it wass called “remotely“. If it is specified as the Server, this means that the function was made available and executed.)

· Buffer trace record: Buffer operation

Rec Number of records

• SQL Trace: Number of records retrieved or processed and passed between the SAP System and the database.
• Enqueue trace: Number of granules
• RFC Trace: Not used
• Buffer trace: Number of processed records

RC – Return code of the logged statement

Statement – Short form of the logged statement

• Depends on the trace type

The runtime (duration) is highlighted in the list if it exceeds a given threshold value (100000 microseconds). This is declared in the type group “SQLT” as the constant “SQLT_DURATION_NEG”. You can change this value by changing the constant accordingly.

Note that the duration can only be as precise as clock of your hardware platform. The duration cannot be less than the time required by the hardware. If the execution time of the statement is less than this time, the duration will be zero.

Detailed List

To switch from the basic list to the extended list, choose Detailed List. The extended list contains three extra display columns:

hh:mm:ss.ms – The time at which the record was executed (in the form hours: minutes: seconds: milliseconds).

Program – Name of the program that executed the logged statement.

Curs

12.  SQL trace record: Number of the cursor (link to cursor cache) used to find the database entries.

13.  Enqueue and RFC trace records: Not used.

Additional Functions

Analyzing Trace Records

Sort list

Display formatted logged statements

Definition of the corresponding ABAP Dictionary object for SQL and Enqueue trace

Display the logged statement in the source code

Display the access plan for a logged SQL statement

Format identical select statements in the trace list

Summarizing the Trace List

Switch between the two lists

Different Trace Types

The system displays different trace types in different colors.

Standard Functions

A range of standard SAP list functions is also available to help you print the list, navigate and search in the list, or save the list to an operating system file. These functions can be called via the corresponding menu entries, buttons, or function keys.

Analyzing Trace Records

Prerequisites

You have displayed the trace records that you want to analyze in a basic or extended list.

Functions

Sorting the Trace List and the Extended Trace List

You can sort the list by any of the parameters in the list heading, that is, transaction name, process identification number, process type, client, and user name. To sort the list, position the cursor on the relevant column and choose the push button  Sort or the appropriate menu option.

Switching from the Trace List to the Extended Trace List

To switch from the basic simple list to the extended list, choose the pushbutton  Extended List or choose the appropriate menu option.

Detailed Display and Replacing Placeholders

When the logged statement is formatted, you can specify whether to replace the placeholders in the statement by the current variables or leave them in the statement and list the variables separately. If there are no variables, the two display forms are identical. To display the statement, double-click its short form or the menu option Detail, or click the magnifying glass icon.
To replace the placeholders with the current variables, choose the push button  Replace Placeholder in the SQL Statement, or choose the appropriate menu option.

Displaying Information about DDIC Objects

If you wish to look at the Dictionary description for the object (table or lock object) to which the statement refers, position the cursor on the object and choose DDIC info. Alternatively, you can choose the same function from the menu. If the current statement contains several DDIC object (for example, a join), the Object Name row contains the first object to appear in the statement.

If the log entry is an RFC entry, the column contains a shortened version of the name of the instance on which the function module is executed. In this case, you cannot display a Dictionary definition.

You can display further DDIC Dictionary information by opening the actual definition of the object in the DDIC.

Execution Plan for SQL Statements

To display the execution plan of a selected SQL statement, place the cursor on the statement and choose the pushbutton  Explain SQL Statement. The SQL statements for which an execution plan can be displayed depends on the database system that you are using. The execution plan for a SELECT statement under the Oracle database system looks like this:

Displaying the Source Code

To switch to the ABAP source code containing the current statement in the log, position the cursor on the short form display of the statement and choose the pushbutton  Display Call Positions in the ABAP Program or the menu option of the same name.

Note that the source code cannot always be displayed. For example, if the call comes from the SAP kernel, you cannot branch to the program code.

Identical Selects

When you are analyzing a trace log, it can be particularly useful to find out if there are any identical select statements. You can do this by choosing the menu option Identical selects in the menu Trace List. The system compiles a list of any SQL statements that are executed more than once. You can then eliminate any SQL statements that are repeated or superfluous.

Summarizing the Trace List

You can summarize the select statements in order to get an overview of the total execution time and the total number of records found. To do this, use the menu option Trace List->Summarize Trace by SQL Statement. You can also group the trace records by table access using the menu option Trace List->Combined Table Accesses.

Embedded SQL 

One of the difficulties of connecting a programming language with an SQL interface is the transfer of retrieved data records. When the system processes an SQL statement, it does not know how big the result will be until it has made the selection. The result consists of table entries, which all have the same structure. The system has to transfer these records to the calling program in the form of a data structure, for example an array, that is known to the calling program.

The disadvantage of an array is its static definition. You have to specify the size of an array before runtime. However, because you cannot know the size of the dataset the system will return, you must define a very large array to avoid an overflow.

To circumvent this problem, the SAP System translates ABAP Open SQL statements into Embedded SQL. To do this, the system defines a cursor. The cursor is the logical connection to the selected datasets in the database and is used to regulate the data transfer between ABAP programs and the database.

During every FETCH operation, the database passes one or more data records to the database interface.

Measured Database Operations

Each SQL statement is broken down into database operations by the SAP System The SQL Trace allows you to measure the runtime of each of these operations:

Logical Sequence of Database Operations

Database requests are interconnected and always occur in the same logical sequence.

The DECLARE function defines and numbers the cursor. DECLARE precedes the PREPARE function.

Use PREPARE to prepare a specific SQL statement, such as:

select * from sflight where carrid eq ‘LH’.

and define the access method before the system can transfer the request to the database. During this preparation, the system is concerned only with the structure of the SQL statement and not with the values it contains.

The OPEN function takes the prepared SELECT statement and completes it with the correct values. In the above example, OPEN would issue the field carrid the value LH.

FETCH passes the entries from the database to the database interface of the SAP System All of the database operations required to execute an SQL statement are linked by the same cursor ID.

If the SQL statement makes changes in the database (INSERT, UPDATE, DELETE), PREPARE is followed by EXEC, which executes the statement

If the system can refer back to an SQL statement that has already been prepared, there is no PREPARE operation, and the statement is executed using REOPEN or REEXEC as appropriate

Buffering 

The SAP System ensures that data transfer between the SAP System and the database system is as efficient as possible. To do this, it uses the following techniques:

·        Table buffering. The program accesses data from the buffer of the application server.

·        Database request buffering. Individual database entries are not read or passed to the database until required by an OPEN SQL statement.

When you analyze trace records, you should also examine the system’s buffering mechanisms.

Table Buffering

For tables can be either partially or fully buffered (refer toBuffering Database Tables.), an OPEN SQL statement only accesses the database if the results of the statement are not already in the buffer Consequently, the SQL Trace does not contain a command or command sequence for every OPEN SQL statement. On the other hand, every SQL statement in the trace file has been sent to the database and executed.

Buffering of Database Requests

To keep the number of runtime-consuming PREPARE calls small, each an application’s work processes hold a certain number of already translated SQL statements in a special buffer. By default, a process holds up to 250 statements.

If the system must execute a specific OPEN SQL, the system first checks whether this statement is stored in the “Statement cache“. If the statement is in the cache, the system executes it immediately using a REOPEN (SELECT) or a REEXEC (INSERT, UPDATE, DELETE).

If the statement is not buffered, a PREPARE operation prepares it for the subsequent OPEN/EXEC. The system administers the buffer according to the LRU algorithm (“least recently used”). When space is needed for new statements, the statements that are rarely used are deleted. As a result of the LRU algorithm, the statement must prepare frequently used statements usually only once.

An application server buffers the DECLARE, PREPARE, OPEN, and EXEC requests within the cursor cache of one work process. As a result, once the system opens a cursor for a DECLARE operation, it can use this cursor over and over again within the same work process.

Analyzing a Sample SQL Data File

When you create an SQL trace file for an application, you can see exactly how the system handles database operations. In a sample application, a report reads, and later changes, records on the ABAP Dictionary table SFLIGHT using ABAP Open SQL statements. Since the table SFLIGHT is not buffered, the system first needs to access the database to retrieve the records. In the sections below, the data file from the sample application is analyzed.

Read Access

The first screen of the SQL trace file displays each measured database request the application made. The trace file records when the request occurred and its duration. The ABAP Dictionary table involved in the request is also listed.

The system measured several database operations involved in retrieving records from SFLIGHT:

Write Access

An sample SQL file analyzing a request that changes data in the Table D010SINF might look like this:

The example shows the system inserting new records into the table (INSERT). As in the first example, where the system carried out a read-only access, the system needs to prepare the database operations (PREPARE) that change the database records. The PREPARE operation precedes the other operations.