how to work with SQL trace?

[Former Member]

helo experts can you please tell me the procedure for performance tuning using SQL TRACE?

[Former Member]

hi Sagarika,

Check this out

http://help.sap.com/saphelp_47x200/helpdata/en/d1/801f7c454211d189710000e8322d00/content.htm

Also refer

[Former Member]

Goto ST05 Transaction and select trace on ..

now enter ur transaction and run the transaction..

once you done transaction and then come back to ST05 - Trace off and display list.

here you are able to see time duration and corresponding select query..

some of the select query will have red color ,so this query is taking lot of time.

now we need to de performance tuning based on ur select query.

Try to keep primary key in where condition if not then try to create secondary index if it is Custom table..

Thanks

Seshu

[Former Member]

Hi,

Refer this thread:

Jogdand M B

[Former Member]

Hi,

Check this thread,

http://help.sap.com/saphelp_47x200/helpdata/en/d1/801f7c454211d189710000e8322d00/content.htm

Regards,

Step.

[Former Member]

Hi Sagarika,

SQL Trace Locate the document in its SAP Library structure

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.

· 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:

...

1. You launch the SQL Trace application.

2. You activate the SQL Trace.

3. You run the application that you want to trace.

4. You deactivate the SQL Trace.

5. You set filters optionally.

6. You evaluate the trace.

You can also evaluate a trace that already exists.

The SQL Trace also provides functions for file administration.

Note

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.

Regards,

prasad

[Former Member]

Hai Sagarika,

This is documentation regarding sql trace. Go through this . I hope this will help you out.....

The SQL trace is a tool, which allows displaying and analyzing the contents for the database calls, which are made by the reports and transactions written in ABAP/4. It monitors programs and transactions on the database level. With the help of this facility for every open SQL instructions, you can display, about which SQL Embedded (DECLARE, OPEN, FETCH) Statement have been executed, besides analyzing the system performance.

Steps to Creation

• From R3 screen, select system –-> Utilities –-> SQL trace. Or Enter transaction ST05.

• Click the trace on button.

• Enter the user name whose programs are going to be traced.

• Execute the program or transaction you want to trace.

• Return to SQL trace initial screen and press the button SQL trace off. This switching off is necessary because if it is not done then SQL trace will trace each and every program executed by a particular user. And it is quite expensive in terms of memory and time of the system.

Analyzing The Trace File

To analyze the created trace, press the button list trace. Using this file you can see exactly how the system handles database requests. The first screen of the SQL trace data file displays each measured database requests, the application made. The trace file records when the request occurred and its duration.

To display dictionary definition information about the table field, position the cursor on the table field and click on the DDIC info button. When this button is clicked, it displays system information like object name, table class, whether buffering is allowed or not i.e. information related to dictionary.

Explain SQL: This button provides the functionality, which includes the utility for providing detailed information about the SQL Operation Strategy followed by the underlying database system. You need to click on Explain SQL button. The system displays the execution plan for SQL statements. Here you can display the actual SQL statement like Select, which fields are being accessed, Table being accessed, all where conditions.

ABAP/4 Display Gives you the actual ABAP/4 code.

More information gives the detailed information for time, select statement, client, number of records selected etc. Replace variable will display the SQL statement with another variables.

[former_member194613]

Becasue of these reappearing question in the forum, I have written a blog on the SQL Trace.

/people/siegfried.boes/blog/2007/09/05/the-sql-trace-st05-150-quick-and-easy

Please read that carefully.

Siegfried

[Former Member]

Hi!

Goto ST05 Transaction and select trace on ..

now enter ur transaction and run the transaction..

once you done transaction and then come back to ST05 - Trace off and display list.here you are able to see time duration and corresponding select query..some of the select query will have red color ,so this query is taking lot of time.

now we need to de performance tuning based on ur select query.

Try to keep primary key in where condition if not then try to create secondary index if it is Custom table

[Former Member]

Hi

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 a particular database table of the ABAP program would be 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.

Starting the Trace:

To analyze a trace file, do the following:

...

Choose the menu path Test  Performance Trace in the ABAP Workbench or go to Transaction ST05. 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.

Stopping the Trace:

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-

Analyzing a Sample trace data:

PREPARE: Prepares the OPEN statement for use and determines the access method.

OPEN: Opens the cursor and specifies the selection result by filling the selection fields with concrete values.

FETCH: Moves the cursor through the dataset created by the OPEN operation. The array size displayed beside the fetch data means that the system can transfer a maximum package size of 392 records at one time into the buffered area.

[former_member194613]

There is a lot written, but nearly all answers miss the mayor point!

You should not start your analysis with the extended trace list but with the condensed SQL statement summary!

Based on the that summary it is very easy to find nearly all SQL problems.

Of course you will not understand the details from this short posting, but it is all written in the my blog.

/people/siegfried.boes/blog/2007/09/05/the-sql-trace-st05-150-quick-and-easy

If you once check out the SQL statement summary, then you will understand the difference.

Sometimes I am really wondering whether it is really worth the effort to write a blog or an answer in this forum.

Siegfried

[Former Member]

When I set a trace on for user...After i turn off the trace and click on List Trace, it prompts for user name/ Object name. I input the user name and the object name click the Display trace list however the system give message saying No SQL trace records available. I dont understand since I did activate trace for user.

Please help. Why are no traces generating or why cant I view the trace that was created. The trace was active before the user ran the program.

Edited by: thanu on Aug 2, 2008 3:01 AM

[Former Member]

and I did read the blog...

any leads appreciated.