ZABAP Tuning Recommendation

Former Member · ‎07-27-2012

Dear Gurus,

I found a function ZMMWMS09 took too long time to run.

Here below is a part of the source code (located by Shared Cursor Cache Analysis) and its execution plan.

(You also can see the issus here: http://blog.sina.com.cn/s/blog_53b575ce0102e8fl.html )

Do hope anyone could give me some tuning recommendation.

##########################Source Code############################

SELECT A~VBELN

B~POSNR

A~LFDAT

B~WERKS

B~LGORT

A~WERKS

A~KUNNR

B~MATNR

B~LFIMG

* B~MENGE

B~CHARG

B~BWART

B~VGBEL

A~FKDAT

B~VGPOS

INTO (T_ASN-VBELN,

T_ASN-POSNR,

T_ASN-LFDAT,

T_ASN-RESWK,

T_ASN-LGOBE,

T_ASN-WERKS,

T_ASN-KUNNR,

T_ASN-MATNR,

T_ASN-LFIMG,

* T_ASN-MENGE,

T_ASN-CHARG,

T_ASN-BWART,

T_ASN-VGBEL,

T_ASN-FKDAT,

T_ASN-VGPOS

)

FROM LIKP AS A

INNER JOIN LIPS AS B

ON A~VBELN = B~VBELN

INNER JOIN VBUK AS C

ON A~VBELN = C~VBELN

WHERE A~VBELN IN TB_VBELN

AND A~WERKS IN TB_VSTEL

AND A~LFART = 'NL'

AND C~WBSTK = 'C'

AND B~BWART IN ('641')

AND B~LFIMG > 0

* AND A~ERDAT EQ SY-DATUM

.

* AND A~ERDAT BETWEEN '20101101' AND '20120314'.

APPEND T_ASN.

ENDSELECT.

##########################Execution Plan############################

SQL_ID g9j2655dx25y4, child number 0

-------------------------------------

SELECT T_00 . "VBELN" , T_01 . "POSNR" , T_00 . "LFDAT" , T_01 . "WERKS" , T_01 .

"LGORT" , T_00 . "WERKS" , T_00 . "KUNNR" , T_01 . "MATNR" , T_01 . "LFIMG" , T_01

. "CHARG" , T_01 . "BWART" , T_01 . "VGBEL" , T_00 . "FKDAT" , T_01 . "VGPOS" FROM

"LIKP" T_00 INNER JOIN "LIPS" T_01 ON T_01 . "MANDT" = :A0 AND T_00 . "VBELN" =

T_01 . "VBELN" INNER JOIN "VBUK" T_02 ON T_02 . "MANDT" = :A1 AND T_00 . "VBELN" =

T_02 . "VBELN" WHERE T_00 . "MANDT" = :A2 AND T_00 . "WERKS" IN ( :A3 , :A4 , :A5 ,

:A6 ) AND T_00 . "LFART" = :A7 AND T_02 . "WBSTK" = :A8 AND T_01 . "BWART" = :A9

AND T_01 . "LFIMG" > :A10

Plan hash value: 3716362595

--------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 31542 (100)| |

|* 1 | TABLE ACCESS BY INDEX ROWID | LIPS | 1 | 74 | 2 (0)| 00:00:01 |

| 2 | NESTED LOOPS | | 6745 | 935K| 31542 (1)| 00:02:00 |

| 3 | NESTED LOOPS | | 6439 | 427K| 18624 (1)| 00:01:11 |

| 4 | INLIST ITERATOR | | | | | |

|* 5 | TABLE ACCESS BY INDEX ROWID | LIKP | 6439 | 320K| 16040 (1)| 00:01:01 |

|* 6 | INDEX RANGE SCAN | LIKP~Y01 | 141K| | 102 (2)| 00:00:01 |

|* 7 | TABLE ACCESS BY INDEX ROWID | VBUK | 1 | 17 | 1 (0)| 00:00:01 |

|* 8 | INDEX UNIQUE SCAN | VBUK~0 | 1 | | 1 (0)| 00:00:01 |

|* 9 | INDEX RANGE SCAN | LIPS~0 | 21 | | 1 (0)| 00:00:01 |

--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter(("T_01"."LFIMG">:A10 AND "T_01"."BWART"=:A9))

5 - filter(("T_00"."LFART"=:A7 AND "T_00"."MANDT"=:A2))

6 - access(("T_00"."WERKS"=:A3 OR "T_00"."WERKS"=:A4 OR "T_00"."WERKS"=:A5 OR

"T_00"."WERKS"=:A6))

7 - filter("T_02"."WBSTK"=:A8)

8 - access("T_02"."MANDT"=:A1 AND "T_00"."VBELN"="T_02"."VBELN")

9 - access("T_01"."MANDT"=:A0 AND "T_00"."VBELN"="T_01"."VBELN")

Thanks in advance.

Jason

Email: xjpem@126.com

volker_borowski2 · ‎07-27-2012

{code}

SELECT T_00 . "VBELN" ,

T_01 . "POSNR" ,

T_00 . "LFDAT" ,

T_01 . "WERKS" ,

T_01 . "LGORT" ,

T_00 . "WERKS" ,

T_00 . "KUNNR" ,

T_01 . "MATNR" ,

T_01 . "LFIMG" ,

T_01 . "CHARG" ,

T_01 . "BWART" ,

T_01 . "VGBEL" ,

T_00 . "FKDAT" ,

T_01 . "VGPOS"

FROM

"LIKP" T_00

INNER JOIN "LIPS" T_01

ON T_01 . "MANDT" = :A0

AND T_00 . "VBELN" = T_01 . "VBELN"

INNER JOIN "VBUK" T_02

ON T_02 . "MANDT" = :A1

AND T_00 . "VBELN" = T_02 . "VBELN"

WHERE T_00 . "MANDT" = :A2

AND T_00 . "WERKS" IN ( :A3 , :A4 , :A5 ,:A6 )

AND T_00 . "LFART" = :A7

AND T_02 . "WBSTK" = :A8

AND T_01 . "BWART" = :A9

AND T_01 . "LFIMG" > :A10

{code}

Without having information about the column stats,
right now the only thing I would suggest is to add
LFART to index LIKP~Y01, either before WERKS, if the
index is not needed for other statements, or directly
after WERKS, if you have other access plans with WERKS
as the driving column on Y01. If you have additional
fields in Y01, that are required for other access plans,
you coud try to add it at the very end, allthough it
will only serve for filtering then, not for acceess.
If you can not modify Y01, you might need to go for a
seperate index LFART+WERKS.

Depending on stats and datadistribution:

Might be worth to check, in what manner WBSTK in VBUK is
reducing the result. If this is massive, I'd try to use VBUK as
the driving table in the join (may be with an index on MANDT+WBSTK).
You might try that in an sqlplus session first, with "set timing on",
and a hint, just to see how that works out. Would be....
leading(T_02) index(T_02,"VBUK~ZIX") use_nl(T_02,T_00) use_nl(T_02,T01)
This might require to have an index VBELN+WERKS on LIKP.

Same thought might apply to BWART and LFIMG for LIPS, allthough,
I do not think LFIMG will do a lot due to "greater as" condition.

Can you put the statement into ST05, press explain, doubleclick
each table end fully expand index stats for each table.

Thanks
Volker

volker_borowski2 · ‎07-27-2012

Hummm, is there a screenshot somewhere, that shows how to use code tags in this new editor?

I have seen some nice "code" visualisation in the new forum already, allthough I did not find how to utilize it.

V.