sql trace

[Former Member]

what can we measure by using sql trace and runtime anlysys

[Former Member]

For SQL trace have a look at below link.

http://help.sap.com/saphelp_erp2005/helpdata/en/d1/801f89454211d189710000e8322d00/content.htm

For runtime analysis have a look at below link.

http://help.sap.com/saphelp_nw04/helpdata/en/c6/617cafe68c11d2b2ab080009b43351/content.htm

You have posted so many queries. If you find the answers are satisfactory then plz mark all the useful answers.

Best Regards,

Vibha Deshmukh

*Please mark all the helpful answers

[Former Member]

SQL Trace can be seen in Xn: ST05

ABAP Runtime Analysis : SE30

ABAP runtine Error Analysis : ST22

Cheers!

[shishupalreddy]

Hi,

Using SQL Trace

we can trace out complete reflections in sap systems by running a tcode or program or any sap object

[pavankumar_hukkeri]

HI raghuveer,

These are the Tools provided for Performance Analysis

1Run time analysis transaction SE30

This transaction gives all the analysis of an ABAP program with respect to the database and the non-database processing.

2.SQL Trace transaction ST05

The trace list has many lines that are not related to the SELECT statement in the ABAP program. This is because the execution of any ABAP program requires additional administrative SQL calls. To restrict the list output, use the filter introducing the trace list.

The trace list contains different SQL statements simultaneously related to the one SELECT statement in the ABAP program. This is because the R/3 Database Interface - a sophisticated component of the R/3 Application Server - maps every Open SQL statement to one or a series of physical database calls and brings it to execution. This mapping, crucial to R/3s performance, depends on the particular call and database system. For example, the SELECT-ENDSELECT loop on the SPFLI table in our test program is mapped to a sequence PREPARE-OPEN-FETCH of physical calls in an Oracle environment.

The WHERE clause in the trace list's SQL statement is different from the WHERE clause in the ABAP statement. This is because in an R/3 system, a client is a self-contained unit with separate master records and its own set of table data (in commercial, organizational, and technical terms). With ABAP, every Open SQL statement automatically executes within the correct client environment. For this reason, a condition with the actual client code is added to every WHERE clause if a client field is a component of the searched table.

To see a statement's execution plan, just position the cursor on the PREPARE statement and choose Explain SQL. A detailed explanation of the execution plan depends on the database system in use.

Runtime Analysis

Switch on RTA Dynamically within ABAP Code

*To turn runtim analysis on within ABAP code insert the following code

SET RUN TIME ANALYZER ON.

*To turn runtim analysis off within ABAP code insert the following code

SET RUN TIME ANALYZER OFF.

OR SE30

SQL Trace

SQL trace (ST05) provides the developer with the ability to analyze database select statements. Simply execute ST05 to turn on SQL trace, then execute the statement/program you want to analyze. Now turn off SQL trace using ST05

And click on list trace to view the details.

You can also perform traces on other items such as authorization objects.

Authorization trace analysis

1. Open two sessions

2. Execute transaction ST01 in one of the sessions

3. Select the authorization checkbox, note the other traces

you can perform (SQL, RFC, Table Buffer etc)

4. Click the 'Trace On' button

5. Within your other session execute the transaction/report

you want to trace or get the user in question to do it

6. Return to the session where you turned the trace on and

click on 'Trace Off' otherwise it will continue to record all authorization checks

7. Click on the 'Analysis' button

8. Enter appropriate data into selection screen such as

Username, type of trace records (i.e. Authorization check)

9. Click on the Execute button.

10. Report displaying trace results will now be displayed

GO THROUGH THIS

Tools provided for Performance Analysis

Following are the different tools provided by SAP for performance analysis of an ABAP object

Run time analysis transaction SE30

This transaction gives all the analysis of an ABAP program with respect to the database and the non-database processing.

SQL Trace transaction ST05

The trace list has many lines that are not related to the SELECT statement in the ABAP program. This is because the execution of any ABAP program requires additional administrative SQL calls. To restrict the list output, use the filter introducing the trace list.

The trace list contains different SQL statements simultaneously related to the one SELECT statement in the ABAP program. This is because the R/3 Database Interface - a sophisticated component of the R/3 Application Server - maps every Open SQL statement to one or a series of physical database calls and brings it to execution. This mapping, crucial to R/3s performance, depends on the particular call and database system. For example, the SELECT-ENDSELECT loop on the SPFLI table in our test program is mapped to a sequence PREPARE-OPEN-FETCH of physical calls in an Oracle environment.

The WHERE clause in the trace list's SQL statement is different from the WHERE clause in the ABAP statement. This is because in an R/3 system, a client is a self-contained unit with separate master records and its own set of table data (in commercial, organizational, and technical terms). With ABAP, every Open SQL statement automatically executes within the correct client environment. For this reason, a condition with the actual client code is added to every WHERE clause if a client field is a component of the searched table.

To see a statement's execution plan, just position the cursor on the PREPARE statement and choose Explain SQL. A detailed explanation of the execution plan depends on the database system in use.

SAP Performance Tools

Runtime Analysis

SQL Trace

Using GroupBy

Following are the different tools provided by SAP for performance analysis of an ABAP object

Run time analysis transaction SE30

This transaction gives all the analysis of an ABAP program with respect to the database and the non-database processing.

SE30 - Runtime Analysis Tool

This runtime analysis tools allows the ABAP/4 programmer to trace the tables used by the SAP dialog/reports programs. In the Analyze button, you can see four more buttons like:-

Hit List

Displays the execution time of each statement in the program.

Tables

Displays the tables accessed during run time.

Group hit list

Displays the execution time of all the statements and grouping them based on the type of command. E.g. performs, SQL and internal tables used.

Hierarchy

Displays the execution times of each statement in the actual order in which were executed. Uses indentation to indicate the level of nesting of statements within subroutines.

Any tables use by the transaction or program can be easily traced with the runtime analysis tools.

Go to transaction SE30

Type in the transaction code you want to analyze

4.6x

In the Restriction section: select the TMP -> Temporary variant

Click the Change button

Click the Duration/type

Select None for Aggregation

Save the variant and execute again

After finishing the process, click back to SE30

Use F3 to move back to the initial screen of SE30

Click the Analyze Button

Click Goto -> Object-centered hit list -> Database tables

The SQL Trace is especially useful for:

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.

SQL Trace transaction ST05

The trace list has many lines that are not related to the SELECT statement in the ABAP program. This is because the execution of any ABAP program requires additional administrative SQL calls. To restrict the list output, use the filter introducing the trace list.

The trace list contains different SQL statements simultaneously related to the one SELECT statement in the ABAP program. This is because the R/3 Database Interface - a sophisticated component of the R/3 Application Server - maps every Open SQL statement to one or a series of physical database calls and brings it to execution. This mapping, crucial to R/3s performance, depends on the particular call and database system. For example, the SELECT-ENDSELECT loop on the SPFLI table in our test program is mapped to a sequence PREPARE-OPEN-FETCH of physical calls in an Oracle environment.

The WHERE clause in the trace list's SQL statement is different from the WHERE clause in the ABAP statement. This is because in an R/3 system, a client is a self-contained unit with separate master records and its own set of table data (in commercial, organizational, and technical terms). With ABAP, every Open SQL statement automatically executes within the correct client environment. For this reason, a condition with the actual client code is added to every WHERE clause if a client field is a component of the searched table.

To see a statement's execution plan, just position the cursor on the PREPARE statement and choose Explain SQL. A detailed explanation of the execution plan depends on the database system in use.

Performance Tuning is useful mainly reducing load on database. It is very important aspect while writing the programs/FM etc.....

SE30 Runtime Analysis , here you can find the load on database

ST05 SQL Trace

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:

... 1. 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.

2. Using the selection buttons provided, set which trace functions you wish to have switched on (SWL trace, enqueue trace, RFC trace, table buffer trace).

3. 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.

4. 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:

... 1. 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.

2. Select the trace functions that you want to switch off.

3. 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.

Regards

Pavan

IF HELPFULL PLEASE Reward Points.......