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