SAP R/3 форум ABAP консультантов
Russian ABAP Developer's Club

Home - FAQ - Search - Memberlist - Usergroups - Profile - Log in to check your private messages - Register - Log in - English
Blogs - Weblogs News

SAP SQL Tuning Aid with Oracle RDBMS Statistics



 
Post new topic   Reply to topic    Russian ABAP Developer's Club Forum Index -> Performance tuning | Производительность
View previous topic :: View next topic  
Author Message
admin
Администратор
Администратор



Joined: 01 Sep 2007
Posts: 1636

PostPosted: Sun Sep 09, 2007 10:25 pm    Post subject: SAP SQL Tuning Aid with Oracle RDBMS Statistics Reply with quote

Code:
*+---------------------------------------+
*| Author Jayanta Narayan Choudhuri      |
*|        Flat 302                       |
*|        395 Jodhpur Park               |
*|        Calcutta 700 068               |
*|      Email [email protected]        |
*+---------------------------------------+

* SQL Tuning Aid in SAP
* ---------------------
* To tune SQLs effectively one must know relative row counts of tables
in the program.
* Also primary Keys & all indexes of all the selected tables are shown
all in 1 place.

* Then the ABAP programmer has to change navigation and logic to suit
indexes.
* The large tables are likely to be the "hot spots". 
* As a last resort it may be necessary to add a new Index to SAP or Z
tables.

* Try with BSEG MSEG A004 RFBLG KAPOL MSEG VBFA
* The Code is given below for SAP with Oracle RDBMS. Should be easy to
adapt to SQLServer Informix DB2,
* if you know a bit of the DBA side of things.


REPORT ZSQLTUNE.

TYPE-POOLS: slis.               "ALV Global types

***Table Declaration
TABLES:  dd02l.


***Internal Tables Declaration

TYPES:  BEGIN OF t_statsora,
            num_rows       TYPE i,
            avg_row_len    TYPE i,
            last_analyzed  TYPE ekbe-budat,
        END OF t_statsora.

TYPES:  BEGIN OF t_stats,
            tabname        TYPE dd02t-tabname,
            tabclass       TYPE dd02v-tabclass,
            num_rows       TYPE i,
            avg_row_len    TYPE i,
            last_analyzed  TYPE ekbe-budat,
            ddtext         TYPE dd02t-ddtext,
            index0(80)     TYPE c,      "DD03L
            index1(80)     TYPE c,      "1-6 from DD17S
            index2(80)     TYPE c,
            index3(80)     TYPE c,
            index4(80)     TYPE c,
            index5(80)     TYPE c,
            index6(80)     TYPE c,
        END OF t_stats.

DATA:   i_stats TYPE STANDARD TABLE OF t_stats,
        r_stats TYPE t_stats,
        r_statsora TYPE t_statsora,
        l_kount TYPE i.

DATA: secs(2)      TYPE n,
      rndnum       TYPE i,
      iscreated    TYPE i.

CONSTANTS: allmychoices(44) TYPE c VALUE
'ProgFuncBAdIFormSmrtObjtTcodWbObTblsHelpWhlp'.

DATA: schema(30)     TYPE c,
      idxnum(1)      TYPE n,
      windexname(30) TYPE c,
      posnum         TYPE dd03l-position,
      wfieldname(30) TYPE c,
      fldname        TYPE string.

FIELD-SYMBOLS: <fs_idx> LIKE r_stats-index2.


*&---------------------------------------------------------------------*
*                      SELECTION-SCREEN DESIGN                         
*
*&---------------------------------------------------------------------*
SELECTION-SCREEN: BEGIN OF BLOCK b1sels WITH FRAME TITLE text-001.
    SELECT-OPTIONS: stabname  FOR     dd02l-tabname.            "Abap
table
SELECTION-SCREEN: END OF BLOCK b1sels.

*&---------------------------------------------------------------------*
*                       INITIALIZATION EVENT                           
*
*&---------------------------------------------------------------------*
INITIALIZATION.
*&---------------------------------------------------------------------*
*                     AT SELECTION-SCREEN VALUE-REQUEST EVENT         
*
*&---------------------------------------------------------------------*

*&---------------------------------------------------------------------*
*                     AT SELECTION-SCREEN EVENT                       
*
*&---------------------------------------------------------------------*


*&---------------------------------------------------------------------*
*                      START-OF-SELECTION EVENT                       
*
*&---------------------------------------------------------------------*
START-OF-SELECTION.

  PERFORM f_validation.

  PERFORM f_retrieve_data.

  PERFORM f_process_data.

  PERFORM f_display_data.

*&---------------------------------------------------------------------*
*&      Form  F_VALIDATION
*&---------------------------------------------------------------------*
FORM f_validation.
ENDFORM.                    " F_VALIDATION

*&---------------------------------------------------------------------*
*&      Form  F_RETRIEVE_DATA
*&---------------------------------------------------------------------*
FORM f_retrieve_data .

    SELECT dd02v~tabname            "ABAP TableBName
           dd02v~tabclass
           dd02t~ddtext
      INTO CORRESPONDING FIELDS OF TABLE i_stats
      FROM dd02v INNER JOIN dd02t
           ON dd02v~tabname = dd02t~tabname
           AND dd02v~ddlanguage = dd02t~ddlanguage
           AND dd02t~ddlanguage = sy-langu
     WHERE dd02t~tabname IN stabname.

    SELECT sqltab AS tabname  "ABAP TableBName
           sqlclass AS tabclass
           ddtext
 APPENDING CORRESPONDING FIELDS OF TABLE i_stats
      FROM dd06v
     WHERE ddlanguage = sy-langu
       AND sqltab IN stabname.


ENDFORM.                    " F_RETRIEVE_DATA

*&---------------------------------------------------------------------*
*&      Form  F_PROCESS_DATA
*&---------------------------------------------------------------------*
FORM f_process_data .

    LOOP AT i_stats INTO r_stats.
        MOVE 0 TO l_kount.
        EXEC SQL.
         open c1 for
            select a.num_rows,
                   a.avg_row_len,
                   TO_CHAR(a.last_analyzed,'YYYYMMDD') As last_analyzed
              from USER_tables a
             where a.table_name = :r_stats-tabname
        ENDEXEC.

        DO.
          EXEC SQL.
            fetch next c1 INTO :R_STATSORA
          ENDEXEC.
          IF sy-subrc <> 0.
            EXIT.
          ENDIF.
          MOVE-CORRESPONDING r_statsora TO r_stats.
          EXIT.
        ENDDO.

        EXEC SQL.
           close c1
        ENDEXEC.

        r_stats-index0 = 'PK('.
        SELECT fieldname
               position
          INTO (wfieldname, posnum)
          FROM dd03l
         WHERE tabname = r_stats-tabname
           AND keyflag = 'X'
          ORDER BY position.
            IF r_stats-index0 = 'PK('.
               CONCATENATE r_stats-index0 wfieldname INTO
r_stats-index0.
            ELSE.
               CONCATENATE r_stats-index0 ',' wfieldname INTO
r_stats-index0.
            ENDIF.
        ENDSELECT.
        CONCATENATE r_stats-index0 ')' INTO r_stats-index0.

        idxnum = 0.
        SELECT indexname
               fieldname
               position
          INTO (windexname, wfieldname, posnum)
          FROM dd17s
         WHERE sqltab = r_stats-tabname
          ORDER BY indexname position.

            IF posnum = 1.
                IF idxnum <> 0.
                   CONCATENATE <fs_idx> ')' INTO <fs_idx>.
                ENDIF.
                ADD 1 TO idxnum.
                IF idxnum > 7.
                    CONCATENATE r_stats-index6 ' more!!!'  INTO
r_stats-index6 .
                    EXIT.
                ENDIF.
                CONCATENATE 'R_STATS-INDEX' idxnum INTO fldname.
                ASSIGN (fldname) TO <fs_idx>.

                CONCATENATE windexname '(' wfieldname INTO <fs_idx>.
            ELSE.
                CONCATENATE <fs_idx> ',' wfieldname INTO <fs_idx>.
            ENDIF.

        ENDSELECT.
        IF idxnum <> 0.
           CONCATENATE <fs_idx> ')' INTO <fs_idx>.
        ENDIF.

        MODIFY i_stats FROM r_stats.

    ENDLOOP.


ENDFORM.                    " F_PROCESS_DATA

*&---------------------------------------------------------------------*
*&      Form  F_DISPLAY_DATA
*&---------------------------------------------------------------------*
FORM f_display_data.

* Macro definition
  DEFINE m_fieldcat.
    ls_fieldcat-fieldname = &1.
    ls_fieldcat-tabname = &2.
    ls_fieldcat-ref_fieldname = &3.
    ls_fieldcat-ref_tabname = &4.
    ls_fieldcat-seltext_l = &7.
    ls_fieldcat-seltext_m = &7.
    ls_fieldcat-seltext_s = &7.
    ls_fieldcat-reptext_ddic = &7.
    ls_fieldcat-hotspot = &5.
    ls_fieldcat-fix_column = &6.
    append ls_fieldcat to lt_fieldcat.
  END-OF-DEFINITION.

  DEFINE m_sort.
    ls_sort-tabname   = &1.
    ls_sort-fieldname = &2.
    ls_sort-up        = 'X'.
    append ls_sort to lt_sort.
  END-OF-DEFINITION.


  DATA:
    ls_fieldcat TYPE slis_fieldcat_alv,
    lt_fieldcat TYPE slis_t_fieldcat_alv," Field catalog
    ls_sort     TYPE slis_sortinfo_alv,
    lt_sort     TYPE slis_t_sortinfo_alv," Sort table
    ls_keyinfo  TYPE slis_keyinfo_alv,
    ls_layout   TYPE slis_layout_alv.


  ls_layout-box_tabname   = 'I_STATS'.
  ls_layout-min_linesize   = 240.
  ls_layout-window_titlebar = 'Index Info & Oracle Statistics'..
  ls_layout-colwidth_optimize = 'X'.

  m_fieldcat 'TABNAME'          'I_STATS' 'TABNAME'     'DD02T'      '
' 'X' 'Table Name'.
  m_fieldcat 'TABCLASS'         'I_STATS' 'TABCLASS'    'DD02V'      '
' ' ' 'Class'.
  m_fieldcat 'NUM_ROWS'         'I_STATS' 'STYLE'       'ABDEMONODE' '
' ' ' 'Num Rows'.
  m_fieldcat 'AVG_ROW_LEN'      'I_STATS' 'STYLE'       'ABDEMONODE' '
' ' ' 'Avg.RowLen'.
  m_fieldcat 'LAST_ANALYZED'    'I_STATS' 'BUDAT'       'EKBE'       '
' ' ' 'LastAnalyzed'.
  m_fieldcat 'DDTEXT'           'I_STATS' 'DDTEXT'      'DD02T'      '
' ' ' 'Description'.

  m_fieldcat 'INDEX0'           'I_STATS' 'MATKX'       'MAKT'       '
' ' ' 'PrmKey'.
  m_fieldcat 'INDEX1'           'I_STATS' 'MATKX'       'MAKT'       '
' ' ' 'Index1'.
  m_fieldcat 'INDEX2'           'I_STATS' 'MATKX'       'MAKT'       '
' ' ' 'Index2'.
  m_fieldcat 'INDEX3'           'I_STATS' 'MATKX'       'MAKT'       '
' ' ' 'Index3'.
  m_fieldcat 'INDEX4'           'I_STATS' 'MATKX'       'MAKT'       '
' ' ' 'Index4'.
  m_fieldcat 'INDEX5'           'I_STATS' 'MATKX'       'MAKT'       '
' ' ' 'Index5'.
  m_fieldcat 'INDEX6'           'I_STATS' 'MATKX'       'MAKT'       '
' ' ' 'Index6'.


  CALL FUNCTION 'REUSE_ALV_LIST_DISPLAY'
       EXPORTING
              is_layout                = ls_layout
              it_fieldcat              = lt_fieldcat
        TABLES
              t_outtab    = i_stats.

  IF sy-subrc <> 0.
    MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
            WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  ENDIF.


ENDFORM.                    " F_DISPLAY_DATA
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    Russian ABAP Developer's Club Forum Index -> Performance tuning | Производительность All times are GMT + 4 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You cannot attach files in this forum
You can download files in this forum


All product names are trademarks of their respective companies. SAPNET.RU websites are in no way affiliated with SAP AG.
SAP, SAP R/3, R/3 software, mySAP, ABAP, BAPI, xApps, SAP NetWeaver and any other are registered trademarks of SAP AG.
Every effort is made to ensure content integrity. Use information on this site at your own risk.