topic Re: SQL trace in Application Development and Automation Discussions

SQL trace

Former Member — Mon, 06 Aug 2007 13:48:56 GMT

Hi,

Plz tel me how to use ST05 ( step wise ) for checking performance issues in a program.

Thanks,

Binay.

Re: SQL trace

Former Member — Mon, 06 Aug 2007 13:51:09 GMT

Hi,

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.

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

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.

Functions

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.

Look at the below link

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

Regards

Sudheer

Re: SQL trace

Former Member — Mon, 06 Aug 2007 13:55:13 GMT

just added that to a similar question in the General forum: Please try and come back with detailed questions

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

Re: SQL trace

Former Member — Tue, 07 Aug 2007 06:32:38 GMT

2. SQL Trace transaction ST05

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.

Regards,

Pavan

Re: SQL trace

seshatalpasai_madala — Tue, 07 Aug 2007 06:39:10 GMT

Hi,

1) CLick on Activate in ST05 before you do some DB operation ( Or you know that invloves DB operations)

2) Execute the DB operation by means of program or transaction execution

3) Come back to ST05 deactivate.

4) Display Trace.

Regards,

Sesh

Re: SQL trace

Former Member — Tue, 07 Aug 2007 09:18:48 GMT

Thanks for the points.

However, I would strongly emphazise the most important point:

Therefore go to drop down list box on the left 'table -> summarize by sql statements'

=> the following display is much more condensed and useful. It contains all what you need!

Siegfried

Re: SQL trace

Former Member — Mon, 27 Aug 2007 03:03:48 GMT

Hi all,

Attach below is my program. It seems like the execution time is too long...

Can you guys help me out? I have tried to minimize the execution time by using SELECT ... FOR ALL ENTRIES but to no avail....

I also used CLIENT SPECIFIED field as selection criteria...


*&---------------------------------------------------------------------*
*& Program    : YZZ_SDPROJCONTROL                                      *
*& Author     : FF-MOHDMF (ACN)                                        *
*& GSKC Forum :                                                        *
*&---------------------------------------------------------------------*
*& Description                                                         *
*&                                                                     *
*& To create a project controlling report based on the internal order  *
*& and its project number.                                             *
*&---------------------------------------------------------------------*
*& Modification Log                                                    *
*&                                                                     *
*& Date       GSKC#    Modified By                                     *
*& Chg Req    Version  Description of Changes                          *
*&                                                                     *
*&---------------------------------------------------------------------*
REPORT  YZZ_SDPROJCONTROL NO STANDARD PAGE HEADING
                         MESSAGE-ID zi
                         LINE-SIZE 148.

* table declaration ----------------------------------------------------
tables: vbak,
        coas,
        kna1,
        bkpf,
        bseg,
        cobk,
        vbfa,
        coep,
        ekkn,
        ekbe,
        vbpa,
        skat,
        makt.

* data declaration -----------------------------------------------------
TYPES: BEGIN OF t_vbak2,
        vbeln    LIKE vbak-vbeln,
        auart    LIKE vbak-auart,
        vkorg    LIKE vbak-vkorg,
        kunnr    LIKE vbak-kunnr,
        flg(1) TYPE c,
      END OF t_vbak2.

DATA: it_vbak TYPE TABLE OF t_vbak2 WITH HEADER LINE.

TYPES: BEGIN OF t_vbak,
        vbeln    LIKE vbak-vbeln,
        auart    LIKE vbak-auart,
        vkorg    LIKE vbak-vkorg,
        kunnr    LIKE vbak-kunnr,
      END OF t_vbak.
DATA: wa_vbak TYPE t_vbak.

TYPES: BEGIN OF t_coas,
        aufnr    LIKE coas-aufnr,
        ktext    LIKE coas-ktext,
        kdauf    LIKE coas-kdauf,
        kokrs    like coas-kokrs,
        objnr    like coas-objnr,
        xblnr    like bkpf-xblnr,      "For downpayment logic
      END OF t_coas.

DATA: wa_coas TYPE t_coas.

DATA: it_coas TYPE TABLE OF t_coas WITH HEADER LINE.

types: begin of t_coep,
        kokrs like coep-kokrs,
        belnr like coep-belnr,
        objnr like coep-objnr,
        perio like coep-perio,
        gjahr like coep-gjahr,
        BEKNZ like coep-BEKNZ,
        wkgbtr like coep-wkgbtr,
        kstar  like coep-kstar,
        mbgbtr like coep-mbgbtr,
      end of t_coep.
data: it_coep type table of t_coep with header line.

DATA: BEGIN OF it_error OCCURS 0,
        aufnr   LIKE coas-aufnr,
      END OF it_error.

DATA: BEGIN OF it_name OCCURS 0,
        vbeln LIKE vbpa-vbeln,
        kunnr LIKE kna1-kunnr,
        name1 LIKE kna1-name1,
      END OF it_name.

DATA: BEGIN OF it_kna1 OCCURS 0,
        kunnr LIKE kna1-kunnr,
        name1 LIKE kna1-name1,
      END OF it_kna1.

DATA: BEGIN OF it_vbpa OCCURS 0,
        vbeln LIKE vbpa-vbeln,
        kunnr LIKE vbpa-kunnr,
      END OF it_vbpa.

types: begin of t_bkpf,
          bukrs like bkpf-bukrs,
          belnr like bkpf-belnr,
          gjahr like bkpf-gjahr,
          xblnr like bkpf-xblnr,
      end of t_bkpf.
DATA: it_bkpf TYPE TABLE OF t_bkpf WITH HEADER LINE.

data: begin of it_bseg occurs 0,
          bukrs like bseg-bukrs,
          gjahr like bseg-gjahr,
          belnr like bseg-belnr,
          bschl like bseg-bschl,
          pswbt like bseg-pswbt,
          augbl like bseg-augbl,
          shkzg like bseg-shkzg,
          hkont like bseg-hkont,
          zfbdt like bseg-zfbdt,
      end of it_bseg.
data: it_bseg1 like it_bseg occurs 0 with header line.

types: begin of t_vbfa,
          vbelv like vbfa-vbelv,
          posnv like vbfa-posnv,
          vbeln like vbfa-vbeln,
          posnn like vbfa-posnn,
          vbtyp_n like vbfa-vbtyp_n,
          erdat like vbfa-erdat,
          matnr like vbfa-matnr,
          rfwrt like vbfa-rfwrt,
          rfmng like vbfa-rfmng,
      end of t_vbfa.
data: it_vbfa type table of t_vbfa with header line.

data: begin of it_final occurs 0,
          kokrs like coas-kokrs,          "Sales Org
          kdauf like coas-kdauf,          "Project No
          aufnr like coas-aufnr,          "Internal order
*          prdcat type string,             "Product / Category
*          prdcat(15) type C,             "Product / Category
          prdcat(15) type N,             "Product / Category

          ktext like coas-ktext,          "Description
          peryr type string,              "Period / Year
          dmbtr like bseg-dmbtr,          "Amount
          qty type I,                     "Quantity
          docno type string,              "DocNo
      end of it_final.

data: it_final1 like it_final occurs 0 with header line.

data: begin of it_ekkn occurs 0,
          ebeln like ekkn-ebeln,
          ebelp like ekkn-ebelp,
          aufnr like ekkn-aufnr,
      end of it_ekkn.

data: begin of it_ekbe occurs 0,
          belnr like ekbe-belnr,
          budat like ekbe-budat,
          wrbtr like ekbe-wrbtr,
          ebeln like ekbe-ebeln,
          ebelp like ekbe-ebelp,
          shkzg like ekbe-shkzg,
      end of it_ekbe.

data: begin of it_cobk occurs 0,
          kokrs like cobk-kokrs,
          belnr like cobk-belnr,
          gjahr like cobk-gjahr,
          refbn like cobk-refbn,
      end of it_cobk.
* selection-screen -----------------------------------------------------
SELECTION-SCREEN BEGIN OF BLOCK blk1 WITH FRAME TITLE text-001.
SELECT-OPTIONS: s_vkorg FOR  vbak-vkorg OBLIGATORY no-extension no intervals,
                s_vbeln FOR  vbak-vbeln OBLIGATORY,
                s_kunnr FOR  vbak-kunnr,
                s_shipto FOR  vbpa-kunnr.

selection-screen skip 1.
parameter:  p_cdown as checkbox default 'X'.
SELECTION-SCREEN END OF BLOCK blk1.
*----------------------------------------------------------------------*
*    AT SELECTION SCREEN
*----------------------------------------------------------------------*
AT SELECTION-SCREEN.

data: l_aufnr like coas-aufnr.

  REFRESH it_vbak. CLEAR it_vbak.
  REFRESH it_error. CLEAR it_error.

  SELECT vbeln auart vkorg kunnr FROM vbak
    INTO wa_vbak
   WHERE vbeln IN s_vbeln
     AND vkorg IN s_vkorg
     AND kunnr IN s_kunnr.


   IF wa_vbak-auart NE 'KB'.
     it_error-aufnr = wa_vbak-vbeln.
     APPEND it_error. CLEAR it_error.
   ELSE.
     CLEAR wa_coas.
     SELECT SINGLE aufnr ktext kdauf kokrs objnr FROM coas client specified
       INTO wa_coas
      WHERE bukrs = wa_vbak-vkorg
        AND kdauf = wa_vbak-vbeln
        and mandt = sy-mandt.
     IF NOT wa_coas IS INITIAL.
       move: wa_coas-aufnr to l_aufnr.
       shift l_aufnr left deleting leading '0'.
       concatenate 'PROJ' l_aufnr into wa_coas-xblnr.
       APPEND wa_coas to it_coas.
     ELSE.
       it_error-aufnr = wa_vbak-vbeln.
       APPEND it_error. CLEAR it_error.
     ENDIF.

     it_vbak-vbeln = wa_vbak-vbeln.
     it_vbak-auart = wa_vbak-auart.
     it_vbak-vkorg = wa_vbak-vkorg.
     it_vbak-kunnr = wa_vbak-kunnr.
     APPEND it_vbak. CLEAR it_vbak.
   ENDIF.

  ENDSELECT.

*----------------------------------------------------------------------*
*    START OF SELECTION
*----------------------------------------------------------------------*
START-OF-SELECTION.

  perform select_data.          "Select header data

  if p_cdown = 'X'.
      perform dwn_pay_logic.
  endif.

  perform sales_value.
  perform prod_mat_list.
  perform app_serv_rev.
*  perform app_serv_cost.
  perform int_order_po.
  perform int_order_misc.
  perform display_header.
  perform display_footer.
*&---------------------------------------------------------------------*
*&      Form  display_header
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM display_header .

loop at it_vbak.


  READ TABLE it_coas WITH KEY kdauf = it_vbak-vbeln.
  IF SY-SUBRC NE 0.
    CLEAR it_coas.
  ENDIF.

  READ TABLE it_kna1 WITH KEY kunnr = it_vbak-kunnr.
  IF SY-SUBRC NE 0.
    CLEAR it_kna1.
  ENDIF.

  READ TABLE it_name WITH KEY vbeln = it_vbak-vbeln.
  IF SY-SUBRC NE 0.
    CLEAR it_name.
  ENDIF.

  NEW-PAGE.
  SKIP.
  WRITE : /122 'Date :'(004), SY-DATUM DD/MM/YY, SY-UZEIT,
          /47 'PROJECT CONTROLLING REPORT'(005),
          122 'User :'(006), SY-UNAME.

  SKIP.
  ULINE AT 1(147).
  WRITE :   /1 sy-vline,
             4 'Criteria'(007),
            25  ':',
            27 'Project Number'(008),
            44 ':',
            46 it_vbak-vbeln,
            59 '; Internal Order'(009),
            78 ':',
            80 it_coas-aufnr,
           147 sy-vline.

  WRITE :   /1 sy-vline,
             4 'Project Description'(010),
            25  ':',
            27 it_coas-ktext,
           147 sy-vline.

  WRITE :   /1 sy-vline,
             4 'Customer/Sold-to'(011),
            25  ':',
            27 it_kna1-kunnr,
            35 it_kna1-name1,
           147 sy-vline.

  WRITE :   /1 sy-vline,
             4 'Ship-to'(012),
            25  ':',
            27 it_name-kunnr,
            35 it_name-name1,
           147 sy-vline.
  SET BLANK LINES ON.
  ULINE AT 1(147).

** Item Header
  write  :  /1  sy-vline,
                'Product/Category'(013),
            25  'Description'(014),
            60  'Period/Year'(015),
            80  'Amount'(016),
           100  'Quantity'(017),
           120  'DocNo.'(019),
           147  sy-vline.
  write  :  /1  sy-vline, 147 sy-vline.
  perform write_details.

endloop.

ENDFORM.                    " display_header
*&---------------------------------------------------------------------*
*&      Form  select_data
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM select_data .

  IF it_vbak[] IS INITIAL.
       message e999 with 'No data available.Please ensure correct selection.'(003).
  else.

  REFRESH it_kna1. CLEAR it_kna1.
  SELECT kunnr name1 FROM kna1
    INTO TABLE it_kna1
    FOR ALL ENTRIES IN it_vbak
    WHERE kunnr = it_vbak-kunnr.

    REFRESH it_vbpa. CLEAR it_vbpa.
    SELECT vbeln kunnr FROM vbpa
      INTO TABLE it_vbpa
      FOR ALL ENTRIES IN it_vbak
      WHERE vbeln = it_vbak-vbeln
        AND kunnr IN s_shipto
        AND parvw = 'WE'.

  REFRESH it_name. CLEAR it_name.
    LOOP AT it_vbpa.

      SELECT SINGLE name1 FROM kna1
        INTO it_name-name1
        WHERE kunnr = it_vbpa-kunnr.

      IF NOT it_name-name1 IS INITIAL.
        it_name-vbeln = it_vbpa-vbeln.
        it_name-kunnr = it_vbpa-kunnr.
        APPEND it_name. CLEAR it_name.
      ENDIF.
    ENDLOOP.
  endif.

ENDFORM.                    " select_data
*&---------------------------------------------------------------------*
*&      Form  dwn_pay_logic
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM dwn_pay_logic .

data: l_xblnr(15) type C.
*      l_hkont like bseg-hkont.

  select bukrs belnr gjahr xblnr from bkpf
      into table it_bkpf
      for all entries in it_coas
        where xblnr = it_coas-xblnr
          and blart = 'DZ'
          and bukrs = it_coas-kokrs.

          if not it_bkpf[] is initial.      "Got downpayment, so pick details
              select bukrs gjahr belnr bschl pswbt augbl shkzg hkont zfbdt from bseg
                  into table it_bseg
                  for all entries in it_bkpf
                    where bukrs = it_bkpf-bukrs
                      and gjahr = it_bkpf-gjahr
                      and belnr = it_bkpf-belnr
                      and bschl = '19'.
          endif.

          if not it_bseg[] is initial.
              select bukrs gjahr belnr bschl pswbt augbl shkzg hkont zfbdt from bseg
                  into table it_bseg1
                  for all entries in it_bseg
                    where bukrs = it_bseg-bukrs
                      and gjahr = it_bseg-gjahr
                      and belnr = it_bseg-augbl
                      and bschl = '09'.
          endif.

          loop at it_bseg.                    "Downpayment details
            read table it_bkpf with key bukrs = it_bseg-bukrs
                                        belnr = it_bseg-belnr
                                        gjahr = it_bseg-gjahr binary search.

            read table it_coas with key kokrs = it_bkpf-bukrs
                                        xblnr = it_bkpf-xblnr binary search.

            select single txt20 from skat        "pick prdcat/hkont desc
                   into it_final-ktext
                       where saknr = it_bseg-hkont.

              if it_bseg-augbl ne space.      "Got cleared downpayment
                  loop at it_bseg1 where belnr = it_bseg-augbl
                                     and bukrs = it_bseg-bukrs.
*                      clear it_final.
*                      select single txt20 from skat        "pick prdcat/hkont desc
*                           into it_final-ktext
*                               where saknr = it_bseg-hkont.

                      move: it_bseg1-zfbdt to it_final-peryr,
                            it_bseg1-pswbt to it_final-dmbtr,
                            it_bseg1-belnr to it_final-docno,
                            it_bseg1-hkont to it_final-prdcat.
                      move: it_coas-kokrs to it_final-kokrs,
                            it_coas-aufnr to it_final-aufnr,
                            it_coas-kdauf to it_final-kdauf.
                      if it_bseg1-shkzg = 'H'.
                          it_final-dmbtr = it_final-dmbtr * -1.
                      endif.
                      shift it_final-prdcat left deleting leading '0'.
                      shift it_final-aufnr left deleting leading '0'.
                      concatenate it_final-peryr+4(2) it_final-peryr(4) into it_final-peryr separated by '/'.
                      append it_final.
                  endloop.
              else.                            "No cleared downpayment
                  move: it_bseg-hkont to it_final-prdcat,
                        it_bseg-zfbdt to it_final-peryr,
                        it_bseg-pswbt to it_final-dmbtr,
                        it_bseg-belnr to it_final-docno,
                        it_bseg-hkont to it_final-prdcat.
                      move: it_coas-kokrs to it_final-kokrs,
                            it_coas-aufnr to it_final-aufnr,
                            it_coas-kdauf to it_final-kdauf.

                      if it_bseg1-shkzg = 'H'.
                          it_final-dmbtr = it_final-dmbtr * -1.
                      endif.
                      shift it_final-prdcat left deleting leading '0'.
                      shift it_final-aufnr left deleting leading '0'.
                      concatenate it_final-peryr+4(2) it_final-peryr(4) into it_final-peryr separated by '/'.
                  append it_final.
              endif.
          endloop.

ENDFORM.                    " dwn_pay_logic
*&---------------------------------------------------------------------*
*&      Form  display_footer
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM display_footer .

  skip 2.

  loop at it_error.
    at first.
      write: /10 'Sales order number entered is not a project!'(018).
    endat.
      write: /10 it_error-aufnr.
  endloop.
ENDFORM.                    " display_footer
*&---------------------------------------------------------------------*
*&      Form  write_details
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM write_details .

    sort it_final ascending by kokrs kdauf aufnr prdcat.
    sort it_final1 by kokrs kdauf aufnr ktext.

    shift it_coas-aufnr left deleting leading '0'.

    loop at it_final where kdauf = it_vbak-vbeln and aufnr = it_coas-aufnr .

        write: /01 sy-vline,
                   it_final-prdcat,
               25  it_final-ktext,
               60  it_final-peryr,
               75  it_final-dmbtr currency bseg-pswsl,
              100  it_final-qty no-zero,
              120  it_final-docno,
              147  sy-vline.

              at end of prdcat.
                sum.
                write: /01 sy-vline,
                        60 'Total'(001),
                        75 it_final-dmbtr,
                       100 it_final-qty no-zero,
                       147 sy-vline.
                write: /01 sy-vline, 147 sy-vline.
              endat.


              at end of aufnr.
                  write: /01 sy-uline(147).

                  loop at it_final1 where kokrs = it_final-kokrs
                                      and kdauf = it_final-kdauf
                                      and aufnr = it_final-aufnr.
                      at new aufnr.
                          write: /01  sy-vline,
                                      'Details from Internal Order'(002),
                                  147 sy-vline.
                          write: /01  sy-vline,
                                 147  sy-vline.
                      endat.

                      write: /01 sy-vline,
                                 it_final1-prdcat no-zero,
                             25  it_final1-ktext,
                             60  it_final1-peryr,
                             75  it_final1-dmbtr currency bseg-pswsl,
                            100  it_final1-qty no-zero,
                            120  it_final1-docno,
                            147  sy-vline.

                      at end of prdcat.
                        sum.
                        write: /01 sy-vline,
                                60 'Total'(001),
                                75 it_final1-dmbtr,
                               100 it_final1-qty no-zero,
                               147 sy-vline.
                        write: /01 sy-vline, 147 sy-vline.
                      endat.

                      at last.
                        write: /01 sy-uline(147).
                      endat.
                  endloop.
              endat.
    endloop.

ENDFORM.                    " write_details
*&---------------------------------------------------------------------*
*&      Form  sales_value
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM sales_value .

data: l_erdat like vbfa-erdat,
      l_belnr like cobk-belnr.

refresh it_coep.

  select vbelv posnv vbeln posnn vbtyp_n erdat from vbfa
    into table it_vbfa
    for all entries in it_coas
        where vbelv = it_coas-kdauf
          and vbtyp_n = 'M'.

  loop at it_coas.
*   clear it_final.
    shift it_coas-aufnr left deleting leading '0'.
    move: it_coas-kokrs to it_final-kokrs,
          it_coas-kdauf to it_final-kdauf,
          it_coas-aufnr to it_final-aufnr.

          loop at it_vbfa where vbelv = it_coas-kdauf.
              move: it_vbfa-erdat(4) to l_erdat.

              select single belnr from cobk into l_belnr
                  where refbn = it_vbfa-vbeln
                    and kokrs = it_coas-kokrs
                    and gjahr = l_erdat.

*****Start do performance tuning here!!
*                select kokrs belnr objnr perio gjahr beknz wkgbtr kstar mbgbtr from coep
*                  into table it_coep
*                    where kokrs = it_coas-kokrs
*                      and belnr = l_belnr
*                      and mbgbtr ne 0.
                select kokrs belnr objnr perio gjahr beknz wkgbtr kstar mbgbtr from coep client specified
                  into table it_coep
                    where mandt = sy-mandt
                      and kokrs = it_coas-kokrs
                      and belnr = l_belnr
                      and mbgbtr ne 0
                      .
*****End do performance tuning here!!


              loop at it_coep.
                  select single txt20 from skat into it_final-ktext
                      where saknr = it_coep-kstar
                        and spras = sy-langu.

                    move: it_coep-kstar to it_final-prdcat,
                          it_coep-wkgbtr to it_final-dmbtr,
                          it_coep-mbgbtr to it_final-qty.
                    concatenate it_coep-perio it_coep-gjahr into it_final-peryr separated by '/'.
                    shift it_final-prdcat left deleting leading '0'.
                    move: it_vbfa-vbeln to it_final-docno.
                    append it_final.
              endloop.
*                endselect.
          endloop.

  endloop.
ENDFORM.                    " sales_value
*&---------------------------------------------------------------------*
*&      Form  prod_mat_list
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM prod_mat_list .

  refresh: it_vbfa.

    select vbelv posnv vbeln posnn vbtyp_n erdat matnr rfwrt rfmng from vbfa
       into table it_vbfa
       for all entries in it_coas
          where vbelv eq it_coas-kdauf
            and vbtyp_n eq 'R'
            and rfwrt ne 0.

  loop at it_coas.
    shift it_coas-aufnr left deleting leading '0'.
    move: it_coas-kokrs to it_final-kokrs,
          it_coas-kdauf to it_final-kdauf,
          it_coas-aufnr to it_final-aufnr.

          loop at it_vbfa where vbelv = it_coas-kdauf.
              select single maktx from makt
                  into it_final-ktext
                  where spras eq sy-langu
                    and matnr eq it_vbfa-matnr.

              move: it_vbfa-matnr to it_final-prdcat,
                    it_vbfa-rfwrt to it_final-dmbtr,
                    it_vbfa-rfmng to it_final-qty,
                    it_vbfa-vbeln to it_final-docno.
              shift it_final-prdcat left deleting leading '0'.
              concatenate it_vbfa-erdat+4(2) it_vbfa-erdat(4) into it_final-peryr separated by '/'.

              append it_final.
          endloop.
          clear it_final.
  endloop.

ENDFORM.                    " prod_mat_list
*&---------------------------------------------------------------------*
*&      Form  int_order_po
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM int_order_po .

    select ebeln ebelp aufnr from ekkn
        into table it_ekkn
        for all entries in it_coas
          where aufnr = it_coas-aufnr.

      if not it_ekkn[] is initial.
          select belnr budat wrbtr ebeln ebelp from ekbe
              into table it_ekbe
              for all entries in it_ekkn
                  where ebeln = it_ekkn-ebeln
                    and ebelp = it_ekkn-ebelp
                    and bewtp = 'Q'.
      endif.

      loop at it_coas.
          clear it_final1.

          move: it_coas-aufnr to it_final1-prdcat,
                it_coas-aufnr to it_final1-aufnr,
                it_coas-kokrs to it_final1-kokrs,
                it_Coas-kdauf to it_final1-kdauf,
                it_coas-ktext to it_final1-ktext.
          shift it_final1-prdcat left deleting leading '0'.
          shift it_final1-aufnr left deleting leading '0'.

                loop at it_ekkn where aufnr = it_coas-aufnr.
                         loop at it_ekbe where ebeln = it_ekkn-ebeln
                                           and ebelp = it_ekkn-ebelp.

                              concatenate it_ekbe-budat+4(2) it_ekbe-budat(4) into it_final1-peryr separated by '/'.
                              move: it_ekbe-wrbtr to it_final1-dmbtr,
                                    it_ekbe-belnr to it_final1-docno.

                              case it_ekbe-shkzg.
                                  when 'H'.
                                      it_final1-dmbtr = it_final1-dmbtr * -1.
                              endcase.
                              append it_final1.
                         endloop.

                endloop.

      endloop.

ENDFORM.                    " int_order_po
*&---------------------------------------------------------------------*
*&      Form  int_order_misc
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM int_order_misc .

refresh: it_coep.

*      select kokrs belnr objnr perio gjahr BEKNZ wkgbtr from coep
*          into table it_coep
*          for all entries in it_coas
*              where kokrs = it_coas-kokrs
*                and objnr = it_Coas-objnr
*                and wrttp = '11'.
        select kokrs belnr objnr perio gjahr BEKNZ wkgbtr from coep client specified
            into table it_coep
            for all entries in it_coas
                    where kokrs = it_coas-kokrs
                      and objnr = it_coas-objnr
                      and wrttp = '11'
                      and mandt = sy-mandt.
          loop at it_coas.
                clear: it_final1.
                shift it_coas-aufnr left deleting leading '0'.
                move: it_coas-aufnr to it_final1-aufnr,
                      it_coas-kdauf to it_final1-kdauf,
                      it_coas-kokrs to it_final1-kokrs.

                loop at it_coep where kokrs = it_coas-kokrs
                                  and objnr = it_coas-objnr.
                    move: 'Miscellaneous Expenses' to it_final1-ktext,
                          it_coep-wkgbtr to it_final1-dmbtr,
                          it_coep-belnr to it_final1-docno.
                    concatenate it_coep-perio it_coep-gjahr into it_final1-peryr separated by '/'.

                    if it_coep-BEKNZ = 'H'.
                        it_final1-dmbtr = it_final1-dmbtr * -1.
                    endif.

                    append it_final1.
                endloop.
          endloop.

ENDFORM.                    " int_order_misc
*&---------------------------------------------------------------------*
*&      Form  app_serv_rev
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM app_serv_rev .

data: l_erdat like vbfa-erdat.

    refresh: it_vbfa.

    select vbelv posnv vbeln posnn vbtyp_n erdat matnr rfwrt rfmng from vbfa
       into table it_vbfa
       for all entries in it_coas
          where vbelv eq it_coas-kdauf
            and vbtyp_n eq 'M'.

    loop at it_vbfa.
        l_erdat = it_vbfa-erdat(4).

        select kokrs belnr gjahr refbn from cobk
*          into table it_cobk
          appending table it_cobk
            for all entries in it_coas
              where refbn = it_vbfa-vbeln
                and kokrs = it_Coas-kokrs
                and gjahr = l_erdat.
    endloop.

***Insert COEP selection here. Requirements starting from (H) onwards in func tech.

ENDFORM.                    " app_serv_rev

Re: SQL trace

Former Member — Mon, 27 Aug 2007 06:36:59 GMT

enter SQL Trace – ST05

<b>Starting the Trace:</b>

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-

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

Re: SQL trace

Former Member — Mon, 27 Aug 2007 06:45:40 GMT

i had explained all the steps

reward if usefull

Re: SQL trace

Former Member — Mon, 27 Aug 2007 07:58:07 GMT

one more time, but the last time,

follow the explanations abouut the SQL trace and use the summary as I have explained. Even if nobody else recommends it, I knwso what I am talking about.

Add the summary here, this will show where the problems of your program are.

Siegfried

Re: SQL trace

Former Member — Tue, 28 Aug 2007 01:05:42 GMT

HI Naresh,

Thanks for the reply. I have done all of the required steps. I can see that the FETCH operation of almost all select statements give me red color. The duration of this FETCH operation is huge! How to resolve this? Thanks.

Re: SQL trace

Former Member — Tue, 28 Aug 2007 07:19:18 GMT

sorry, you did not follow my explanation ...

Execute your program at least once before you trace it, otherwise you measure disk performance, that is not what you want.

Create the SQL Statement Summary, there are not fetches anymore.

Of course, the fetch operations are the slowest, the rest is preparation, red color is irrelevant, you must see the numbers in relation to the found records. The time is in microseconds, I looks larger than it is

Write down total time and time for top statements!

Siegfried

Re: SQL trace

Former Member — Tue, 28 Aug 2007 07:48:01 GMT

Hi Siegfried,

I have done the steps you provide above. I can see that the duration for select statement of table COAS and COEP is very long. My question: How do I go about reducing this duration?

Thanks.