SQL Performance trace part two

The trace list is the same regardless of the database system. Data operations that require more than 100 milliseconds are marked red. Each section of the list has a header identifying the user, transaction, R/3 work process number, and R/3 client ID. The expanded trace list has the following columns:

hh:mm:ss:ms Time stamp indicating the begin of sending the database request (hour:minute:second:millisecond)

Duration Duration of the database operation in microseconds (varies according to system load)

Program Name of the program calling the SQL statement

Object Name of the table or view, as taken from the SQL statement

Oper Database operation to be executed

Curs Name of the database cursor

ArrSz Size of packets containing the data records sent from the database server to the application server

Rec Number of data records sent by the database operation

RC Return code of the database system

Statement The text of the SQL statement


DATA BASE OPERATIONS:

During the processing of an SQL statement performing a read access, the following operations occur:

PREPARE: This operation parses the SQL statement and converts it to a statement that can be processed by the database. In particular, the access strategy is determined. Placeholders are used for variables in the SQL statement.

OPEN/REOPEN: This operation replaces the placeholders in the SQL statement with concrete values, and opens the database cursor.

FETCH: This operation transfers the database records from the database server to the application server. The number of records transferred per FETCH is given by dividing the length of the data record to be read in bytes by the value of dbs/io_buf_size, which, by default, is set to 33 792 bytes.

An SQL statement performing a write access is processed in the same way, except that EXEC/REEXEC is used instead of OPEN/REOPEN and FETCH.

Database cursors per R/3 work process are buffered in the SAP cursor cache. This enables the PREPARE operation to be omitted for repeated executions of similar SQL statements. Displacement occurs in the SAP cursor cache.

EXPLAINING SQL USAGE IN A REPORT:

To access Explain SQL from the trace list, choose Trace ® Explain SQL. Alternatively, use the Explain SQL button. Explain SQL is performed 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.

Double-click the table frame to display a dialog box containing statistical information on the table and information on all indexes of the table. Double-click an index to display statistical information on it.

Use the Analyze button to refresh the statistical information on a table and its indexes. To enable the cost-based optimizer to function effectively, the statistical information it uses must be updated after each change to a table or its indexes. Use the Index Statistics button to display an overview of the statistical information across all indexes.

16

RELATED POST

SQL PERFORMANCE TRACE PART ONE

SAP ABAPCONSTANTS

SAP ABAPTABLE CONTROL

SAP ABAPCONVERT

SAP ABAPCREATE

SAP ABAPDATA PART ONE


No comments:

Post a Comment