SAP SQL Tuning Aid with Oracle RDBMS Statistics

With Compliments from:
Jayanta Narayan Choudhuri
http://www.geocities.com/ojnc

*+---------------------------------------+
*| Author Jayanta Narayan Choudhuri      |
*|        Flat 302                       |
*|        395 Jodhpur Park               |
*|        Calcutta 700 068               |
*|      Email sss@cal.vsnl.net.in        |
*+---------------------------------------+

* 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

See Also
Security Audit Logs - Intentionally Blocked User

Fast Links:
Get help regarding your Basis problems
Do you have a SAP Basis Question?

SAP Basis Books
SAP System Administration, Security, Authorization, ALE, Performance Tuning Reference Books

SAP Basis Tips
SAP BC Tips and Basis Components Discussion Forum

Administration In SAP - Sapgui, Unix, SAP ITS, Router, Client Copy and IDES 

Best regards,
SAP Basis, ABAP Programming and Other IMG Stuff
http://www.erpgreat.com

All the site contents are Copyright © www.erpgreat.com and the content authors. All rights reserved.
All product names are trademarks of their respective companies.  The site www.erpgreat.com is in no way affiliated with SAP AG. 
Every effort is made to ensure the content integrity. Information used on this site is at your own risk. 
 The content on this site may not be reproduced or redistributed without the express written permission of 
www.erpgreat.com or the content authors.