Skip to Content
author's profile photo Former Member
Former Member

ST05 ( Performance Analysis)

Hi Friends,

I have written two Abap statements...

1) Using select single *

2) Using select upto 1 rows.

I know that select upto 1 rows is faster than select single * , but i want to see the perfomance of the above statements using Txcode ST05..

Can any of explain me ?

Full Points for sure those who explains with Step By Step.... my email-id is

vijayalaxmi_madiwala@yahoo.co.uk

Thnaks and advance

Vijaya

Add a comment
10|10000 characters needed characters exceeded

Related questions

5 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Nov 22, 2007 at 07:11 AM

    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.

    <b>Starting the Trace:</b>

    To analyze a trace file, do the following:

    ...

    Choose the menu path Test &#61614; 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 &#61614;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-

    <b>

    Analyzing a Sample trace data:</b>

    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.

    <b>Reward if usefull</b>

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member 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 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.....

      SQL Trace

      <b>Use</b>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.

      <b>Features</b>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.

      &#61489;&#61486; 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.

      Functions

      <b>The following functions are available on the initial screen:</b>

      <b>Select trace:</b>

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

      <b>Trace Status</b>

      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.

      <b>Starting the Trace</b>

      <b>Prerequisites</b>

      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.

      <b>Procedure</b>

      To analyze a trace file, do the following:

      1. Choose the menu path Test &#61614; 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

      <b>Performance Trace</b>

      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.

      <b>Result</b>

      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.

      <b>Stopping the Trace</b>

      <b>Prerequisites</b>

      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.

      <b>Procedure</b>

      To deactivate the trace:

      1. Choose Test &#61614;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-

      <b>Result</b>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.

      <b>Reward if usefull</b>

  • Posted on Nov 22, 2007 at 09:41 AM

    Read the introduction to the SQL trace:

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

    Write a program with select up to 1 rows and select single

    AND VERY import a second time

    select up to 1 rows and select single

    Run the SQL trace, check the summary, but check also the extended list where

    you see the 4 executions. Write down the 4 execution times.

    AND do the test several times, that is very important!

    Tell us your result. Or check for other threads, it was discussion 2 weeks ago.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 22, 2007 at 02:34 PM

    Vijaya - the difference isn't worth the effort trying to find out.

    Rob

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Nov 23, 2007 at 10:36 AM

    @Rob,

    you should never stop enthusiasm 😊

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 03, 2007 at 10:32 AM

    hi vijaya,

    u first write a program where u must have used both d select single * and select

    upto 1 rows.

    then go to ST05--->click on analyzer trace on,then u can see d effects.

    its better 2 use SE30

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.