nested loops MSEG MKPF

[nicola_blasi]

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

[fidel_vales · Accepted solution]

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

[nicola_blasi · Accepted solution]

Hy Fidel

thanks for you explanation about my query....

this is my actual access plan:

Execution Plan

SELECT STATEMENT ( Estimated Costs = 53.336 , Estimated #Rows = 2.639 )

5 FILTER

5 NESTED LOOPS

5 TABLE ACCESS BY INDEX ROWID MSEG

INDEX RANGE SCAN MSEG~M

5 TABLE ACCESS BY INDEX ROWID MKPF

INDEX UNIQUE SCAN MKPF~0

I tried to following your analysys changing my index , anyway i've seen that another index (unique index) exists with that fields:

UNIQUE Index MSEG~0

Column Name #Distinct

MANDT 1

MBLNR 7.521.784

MJAHR 8

ZEILE 904

Last statistics date 03.06.2008

Analyze Method Sample 524.250 Rows

Levels of B-Tree 3

Number of leaf blocks 703.738

Number of distinct keys 65.228.897

Average leaf blocks per key 1

Average data blocks per key 1

Clustering factor 15.831.243

what do you suggest? could be possible add XAUTO and MATNR to do better performance?

thanks a lot

Nick

[Former Member · Accepted solution]

Hi,

Please post your query in ABAP forum so you get better answers.

/community [original link is broken]

regards,

kaushal

[fidel_vales · Accepted solution]

Hello kaushal,

> Please post your query in ABAP forum so you get better answers.

I'm the first one thinking that a lot of posts do not belong to this Forum, but in this case I do not (fully) agree. I have not seen a line of ABAP here, so I do not know how the ABAPers could help.

It is true that the first point to tune is the source code, but in this case a better index would help a lot (and then it could be investigate if the source code can/should be changed or it is the users input the one with "issues")

Going to the explain plan we see something "good"

 SELECT STATEMENT ( Estimated Costs = 53.336 , Estimated #Rows = 2.639 )               
       FILTER                                                                                
NESTED LOOPS                                                               
               TABLE ACCESS BY INDEX ROWID MSEG                                       
                    INDEX RANGE SCAN MSEG~M                                            
               TABLE ACCESS BY INDEX ROWID MKPF                                       
                    *INDEX UNIQUE SCAN MKPF~0*

The access to the second table (MKPF) is done via Index Unique Scan. Only 0 or one rows are retuned from this part. Then the first access is the one to be tuned.

The unique index should not be touched (if you change it, adding more fields, you can have duplicate keys for the current keys (MANDT, MBLNR, MJAHR, ZEILE ).

So no touch it.

My previous recommendation still is valid.

An index on MANDT, XAUTO, MATNR is better for the query. and adding at the end MBLNR, MJAHR, ZEILE avoid the access to the table. The access path should be then (more or less):

 SELECT STATEMENT ( Estimated Costs = xxxxx , Estimated #Rows = xxxx )               
           NESTED LOOPS                                                                                
INDEX RANGE SCAN MSEG~ZZZ
               TABLE ACCESS BY INDEX ROWID MKPF                                       
                    *INDEX UNIQUE SCAN MKPF~0*

[nicola_blasi · Accepted solution]

Fidel

only to be sure....do you suggest to put the fileds in the order you describe?

MANDT, XAUTO, MATNR,MBLNR, MJAHR, ZEILE ?

i ask it because, looking the query, i thought that MATNR and XAUTO was at the end before zeile:

MANDT,MBLNR, MJAHR,MATNR,XAUTO,ZEILE...

what do you think? is it the same thing?

thanks

Nick

[fidel_vales · Accepted solution]

Hello Nick,

It is not the same. Too long to explain here, but in resume (and generalizing):

• the order on the SQL is not important

• the order on the index IS important.

As I mentioned before, if your second field in the index is MBLNR, then Oracle will not be able to use it as a filter condition as its value is unknown. In the query it is only specified as part of the join of both tables:

> T_01 . "MBLNR" = T_00 . "MBLNR" AND

As it cannot be used and the first field (MANDT) is unselective, then Oracle will dismiss the index

[nicola_blasi · Accepted solution]

Hy Fidel

I tried to create the new index Z07 . Now the select take the correct index. Anyway the estimated cost is not so good as i expected. Anyway i'll see if the query will have a better performance.

the new cost of the query:

Execution Plan

SELECT STATEMENT ( Estimated Costs = 53.118 , Estimated #Rows = 2.639 )

5 FILTER

5 NESTED LOOPS

INDEX FAST FULL SCAN MSEG~Z07

5 TABLE ACCESS BY INDEX ROWID MKPF

INDEX UNIQUE SCAN MKPF~0

the index MSEG-Z07 :

MANDT

XAUTO

MATNR

MBLNR

MJAHR

ZEILE

Thanks a lot for your help ...i'll add all points possible:-)

Nick

[fidel_vales · Accepted solution]

Hi,

It looks better, nevertheless it is taking a "FAST FULL SCAN" instead of a "RANGE SCAN"

Probably because XAUTO only has two different values and MATNR is specified with a range.

You can do a test with the mentioned index and then modify the statistics, increasing the number of different values for the field XAUTO and see if the range scan is take. This is better only if the value you are choosing for XAUTO is selective

[nicola_blasi · Accepted solution]

Hy Fidel

now this select is finished in 2H 30M (against the 23H 30m of previuos time).

The problem was only in MSEG so for now i think that the problem could be closed.

i'm not the owner of the select anyway i'll suggest your idea about the field XAUTO .

Thanks a lot

Nick

[Former Member]

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.