一总体分析
事务ZSMSALES为销售收入(开票)查询报表。我们对事务进行了跟踪分析(输入条件:销售组织S017、开票日期2008年7月26日、地点1154),结果如下:
| CPU | DB | System | Generation | TOTAL |
Time(ms | 862,851 | 77,456,232 | 78,919 | 0 | 62,578,049 |
Perc. | 1 | 99 | 0 | 0 | 100 |
二 原因分析
根据分析我们看到DB运行时间占总运行时间的99,其中,Fetch VBRK占总运行时间的98.8%
在SQL分析中,我们找到了如下典型的SQL:
SELECT
T_00 . "FKDAT" , T_00 . "VBELN" , T_00 . "FKART" , T_00 . "VKORG" , T_00 . "VTWEG" ,
T_00 . "KUNAG" , T_00 . "KUNRG" , T_00 . "WAERK" , T_01 . "VBTYP" , T_01 . "FKART" ,
T_02 . "POSNR" , T_02 . "WERKS" , T_02 . "LGORT" , T_02 . "MATNR" , T_02 . "ARKTX" ,
T_02 . "FKIMG" , T_02 . "VRKME" , T_02 . "KZWI5" , T_02 . "MATKL" , T_02 . "AKTNR" ,
T_02 . "KNUMA_PI" , T_02 . "WAVWR" , T_02 . "NETWR" , T_02 . "MWSBP" , T_02 . "VBELN" ,
T_03 . "EKGRP" , T_03 . "BWSCL" , T_03 . "MMSTA" , T_03 . "CONS_PROCG" , T_03 . "MATNR" ,
T_03 . "WERKS" , T_04 . "BRAND_ID" , T_04 . "FREE_CHAR" , T_04 . "MATNR" , T_05 . "OPER" ,
T_05 . "MATNR" , T_05 . "WERKS"
FROM
"VBRK" T_00 INNER JOIN "TVFK" T_01 ON T_01 . "MANDT" = :A0 AND T_01 . "FKART" = T_00 . "FKART" INN
ER JOIN "VBRP" T_02 ON T_02 . "MANDT" = :A1 AND T_02 . "VBELN" = T_00 . "VBELN" INNER JOIN "MARC"
T_03 ON T_03 . "MANDT" = :A2 AND T_03 . "MATNR" = T_02 . "MATNR" AND T_03 . "WERKS" = T_02 . "WERK
S" INNER JOIN "MARA" T_04 ON T_04 . "MANDT" = :A3 AND T_04 . "MATNR" = T_03 . "MATNR" INNER JOIN "
ZMARC" T_05 ON T_05 . "MANDT" = :A4 AND T_05 . "MATNR" = T_03 . "MATNR" AND T_05 . "WERKS" = T_03
. "WERKS"
WHERE
T_00 . "MANDT" = :A5 AND T_00 . "VKORG" = :A6 AND T_00 . "FKDAT" = :A7 AND T_02 . "WERKS" = :A8
分析结果如下:
根据以下分析发现,主表VBRK用的是全表扫描,可以在这方面进行优化
三 建议
3.1 VBRK表建立索引:MANDT,FKDAT
3.2 如果加上索引后,不能达到预期效果,建议重新写程序,建立report,代替此query。