튜닝시 좀 더 쉽게 테이블 현황을 보여줄수 있는 프로그램입니다.
사내라서 첨부가 안되내요..ㅠ.ㅠ
*+---------------------------------------+
*| 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
댓글 11
-
양키
2010.07.09 15:40
유용한 프로그램이 될것같네요 ^^ 자료 공개 감사합니다. -
Risky
2010.07.09 18:55
우와, 많은 도움이 되었습니다. 감사합니다. -
카루이안
2010.07.12 07:24
좋은자료군요. 감사합니다. 잘쓰도록 하겠습니다. -
MadMax
2010.07.13 09:15
감사합니다.
-
아밥 잭
2010.07.13 18:16
지금 관련 작업을 하고있는데 정말 감사합니다. 잘 보겠습니다. -
icarus
2010.07.15 17:24
튜닝시 DB 의 정보를 한 눈에 파악할 수 있겠군요...~~
좋은 정보네요...감사합니다...~~^^
-
맹꽁이
2010.09.07 09:57
감사합니다 - 참고할게요 ^^
-
조니
2010.11.29 11:41
자료 감사합니다.~~~ -
디카초올~
2011.12.08 12:08
감사합니다. -
jiangtairi
2013.07.17 16:24
좋은 자료 감사합니다.
-
이수현
2013.08.01 10:05
감사합니다...
번호 | 제목 | 글쓴이 | 날짜 | 조회 수 |
---|---|---|---|---|
264 |
솔루션 매니저 자료
[11] ![]() | 소론텔 | 2009.08.04 | 3354 |
263 |
ABAP 네임드별 편리한 Objects
[11] ![]() | G.A.S | 2008.08.12 | 3353 |
262 |
ABAP4 교재(SAPShow)
[4] ![]() | 한상준 | 2007.04.25 | 3353 |
261 |
SAP ONLINE HELP 5/5
[2] ![]() | 초밥퍼 | 2007.04.11 | 3353 |
260 |
<img src=d.gif>타 시스템에서 CTS 후, SID 변경 시키는 방법 입니다.[추천:e-abap]
[1] ![]() | 초밥퍼 | 2007.05.31 | 3352 |
259 |
쿼리 관련 자료
[4] ![]() | nickname | 2008.11.28 | 3351 |
258 |
abap dictionary 정리자료입니다
[17] ![]() | xcomedy | 2008.05.08 | 3350 |
257 |
ABAP 문법 엑셀 정리한 파일
[24] ![]() | 오대독자 | 2009.02.11 | 3349 |
256 |
SAP Tree and Tree Model
[9] ![]() | magenta | 2008.01.02 | 3348 |
255 |
세번째 아밥 온라인 교재
[16] ![]() | Lora | 2007.09.14 | 3348 |
254 |
아밥 요약집입니다. 챕터별..
![]() | 풍운사랑 | 2007.09.07 | 3347 |
253 |
dynpro abap manual
[5] ![]() | michael | 2007.04.10 | 3347 |
252 |
BSP 소개자료입니다,.
![]() | 신종철 | 2007.08.02 | 3343 |
251 |
01[APO].Overview
[2] ![]() | sonata | 2009.09.22 | 3341 |
250 |
ABAP 교육교재-2
[31] ![]() | SNIFF | 2009.01.09 | 3340 |
249 |
SELECT구문_최적화
[14] ![]() | xcomedy | 2008.07.19 | 3340 |
248 |
BAPI Programmin
[2] ![]() | COMAN | 2007.07.16 | 3338 |
247 |
ABAP 교육자료 5
[24] ![]() | 서창리 | 2009.06.29 | 3335 |
246 |
parallel processing
[5] ![]() | sapjoy | 2007.05.29 | 3335 |
245 |
abap 교육자료입니다.(1)
[18] ![]() | graceKim | 2009.04.29 | 3334 |