SAP SQL Tuning Aid with Oracle RDBMS Statistics

* 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: 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 ')' INTO .
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 .

CONCATENATE windexname '(' wfieldname INTO .
ELSE.
CONCATENATE ',' wfieldname INTO .
ENDIF.

ENDSELECT.
IF idxnum <> 0.
CONCATENATE ')' INTO .
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