튜닝시 좀 더 쉽게 테이블 현황을 보여줄수 있는 프로그램입니다.

사내라서 첨부가 안되내요..ㅠ.ㅠ

*+---------------------------------------+
*| 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