* 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.
*&---------------------------------------------------------------------*
*& 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.
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.
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.
