* 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