Solved: Re: Performance issue - MB51 transaction

former_member184072 · ‎04-05-2012

Hi Gurus,

We are running into serious performance issues with the MB51 transaction in our production system.

We found SAP note 1550000, which in turn refers to 1598760 and 1516684. The changes mentioned are extreme impact changes involving adjusting MSEG, creating new indexes and the amount of testing that will be required is huge. We are not yet ready to implement these changes.

The issue will also be taken care of in the further support pack. However, we need a solution for the performance issue in our production system.

I ran a trace on the transaction and the following statement takes a lot of time:

   select (g_t_fields)
    into corresponding fields of table itab
    from mkpf inner join mseg
    on    mkpf~mandt = mseg~mandt
      and mkpf~mblnr = mseg~mblnr
      and mkpf~mjahr = mseg~mjahr
       WHERE MKPF~BUDAT in BUDAT
         and MSEG~BWART in BWART
         and MSEG~CHARG in CHARG
         and MSEG~KUNNR in KUNNR
         and MSEG~LGORT in LGORT
         and MSEG~LIFNR in LIFNR
         and MSEG~MATNR in MATNR
         and MSEG~OID_EXTBOL in OID_EXTB
         and MSEG~OID_MISCDL in OID_MISC
         and MSEG~SOBKZ in SOBKZ
         and MKPF~USNAM in USNAM
         and MKPF~VGART in VGART
         and MSEG~WERKS in WERKS
         and MKPF~XBLNR in XBLNR

SELECT STATEMENT ( Estimated Costs = 6,618 , Estimated #Rows = 20 )

6 NESTED LOOPS

           4 NESTED LOOPS
             ( Estim. Costs = 6,618 , Estim. #Rows = 20 )
             Estim. CPU-Costs = 1,124,316,557 Estim. IO-Costs = 6,514

               2 TABLE ACCESS BY INDEX ROWID MSEG
                 ( Estim. Costs = 6,610 , Estim. #Rows = 19 )
                 Estim. CPU-Costs = 1,124,253,084 Estim. IO-Costs = 6,507

                   1 INDEX SKIP SCAN MSEG~M
                     ( Estim. Costs = 6,607 , Estim. #Rows = 19 )
                     Search Columns: 3
                     Estim. CPU-Costs = 1,124,206,069 Estim. IO-Costs = 6,503
                     Access Predicates Filter Predicates

               3 INDEX UNIQUE SCAN MKPF~0
                 Search Columns: 3
                 Estim. CPU-Costs = 1,804 Estim. IO-Costs = 0
                 Access Predicates

           5 TABLE ACCESS BY INDEX ROWID MKPF
             Estim. CPU-Costs = 3,341 Estim. IO-Costs = 0
             Filter Predicates

The index skip scan shows the following:

Last statistics date 03/13/2011 07:57

Analyze Method Sample 269,474 Rows

Branch levels of B-Tree 3

Number of leaf blocks 206,838

Number of rows 26,947,400

Number of distinct keys 1,326,447

Average leaf blocks per key 1

Average data blocks per key 17

Clustering factor 22,708,100

Partitioned NO

LAST DDL Date 04/26/2008 20:08

The index unique scan shows the following:

Last statistics date 03/13/2011 06:45

Analyze Method Sample 217,412 Rows

Branch levels of B-Tree 2

Number of leaf blocks 57,752

Number of rows 10,930,122

Number of distinct keys 10,930,122

Average leaf blocks per key 1

Average data blocks per key 1

Clustering factor 4,837,946

Partitioned NO

LAST DDL Date 10/21/2006 20:06

Index information on MKPF:

UNIQUE Index MKPF~0

Column Name #Distinct

MANDT                                            1
MBLNR                                    1,268,956
MJAHR                                           16

NONUNIQUE Index MKPF~BUD

Column Name #Distinct

MANDT                                            1
BUDAT                                        5,141
MBLNR                                    1,268,956

NONUNIQUE Index MKPF~ZBD

Column Name #Distinct

MANDT 1
AWSYS 1

Index information on MSEG:

UNIQUE Index MSEG~0

Column Name #Distinct

MANDT                                            1
MBLNR                                      997,266
MJAHR                                           16
ZEILE                                          330

NONUNIQUE Index MSEG~M

Column Name #Distinct

MANDT                                            1
MATNR                                       32,513
WERKS                                        1,215
LGORT                                        1,132
BWART                                           96
SOBKZ                                            6

NONUNIQUE Index MSEG~R

Column Name #Distinct

MANDT 1
RSNUM 84,624

NONUNIQUE Index MSEG~S

Column Name #Distinct

SMBLN                                        2,586
SJAHR                                           16
SMBLP                                           65

NONUNIQUE Index MSEG~ZA

Column Name #Distinct

MANDT 1
OID_EXTBOL 4,476

NONUNIQUE Index MSEG~ZC

Column Name #Distinct

MJAHR 16
AUFNR 140,508

Will updating the table statistics help improve the performance?

Please suggest a solution to improve the performace.

Regards,

Anusha

former_member192616 · ‎04-05-2012

Hi Anusha,

thanks, this is how questions should look like. The only thing missing is the SQL statement as it was sent to the DB (to understand which columns are skipped.

You found already the relevant notes with a suggestion for a solution. And yes the change is not an easy one.

Since you are running on ORACLE you can check this note as well: 12938072 . The change impact is not as heavy but only for ORACLE the goal is to implement a join on indexes only and delaying the table access to the end when the final result set is known.

Kind regards,

Hermann

former_member192616 · ‎04-05-2012

Hi Anusha,

thanks, this is how questions should look like. The only thing missing is the SQL statement as it was sent to the DB (to understand which columns are skipped.

You found already the relevant notes with a suggestion for a solution. And yes the change is not an easy one.

Since you are running on ORACLE you can check this note as well: 12938072 . The change impact is not as heavy but only for ORACLE the goal is to implement a join on indexes only and delaying the table access to the end when the final result set is known.

Kind regards,

Hermann

yuri_ziryukin · ‎04-05-2012

And of course the correction of the note number

1293807 instead of 12938072.

former_member192616 · ‎04-05-2012

thanks 🙂

yuri_ziryukin · ‎04-05-2012

Hello Anusha,

the reply from Hermann is relevant. You'll need to invest some time to properly design your indexes. And this will be also relatively important change.

But... I would like you to take the note 1550000 seriously and indeed go for this solution. One of SAP's largest customers implemented this change recently and now they are quite happy with the performance of MB51. I strongly encourage you to go for it.

Yuri

former_member192616 · ‎04-05-2012

Hi Yuri,

i fully agree. 1550000 should be the long term solution. 1293807 can be an intermediate solution... and still has some impact (index design, code change)

Kind regards,

Hermann

former_member184072 · ‎04-09-2012

Hi Hermann and Yuri,

Thanks for your inputs.

The SQL statement as it is sent to the DB:

SELECT
T_01 . "ANLN1" , T_01 . "ANLN2" , T_01 . "APLZL" , T_01 . "AUFNR" , T_01 . "AUFPL" ,
T_00 . "BKTXT" , T_00 . "BLDAT" , T_01 . "BPMNG" , T_01 . "BPRME" , T_01 . "BSTME" ,
T_01 . "BSTMG" , T_00 . "BUDAT" , T_01 . "BUKRS" , T_01 . "BWART" , T_01 . "BWTAR" ,
T_01 . "CHARG" , T_00 . "CPUDT" , T_00 . "CPUTM" , T_01 . "DMBTR" , T_01 . "EBELN" ,
T_01 . "EBELP" , T_01 . "ERFME" , T_01 . "ERFMG" , T_01 . "EXBWR" , T_01 . "EXVKW" ,
T_01 . "GRUND" , T_01 . "KDAUF" , T_01 . "KDEIN" , T_01 . "KDPOS" , T_01 . "KOSTL" ,
T_01 . "KUNNR" , T_01 . "KZBEW" , T_01 . "KZVBR" , T_01 . "KZZUG" , T_01 . "LGORT" ,
T_01 . "LIFNR" , T_01 . "MATNR" , T_00 . "MBLNR" , T_01 . "MEINS" , T_01 . "MENGE" ,
T_00 . "MJAHR" , T_01 . "NPLNR" , T_01 . "OID_EXTBOL" , T_01 . "OID_MISCDL" ,
T_01 . "PS_PSP_PNR" , T_01 . "RSNUM" , T_01 . "RSPOS" , T_01 . "SHKZG" , T_01 . "SOBKZ" ,
T_00 . "USNAM" , T_00 . "VGART" , T_01 . "VKWRT" , T_01 . "WAERS" , T_01 . "WERKS" ,
T_00 . "XABLN" , T_01 . "XAUTO" , T_00 . "XBLNR" , T_01 . "ZEILE"
FROM
"MKPF" T_00 INNER JOIN "MSEG" T_01 ON T_01 . "MANDT" = '010' AND T_00 . "MANDT" = T_01 . "MANDT" A
ND T_00 . "MBLNR" = T_01 . "MBLNR" AND T_00 . "MJAHR" = T_01 . "MJAHR"
WHERE
T_00 . "MANDT" = '010' AND T_00 . "BUDAT" BETWEEN '20110131' AND '20110204' AND T_01 . "LGORT" =
'022' AND T_01 . "WERKS" = 'UA01'

I checked the note 1293807 and I'll be creating the required indexes to check delayed table access.

I'll let you know how it goes.

Please let me know if you would like me to try something else as well to resolve the performance issue.

Thank you.

Regards,

Anusha

former_member192616 · ‎04-10-2012

Hi Anusha,

it's not only the indexes .... you have to rewrite the SQL statement as described in the note.

Kind regards,

Hermann

former_member184072 · ‎05-18-2012

Hi Hermann,

I created the indexes (called Z51 on MSEG and MKPF) and a test program with the modified query and moved those to our quality server to check the performance.

The performance of the original program (MB51) improved.

I was expecting the test program to perform better than the original one.

However, the performance of the test program is a bit slow than the original one.

The execution plan also varies.

Execution plan for MB51 (original program without modified query):

SELECT STATEMENT ( Estimated Costs = 11 , Estimated #Rows = 20 )

6 NESTED LOOPS

            4 NESTED LOOPS
              ( Estim. Costs = 11 , Estim. #Rows = 20 )
              Estim. CPU-Costs = 108,480 Estim. IO-Costs = 11

                2 TABLE ACCESS BY INDEX ROWID MSEG
                  ( Estim. Costs = 3 , Estim. #Rows = 19 )
                  Estim. CPU-Costs = 45,007 Estim. IO-Costs = 3

                    1 INDEX RANGE SCAN MSEG~Z51
                      ( Estim. Costs = 1 , Estim. #Rows = 19 )
                      Search Columns: 3
                      Estim. CPU-Costs = 6,537 Estim. IO-Costs = 1
                      Access Predicates

                3 INDEX UNIQUE SCAN MKPF~0
                  Search Columns: 3
                  Estim. CPU-Costs = 1,804 Estim. IO-Costs = 0
                  Access Predicates

            5 TABLE ACCESS BY INDEX ROWID MKPF
              Estim. CPU-Costs = 3,341 Estim. IO-Costs = 0
              Filter Predicates

Execution plan for test program (with modified query):

SELECT STATEMENT ( Estimated Costs = 28 , Estimated #Rows = 20 )

11 FILTER
Filter Predicates

10 NESTED LOOPS

               8 NESTED LOOPS
                 ( Estim. Costs = 28 , Estim. #Rows = 20 )
                 Estim. CPU-Costs = 241,318 Estim. IO-Costs = 28

                   6 NESTED LOOPS
                     ( Estim. Costs = 20 , Estim. #Rows = 20 )
                     Estim. CPU-Costs = 174,107 Estim. IO-Costs = 20

                       4 NESTED LOOPS
                         ( Estim. Costs = 12 , Estim. #Rows = 19 )
                         Estim. CPU-Costs = 115,424 Estim. IO-Costs = 12

                           1 INDEX RANGE SCAN MSEG~Z51
                             ( Estim. Costs = 1 , Estim. #Rows = 19 )
                             Search Columns: 3
                             Estim. CPU-Costs = 7,107 Estim. IO-Costs = 1
                             Access Predicates Filter Predicates
                           3 TABLE ACCESS BY INDEX ROWID MSEG
                             ( Estim. Costs = 1 , Estim. #Rows = 1 )
                             Estim. CPU-Costs = 5,701 Estim. IO-Costs = 1

                               2 INDEX UNIQUE SCAN MSEG~0
                                 Search Columns: 4
                                 Estim. CPU-Costs = 3,259 Estim. IO-Costs = 0
                                 Access Predicates Filter Predicates

                       5 INDEX RANGE SCAN MKPF~Z51
                         Search Columns: 4
                         Estim. CPU-Costs = 3,089 Estim. IO-Costs = 0
                         Access Predicates Filter Predicates

                   7 INDEX UNIQUE SCAN MKPF~0
                     Search Columns: 3
                     Estim. CPU-Costs = 1,834 Estim. IO-Costs = 0
                     Access Predicates Filter Predicates

9 TABLE ACCESS BY INDEX ROWID MKPF
Estim. CPU-Costs = 3,361 Estim. IO-Costs = 0

The test program is:

REPORT ZTEST_MB51.
tables: mseg, mkpf.

TYPES: BEGIN OF stype_fields,                               "n599966
         fieldname           type aind_str3-fieldname,     "n599966
       END OF   stype_fields.
data : g_t_fields            type standard table of        "n921164
                             stype_fields.
data: begin of itab occurs 0.
data: ANLN1 type MSEG-ANLN1 .
data: ANLN2 type MSEG-ANLN2 .
data: APLZL type MSEG-APLZL .
data: AUFNR type MSEG-AUFNR .
data: AUFPL type MSEG-AUFPL .
data: BKTXT type MKPF-BKTXT .
data: BLDAT type MKPF-BLDAT .
data: BPMNG type MSEG-BPMNG .
data: BPRME type MSEG-BPRME .
data: BSTME type MSEG-BSTME .
data: BSTMG type MSEG-BSTMG .
data: BUDAT type MKPF-BUDAT .
data: BUKRS type MSEG-BUKRS .
data: BWART type MSEG-BWART .
data: BWTAR type MSEG-BWTAR .
data: CHARG type MSEG-CHARG .
data: CPUDT type MKPF-CPUDT .
data: CPUTM type MKPF-CPUTM .
data: DMBTR type MSEG-DMBTR .
data: EBELN type MSEG-EBELN .
data: EBELP type MSEG-EBELP .
data: ERFME type MSEG-ERFME .
data: ERFMG type MSEG-ERFMG .
data: EXBWR type MSEG-EXBWR .
data: EXVKW type MSEG-EXVKW .
data: GRUND type MSEG-GRUND .
data: KDAUF type MSEG-KDAUF .
data: KDEIN type MSEG-KDEIN .
data: KDPOS type MSEG-KDPOS .
data: KOSTL type MSEG-KOSTL .
data: KUNNR type MSEG-KUNNR .
data: KZBEW type MSEG-KZBEW .
data: KZVBR type MSEG-KZVBR .
data: KZZUG type MSEG-KZZUG .
data: LGORT type MSEG-LGORT .
data: LIFNR type MSEG-LIFNR .
data: MATNR type MSEG-MATNR .
data: MBLNR type MKPF-MBLNR .
data: MEINS type MSEG-MEINS .
data: MENGE type MSEG-MENGE .
data: MJAHR type MKPF-MJAHR .
data: NPLNR type MSEG-NPLNR .
data: OID_EXTBOL type MSEG-OID_EXTBOL .
data: OID_MISCDL type MSEG-OID_MISCDL .
data: PS_PSP_PNR type MSEG-PS_PSP_PNR .
data: RSNUM type MSEG-RSNUM .
data: RSPOS type MSEG-RSPOS .
data: SHKZG type MSEG-SHKZG .
data: SOBKZ type MSEG-SOBKZ .
data: USNAM type MKPF-USNAM .
data: VGART type MKPF-VGART .
data: VKWRT type MSEG-VKWRT .
data: WAERS type MSEG-WAERS .
data: WERKS type MSEG-WERKS .
data: XABLN type MKPF-XABLN .
data: XAUTO type MSEG-XAUTO .
data: XBLNR type MKPF-XBLNR .
data: ZEILE type MSEG-ZEILE .
data: end of itab.

* Selection screen
selection-screen begin of block mseg with frame title text-001.
select-options MATNR for MSEG-MATNR memory id MAT
MATCHCODE OBJECT MAT1.
select-options WERKS for MSEG-WERKS memory id WRK.
select-options LGORT for MSEG-LGORT memory id LAG.
select-options CHARG for MSEG-CHARG memory id CHA.
select-options LIFNR for MSEG-LIFNR memory id LIF.
select-options KUNNR for MSEG-KUNNR memory id KUN.
select-options BWART for MSEG-BWART memory id BWA.
select-options SOBKZ for MSEG-SOBKZ.
select-options OID_EXTB for MSEG-OID_EXTBOL.
select-options OID_MISC for MSEG-OID_MISCDL.
selection-screen end of block mseg.

selection-screen begin of block mkpf with frame title text-002.
select-options BUDAT for MKPF-BUDAT.
select-options USNAM for MKPF-USNAM memory id USR.
select-options VGART for MKPF-VGART.
select-options XBLNR for MKPF-XBLNR.
selection-screen end of block mkpf.

write: sy-uzeit.

perform fields_for_selection.

select
(g_t_fields)
into corresponding fields of table itab
from
( ( (
mkpf as ki inner join
mseg as si on
    ki~mandt = si~mandt and
    ki~mblnr = si~mblnr and
    ki~mjahr = si~mjahr ) inner join
mkpf as kt on
    ki~mandt = kt~mandt and
    ki~mblnr = kt~mblnr and
    ki~mjahr = kt~mjahr ) inner join
mseg as st on
    si~mandt = st~mandt and
    si~mblnr = st~mblnr and
    si~mjahr = st~mjahr and
    si~zeile = st~zeile )
where
ki~budat in BUDAT
and si~bwart in bwart
and si~CHARG in CHARG
         and si~KUNNR in KUNNR
         and si~LGORT in LGORT
         and si~LIFNR in LIFNR
         and si~MATNR in MATNR
         and si~OID_EXTBOL in OID_EXTB
         and si~OID_MISCDL in OID_MISC
         and si~SOBKZ in SOBKZ
         and ki~USNAM in USNAM
         and ki~VGART in VGART
         and si~WERKS in WERKS
         and ki~XBLNR in XBLNR.

write:/'After select, execution time:', sy-uzeit.

loop at itab.
write: / itab-aufnr.
endloop.

form fields_for_selection. "#EC CALLED

* create table with the fields to be transported
append 'ST~ANLN1' to g_t_fields.
append 'ST~ANLN2' to g_t_fields.
append 'ST~APLZL' to g_t_fields.
append 'ST~AUFNR' to g_t_fields.
append 'ST~AUFPL' to g_t_fields.
append 'KT~BKTXT' to g_t_fields.
append 'KT~BLDAT' to g_t_fields.
append 'ST~BPMNG' to g_t_fields.
append 'ST~BPRME' to g_t_fields.
append 'ST~BSTME' to g_t_fields.
append 'ST~BSTMG' to g_t_fields.
append 'KT~BUDAT' to g_t_fields.
append 'ST~BUKRS' to g_t_fields.
append 'ST~BWART' to g_t_fields.
append 'ST~BWTAR' to g_t_fields.
append 'ST~CHARG' to g_t_fields.
append 'KT~CPUDT' to g_t_fields.
append 'KT~CPUTM' to g_t_fields.
append 'ST~DMBTR' to g_t_fields.
append 'ST~EBELN' to g_t_fields.
append 'ST~EBELP' to g_t_fields.
append 'ST~ERFME' to g_t_fields.
append 'ST~ERFMG' to g_t_fields.
append 'ST~EXBWR' to g_t_fields.
append 'ST~EXVKW' to g_t_fields.
append 'ST~GRUND' to g_t_fields.
append 'ST~KDAUF' to g_t_fields.
append 'ST~KDEIN' to g_t_fields.
append 'ST~KDPOS' to g_t_fields.
append 'ST~KOSTL' to g_t_fields.
append 'ST~KUNNR' to g_t_fields.
append 'ST~KZBEW' to g_t_fields.
append 'ST~KZVBR' to g_t_fields.
append 'ST~KZZUG' to g_t_fields.
append 'ST~LGORT' to g_t_fields.
append 'ST~LIFNR' to g_t_fields.
append 'ST~MATNR' to g_t_fields.
append 'KT~MBLNR' to g_t_fields.
append 'ST~MEINS' to g_t_fields.
append 'ST~MENGE' to g_t_fields.
append 'KT~MJAHR' to g_t_fields.
append 'ST~NPLNR' to g_t_fields.
append 'ST~OID_EXTBOL' to g_t_fields.
append 'ST~OID_MISCDL' to g_t_fields.
append 'ST~PS_PSP_PNR' to g_t_fields.
append 'ST~RSNUM' to g_t_fields.
append 'ST~RSPOS' to g_t_fields.
append 'ST~SHKZG' to g_t_fields.
append 'ST~SOBKZ' to g_t_fields.
append 'KT~USNAM' to g_t_fields.
append 'KT~VGART' to g_t_fields.
append 'ST~VKWRT' to g_t_fields.
append 'ST~WAERS' to g_t_fields.
append 'ST~WERKS' to g_t_fields.
append 'KT~XABLN' to g_t_fields.
append 'ST~XAUTO' to g_t_fields.
append 'KT~XBLNR' to g_t_fields.
append 'ST~ZEILE' to g_t_fields.

ENDFORM. " fields_for_selection

Does the query need to be modified further or am I missing something?

Please advise.

Regards,

Anusha