sql trace

[Former Member]

Hi Gurus,

What is SQL Trace and how to use this and why we use this,

if I create secondary indexes how can I test the performace of that index in SQL Trace.

if any body knows please replie this.

Regards,

Shashikumar.G.

[Former Member]

Hi

The SQL Trace part of the Performance Trace tool allows you to see how the OPEN SQL statements that you use in ABAP programs are converted to standard SQL statements (see Embedded SQL) and the parameters with which the embedded SQL statements are passed to the database system.

Overview

While the trace is switched on, the SQL Trace function records all database activity by a particular user or group of users. The R/3 System takes OPEN SQL statements and converts them in to embedded SQL statements that it passes to the database. It is the embedded SQL statements, their parameters, return codes, and the number of entries retrieved, inserted, or deleted that are recorded in the SQL Trace file. The log file also contains the runtime of the statement and the place in the application program from which it was called.

The SQL trace tells you:

The SQL statements executed by your program.

The values that the system uses for particular database access and changes.

How the system converts ABAP Open SQL statements (such as SELECT) into Standard SQL statements.

Where your application executes COMMITs.

Where your application repeats the same database access.

The database accesses and changes that occur in the update part of your application.

Rewards points if helpful.

rgrds

shazia

[Former Member]

Hi

You talking about option in transaction ST05. You can see all SQL actions and tables in given program run by this trace.

Enter st05,start traicing,run your program,enter again to st05 end end tracing,see the report.

Very usefull tool.

Regards

Yossi

[Former Member]

SQL Trace is a performance analysis tool which helps in identifying the trouble points in the program. The transaction for SQL trace is ST05 and select the SQL trace checkbox and click on "Activate Trace with Filter" and ensure that you filter it based on your user id. Once you set this, run your program/FM/transaction and after the process is done go to ST05 and deactivate the trace. Click on Display Trace to analyze the program/FM and see if there are any chance of improvements.

For the indexes the system automatically triggers the indexes the only way to see if the indexes are working or not is to do a SQL trace before and after the indexes and see if the indexes helped or not.

hith

Sunil Achyut

[Former Member]

HI Shashi

SQL trace can be done thru the transaction ST05. This will help us in analyzing the performance of a give select statement. Say for example you want to check the performance of a program. First come to ST05 click on "TRace On" option, then execute your program. Then put the trace off and view the trace results. You can see the time take for your each SQL statement. Double click on the "Select" which you want to see, and see whether it is using the indexes, if it is using the indexes it shall give you the same saying Index scan and the index being used for this select.

Reward point if helpful!!

~Ranganath

[Former Member]

hi,

goto ST05 transaction code..for checking the performance of the written code.

Reward with points if helpful.

[Former Member]

Is there no detailed description available ... I expected some references.

In the meantime:

Open a second mode, execute your program in the first mode at least once, better several times to fill buffers and caches

Start trace in second mode,

Run program again

Switch off the trace and display the trace results.

The result is a very long result screen, showing each execution, very often with several lines per statement. That is usually too much.

Therefore go to 'table' 'summarize by sql statements'

The result is an overview with one line per statement! show number of executions,

duration, number of records etc.

And most important the explain! Select your problematic statement, use 'explain', the new screen shows you which index is used. Unfortunately, the explains can have very different layouts, depending on your database. Some are more easy and some more difficult to read, but the iindex is always displayed somewhere.

If your new index helps, then it will appear here in your next execution, better check status without index first and execute it again with index. Check the difference in runtime.

Siegfried