Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Mb51 ; time-outs, important cpu costs

Farid
Active Participant
0 Kudos

Hello,

Every day, users running transaction MB51 on the production system (ecc6, Oracle 10.2.0.4) are facing time-out issues (1200 sec).

I had a look at the execution plan, there are important CPU costs, since this a sap standard transaction, I wanted some advice ;

The statisctics are up to date both for the tables and the indexes

SELECT STATEMENT ( Estimated Costs = 916 , Estimated #Rows = 0

5 NESTED LOOPS

( Estim. Costs = 915 , Estim. #Rows = 2.383 )

Estim. CPU-Costs = 9.314.135 Estim. IO-Costs = 914

2 TABLE ACCESS BY INDEX ROWID MSEG

( Estim. Costs = 438 , Estim. #Rows = 2.383 )

Estim. CPU-Costs = 5.052.644 Estim. IO-Costs = 438

1 INDEX RANGE SCAN MSEG~M

( Estim. Costs = 5 , Estim. #Rows = 2.383 )

Search Columns: 5

Estim. CPU-Costs = 132.111 Estim. IO-Costs = 5

Access Predicates

4 TABLE ACCESS BY INDEX ROWID MKPF

Estim. CPU-Costs = 1.788 Estim. IO-Costs = 0

Table MSEG

Last statistics date 05.03.2012

Analyze Method Sample 395.660 Rows

Number of rows 39.566.000

Number of blocks allocated 4.205.760

Number of empty blocks 0

Average space 0

Chain count 0

Average row length 680

Partitioned NO

UNIQUE Index MSEG~0

Column Name #Distinct

MANDT 1

MBLNR 483.630

MJAHR 3

ZEILE 476

Last statistics date 05.03.2012

Analyze Method Sample 386.574 Rows

Levels of B-Tree 3

Number of leaf blocks 195.500

Number of distinct keys 38.657.400

Average leaf blocks per key 1

Average data blocks per key 1

Clustering factor 5.744.200

3 INDEX UNIQUE SCAN MKPF~0

Search Columns: 3

Estim. CPU-Costs = 210 Estim. IO-Costs = 0

Access Predicates

NONUNIQUE Index MSEG~M

Column Name #Distinct

MANDT 1

MATNR 16.601

WERKS 29

LGORT 38

BWART 52

SOBKZ 5

Last statistics date 05.03.2012

Analyze Method Sample 394.562 Rows

Levels of B-Tree 3

Number of leaf blocks 379.900

Number of distinct keys 16.601

Average leaf blocks per key 70

Average data blocks per key 6.681

Clustering factor 35.901.000

NONUNIQUE Index MSEG~R

Column Name #Distinct

MANDT 1

RSNUM 192.208

Last statistics date 05.03.2012

Analyze Method Sample 369.922 Rows

Levels of B-Tree 3

Number of leaf blocks 139.100

Number of distinct keys 404.600

Average leaf blocks per key 1

Average data blocks per key 15

Clustering factor 6.151.600

NONUNIQUE Index MSEG~S

Column Name #Distinct

SMBLN 528

SJAHR 4

SMBLP 150

Last statistics date 05.03.2012

Analyze Method Sample 396.865 Rows

Levels of B-Tree 3

Number of leaf blocks 182.300

Number of distinct keys 582

Average leaf blocks per key 313

Average data blocks per key 7.110

Clustering factor 4.138.300

1 ACCEPTED SOLUTION

Former Member
0 Kudos

What notes have you looked at??

Rob

4 REPLIES 4

Farid
Active Participant
0 Kudos

Table MKPF

Last statistics date 05.03.2012

Analyze Method Sample 73.955 Rows

Number of rows 2.465.167

Number of blocks allocated 70.524

Number of empty blocks 0

Average space 0

Chain count 0

Average row length 142

Partitioned NO

NONUNIQUE Index MKPF~BUD

Column Name #Distinct

MANDT 1

BUDAT 539

MBLNR 2.465.167

Last statistics date 05.03.2012

Analyze Method Sample 219.184 Rows

Levels of B-Tree 2

Number of leaf blocks 12.750

Number of distinct keys 2.499.699

Average leaf blocks per key 1

Average data blocks per key 1

Clustering factor 1.306.222

NONUNIQUE Index MKPF~Z01

Column Name #Distinct

MANDT 1

XBLNR 43.025

Last statistics date 05.03.2012

Analyze Method Sample 169.933 Rows

Levels of B-Tree 2

Number of leaf blocks 15.780

Number of distinct keys 462.223

Average leaf blocks per key 1

Average data blocks per key 1

Clustering factor 587.177

UNIQUE Index MKPF~0

Column Name #Distinct

MANDT 1

MBLNR 2.465.167

MJAHR 3

Last statistics date 05.03.2012

Analyze Method Sample 225.313 Rows

Levels of B-Tree 2

Number of leaf blocks 10.044

Number of distinct keys 2.274.411

Average leaf blocks per key 1

Average data blocks per key 1

Clustering factor 1.149.849

*And here is the standard abap coding responsible

*SAP include 
*RM07DOCS_GENERATED
*FORM DATA_SELECTION .     "#EC CALLED

* Data selection routine for standard access

* process SELECT command depending on the
* required material selection
  if  g_cnt_matnr_total  > 0                 and
      g_cnt_matnr_total  = g_cnt_matnr_i_eq.
*   work with .. for all entries ...
    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
    for all entries in matnr
    where matnr = matnr-low
      and MKPF~BUDAT in BUDAT
      and MSEG~BWART in BWART
      and MSEG~CHARG in CHARG
      and MSEG~KUNNR in KUNNR
      and MSEG~LFBNR in LFBNR
      and MSEG~LGORT in LGORT
      and MSEG~LIFNR in LIFNR
      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
%_HINTS
ORACLE '&SUBSTITUTE VALUES&'
.
  else.
*   wor

Former Member
0 Kudos

What notes have you looked at??

Rob

yuri_ziryukin
Employee
Employee
0 Kudos

Hello Raoul,

here we are:

1516684: MKPF fields added to MSEG - Performance optimization

1550000: MB51: Redesign of selection for performance optimization

1558298: MB5B: Redesign of selection to optimize performance

1567602: DB dependent steps to support the redesign of MB51

1598760: FAQ: MSEG Enhancement & Redesign MB51/MB5B

Regards,

Yuri

Former Member
0 Kudos

Hi ,

Is the user running the report in foreground ?

If yes then i will give time out after the threshold limt.

Ask the user to run the report in background....it will not give teh time out.....

Best Regards

Edited by: Akhilesh01 on Mar 7, 2012 9:25 AM