topic Re: SQL Trace(ST05) and Run Time Analysys(SE30) in Application Development and Automation Discussions

SQL Trace(ST05) and Run Time Analysys(SE30)

Former Member — Tue, 29 Jan 2008 05:48:41 GMT

Hi ABAP Experts,

Can any one explain what is the use of

1) ST05 and

2) SE30

ponits will be given for clear cut explanation

Thanks and Regards

Vijaya

Re: SQL Trace(ST05) and Run Time Analysys(SE30)

Former Member — Tue, 29 Jan 2008 05:50:42 GMT

hope it will help you.

Pls reward if help.

SQL Trace

SQL trace(ST05) provides the developer with the ability to analyse database select statements. Simply execute ST05

to turn on SQL trace, then execute the statement/program you want to analyse. 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 authorisation objects.

Authorisation trace analysis 1. Open two sessions

2. Execute transaction ST01 in one of the sessions

3. Select the authorisation checkbox, note the other traces

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

4. Click the 'Trace On' button

5. Within your other session execte 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 athorisation 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

http://www.sapdevelopment.co.uk/perform/perform_sqltrace.htm

Some useful transaction related to this are ..

ST01 SAP system trace

ST02 Buffer statistics.

ST03 Workload analysis.

ST04 Database performance analysis.

ST05 SQL trace .

ST06 Operating system monitor ...

From the recorded SQL trace you can deduce:

· Which SQL statements your application carries out

· Which values the system uses for specific database accesses and changes

· How the system translates ABAP OPEN SQL commands (such as

SELECT) into standard SQL commands

· Where your application positions COMMIT statements

· Where your application makes repeated database accesses

· What database accesses or changes occur in the update section of your

application

Look at the below links, you will get the idea

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

http://www.sapbrain.com/TOOLS/SQLTRACE/SQL_TRACE.html

Check the following links:

http://www.sapbrainsonline.com/TOOLS/RUNTIMEanalysis/SAP_runtime_analysis.html

http://www.sapbrainsonline.com/TOOLS/SQLTRACE/SQL_TRACE.html

Re: SQL Trace(ST05) and Run Time Analysys(SE30)

Former Member — Tue, 29 Jan 2008 05:51:19 GMT

here you go:

/people/siegfried.boes/blog/2007/11/13/the-abap-runtime-trace-se30--quick-and-easy

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

Re: SQL Trace(ST05) and Run Time Analysys(SE30)

Former Member — Tue, 29 Jan 2008 05:52:28 GMT

Hope it will help you.

Pls reward if help.

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.

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.

STEPS

Run time analysis transaction SE30

In Transaction SE30, fill in the transaction name or the program name which needs to be analyzed for performance tuning.

For our case, let this be “ZABAP_PERF_TUNING”

After giving the required inputs to the program, execute it. After the final output list has been displayed, PRESS the “BACK” button.

On the original SE30 screen, now click on “ANALYZE” button.

The percentage across each of the areas ABAP/ Database/System shows the percentage of total time used for those areas and load on these areas while running the program . The lesser the database load faster the program runs.

SQL Trace – ST05

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.

Re: SQL Trace(ST05) and Run Time Analysys(SE30)

Former Member — Tue, 29 Jan 2008 06:07:56 GMT

Hi Vijaya,

ST05 & SE30 are Used in ABAP for Performance Optimization & Tunning of Code.

ST05 : Performance Analysis : is used to Analyze Overall dependency of Code.Means to Locate Tables used in Given Code.

SE30:Run Time Performance Analyse : is used to Understand DataBase Dependency of our Code.

Hope this would Clarifies You....!!!

Re: SQL Trace(ST05) and Run Time Analysys(SE30)

Former Member — Tue, 29 Jan 2008 06:17:28 GMT

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

From the time you turn on the trace function to the time you turn it off again, all database activity occurring either for a specific user or for an entire system is recorded. The SAP System takes OPEN SQL statements and converts them in to embedded SQL statements that it passes to the database and makes the results available. The embedded SQL statement and its parameters are recorded in the SQL Trace file. The results of the SQL statement, like return code, number of entries retrieved, inserted, or deleted by the database are recorded in the SQL Trace file as well. The log file also contains the runtime of the statement and the place in the application program, respectively transaction, from which it was called which enables additional analyses (supported by the SQL Trace function).

From the recorded SQL trace you can deduce:

· which SQL statements your application carries out

· which values the system uses for specific database accesses and changes

· how the system translates ABAP OPEN SQL commands (such as SELECT) into standard SQL commands

· where your application positions COMMIT statements

· where your application makes repeated database accesses

· what database accesses or changes occur in the update section of your application

Create a Trace (SQL Server Profiler)

To create a trace

On the File menu, click New Trace, and connect to an instance of SQL Server.

The Trace Properties dialog box appears.

Note:

The Trace Properties dialog box fails to appear, and the trace begins instead, if Start tracing immediately after making connection is selected. To turn off this setting, on the Tools menu, click Options, and clear the Start tracing immediately after making connection check box.

In the Trace name box, type a name for the trace.

In the Use the template list, select a trace template on which to base the trace, or select Blank if you do not want to use a template.

To save the trace results, do one of the following:

Click Save to file to capture the trace to a file. Specify a value for Set maximum file size. The default value is 5 megabytes (MB).

Optionally, select Enable file rollover to automatically create new files when the maximum file size is reached. You can also optionally select Server processes trace data, which causes the service that is running the trace to process trace data instead of the client application. When the server processes trace data, no events are skipped even under stress conditions, but server performance may be affected.

Click Save to table to capture the trace to a database table.

Optionally, click Set maximum rows, and specify a value.

Caution:

When you do not save the trace results to a file or table, you can view the trace while SQL Server Profiler is open. However, you lose the trace results after you stop the trace and close SQL Server Profiler. To avoid losing the trace results in this way, click Save on the File menu to save the results before you close SQL Server Profiler.

Optionally, select the Enable trace stop time check box, and specify a stop date and time.

To add or remove events, data columns or filters, click the Events Selection tab. For more information, see: How to: Specify Events and Data Columns for a Trace File (SQL Server Profiler)

Click Run to start the trace.

Runtime analysis:

se30 is the tcode for run time analysis, is a tool it

display report based on lps for abap pragram execution

time , data base load, system load. these are all will

display in micro seconds.this tool will use for Transaction

code, function modules , abap progrmes

In Transaction SE30, fill in the transaction name or the program name which needs to be analyzed for performance tuning.

For our case, let this be “ZABAP_PERF_TUNING”

After giving the required inputs to the program, execute it. After the final output list has been displayed, PRESS the “BACK” button.

On the original SE30 screen, now click on “ANALYZE” button.

SQL trace:

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

SE30

/people/siegfried.boes/blog/2007/11/13/the-abap-runtime-trace-se30--quick-and-easy

regards,

vasavi.

kindly reward if helpful.

Re: SQL Trace(ST05) and Run Time Analysys(SE30)

Former Member — Mon, 16 Nov 2009 13:57:10 GMT

Two good links....

http://www.sdn.sap.com/irj/scn/weblogs;jsessionid=(J2EE3417800)ID1304776450DB20120100481755260918End?blog=/pub/wlg/7769

http://www.sdn.sap.com/irj/scn/weblogs;jsessionid=(J2EE3417800)ID1304776450DB20120100481755260918End?blog=/pub/wlg/7205

Re: SQL Trace(ST05) and Run Time Analysys(SE30)

Former Member — Mon, 16 Nov 2009 15:51:02 GMT

Hi Vijaya,

Both the transactions are used for Performance optimization.

ST05 : This Tcode is used for SQL trace. It will give you the details related to database table access (eg. How much time the program took while accessing data from some specific table)

you have to run the SQL trace before executing the program. Once program executed, stop the SQL trace and see the result. It will give you detail idea for each and every table used by the program select queries.

SE30 : This is used to do runtime analysis of any program. And will give you the details about the program run time, database accessing time etc.

Hope this will help you.

Regards,

Sourabh Batwara