Skip to Content
author's profile photo Nicola Blasi
0
Nicola Blasi

nested loops MSEG MKPF

Posted on Jun 03, 2008 at 01:10 PM 38 Views

Good afternoon

from a BW system , a user try to extract data in a R3 46C system .

this loading hangs during the access of table mseg for many hours.

the select in hang is the following:

SELECT

T_00 . "MBLNR" , T_00 . "MJAHR" , T_00 . "ZEILE"

FROM

"MSEG" T_00 , "MKPF" T_01

WHERE

( T_01 . "MANDT" = :A0 AND T_01 . "MBLNR" = T_00 . "MBLNR" AND T_01 . "MJAHR" = T_00 . "MJAHR" )

AND T_00 . "MANDT" = :A1 AND T_01 . "CPUDT" >= :A2 AND T_00 . "MATNR" BETWEEN :A3 AND :A4 AND

T_00 . "XAUTO" <> :A5#

The index used is the following:

NONUNIQUE Index MSEG~M

MANDT 1

MATNR 11.877

WERKS 142

LGORT 556

BWART 174

SOBKZ 4

Last statistics date 06.05.2008

Analyze Method Sample 612.175 Rows

Levels of B-Tree 3

Number of leaf blocks 674.400

Number of distinct keys 25.071

Average leaf blocks per key 26

Average data blocks per key 1.635

Clustering factor 40.992.900

I've created a new index to have a better performance:

NONUNIQUE Index MSEG~Z07

Column Name #Distinct

MANDT 1

MBLNR 7.521.784

MJAHR 8

MATNR 11.877

XAUTO 2

Last statistics date 03.06.2008

Analyze Method mple 63.678.689 Rows

Levels of B-Tree 3

Number of leaf blocks 449.133

Number of distinct keys 59.182.326

Average leaf blocks per key 1

Average data blocks per key 1

Clustering factor 17.885.740

Anyway the query doesn't take this index.

Any suggestion about this query? from sm51 i have the wp running with MSEG.

SAP 46C

Oracle 10.2.0.2

thanks

Nick

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Best Answer
    author's profile photo Fidel Vales
    Fidel Vales
    Posted on Jun 03, 2008 at 07:09 PM
    2

    Hi,

    I'd need more information (like the current access plan, all statistics, how many rows are returned in total ... ) to be sure.

    But the reason the new index is not taken is because it is not selective.

    Lets take a look at the query (a bit modified for clarity) 

    SELECT
    T_00 . "MBLNR" , T_00 . "MJAHR" , T_00 . "ZEILE"
FROM
    "MSEG" T_00 , "MKPF" T_01
WHERE ( 
   T_01 . "MANDT" = :A0 AND 
   T_01 . "MBLNR" = T_00 . "MBLNR" AND 
   T_01 . "MJAHR" = T_00 . "MJAHR" ) AND 

   T_01 . "CPUDT" >= :A2 AND 

   T_00 . "MANDT" = :A1 AND 
   T_00 . "MATNR" BETWEEN :A3 AND :A4 AND
   T_00 . "XAUTO" :A5

    On the query there are 3 parts after the "WHERE":

    1) Join condition on both queries

    2) Filter condition for table "MKPF"

    3) Filter condition for table "MSEG"

    Now, lets take a look at the point 3 and your new index

    MANDT -> It is specified in the Filter condition

    MBLNR -> it is not specified in the Filter condition

    MJAHR -> it is not specified in the Filter condition

    MATNR -> It is specified in the Filter condition

    XAUTO -> It is specified in the Filter condition

    The index is not used because the second field is not in the conditions, it is only on the join condition, and not considered by the CBO in case of nested loop join.

    A much better index would be

    MANDT

    XAUTO (I think it is specified with an "=", only have two values, is the one specified selective?)

    MATNR (it is specified with BETWEEN, not too good)

    MBLNR

    MJAHR

    ZEILE

    Adding the last 3 field to the index you will avoid the access to the table as all information needed is on the index.

    Perhaps something similar could be done for the MKPF table.

    Edited by: Fidel Vales on Jun 3, 2008 9:09 PM

    Not sure if the XAUTO is specified with an equal, it is missing on the original information

    Add a comment
    10|10000 characters needed characters exceeded

    Comment on This Answer
  • author's profile photo Former Member
    Former Member
    Posted on Jun 03, 2008 at 10:04 PM
    0

    Great explanation Fidel.

    I would like to add that CPUDT is not contain in any standard-index of MKPF so either by adding an index containg that field or using BUDAT (Posting Date) instead (which is contained in an index) can also improve performance.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.