Poor performance reading MBEWH table

[guilherme_frisoni]

Hi,

I'm getting serious performance problems when reading MBEWH table directly.

I did the following tests:

  GET RUN TIME FIELD t1.
  SELECT mara~matnr
    FROM mara
    INNER JOIN mbewh ON mbewh~matnr = mara~matnr
    INTO TABLE gt_mbewh
    WHERE mbewh~lfgja = '2009'.
  GET RUN TIME FIELD t2.

  GET RUN TIME FIELD t3.
  SELECT mbewh~matnr
    FROM mbewh
    INTO TABLE gt_mbewh
    WHERE mbewh~lfgja = '2009'.
  GET RUN TIME FIELD t4.

t2 = t2 - t1.
t4 = t4 - t3.
write: 'With join: ', t2.
write /.
write: 'Without join: ', t4.

And as result I got:

With join:      27.166
Without join:  103970.297

All MATNR in MBEWH are in MARA.

MBEWH has 71.745 records and MARA has 705 records.

I created an index for lfgja field in MBEWH.

Why I have better performance using inner join?

In production client, MBEW has 68 million records, so any selection takes too much time.

Thanks in advance,

Frisoni

[Former Member]

Hi Guilherme,

Check what happen if you do this:

  SELECT mbewh~matnr
    FROM mbewh
    INNER JOIN mbewh ON mara~matnr = mbewh~matnr
    INTO TABLE gt_mbewh
    WHERE mbewh~lfgja = '2009'.

Regards, Fernando Da Ró

[guilherme_frisoni]

Hi Fernando,

  GET RUN TIME FIELD t3.
    SELECT m1~matnr
    FROM mbewh as m1
    INNER JOIN mbewh as m2 ON m2~matnr = m1~matnr
    INTO TABLE gt_mbewh
    WHERE m1~lfgja = '2009'.
  GET RUN TIME FIELD t4.

Result:

Without join:  259346.845

Not so good. 😕

Frisoni

[Former Member]

Even worst...

Perhaps, your database is taking some strange decision when accessing the data.

First, using [ST05|https://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/7205] [original link is broken] [original link is broken] [original link is broken]; check the execution plan.

My guess is that your database statistics aren't updated (check it on DB20 for each table), and share your impressions with us.

OPS... I'm sorry Guilherme I made a mistake, I "read" that without join was better than join... Anyway, the execution plan gathered on ST05 will solve your doubt.

Regards, Fernando Da Ru00F3s

Edited by: Fernando Ros on Jul 10, 2009 11:35 PM

[former_member156446]

if you look at the table MATNR BWKEY BWTAR LFGJA LFMON are the primary keys... the more primary keys u can use more performance can be improved... try to work on it...

for more easier test : se30 > tips and tricks button..

write your two codes side by side and hit Measure runtime...

[Former Member]

your measurment comes for a test system, with this totals for the tables??

> MBEWH has 71.745 records and MARA has 705 records.

... if yes, what is the question?

With this setup the bahavior is o.k.

But does it help for your production system? Not at all. How many records are in MARA in the production system?

How records will come back?

Maybe archiving could be a godd starting point.

Otherwise, please add ST05 information to such questions!!!!

Siegfried

[guilherme_frisoni]

Hi,

I just found that besides the current client has just 80000 records, there are two hidden clients in the same server with 50 million rocords.

Checking ST05, when I run SELECT just with MBEWH, any index is used, even after I created an Index in the where field.

But when using a JOIN with MARA, the primary index is used and performance is much better.

Thanks,

Frisoni

[Former Member]

> any index is used,

there is no any index, either an index is used, then it has a name or no index.

You can see the index only in the ST05, with the function DB-explain

A secondary index can be as good as the primary index!

The data of th other clients are irrelvant you will not see and not search them.

[guilherme_frisoni]

Hi,

ST05 execution plans are below. In same order as the code posted before.

SELECT STATEMENT ( Estimated Costs = 51.113 , Estimated #Rows = 555.197 )

       3 NESTED LOOPS
         ( Estim. Costs = 51.113 , Estim. #Rows = 555.197 )
         Estim. CPU-Costs = 1.091.447.843 Estim. IO-Costs = 51.021

           1 INDEX FAST FULL SCAN MARA~0
             ( Estim. Costs = 48 , Estim. #Rows = 7.282 )
             Estim. CPU-Costs = 9.706.161 Estim. IO-Costs = 47
             Filter Predicates
           2 INDEX RANGE SCAN MBEWH~0
             ( Estim. Costs = 7 , Estim. #Rows = 76 )
             Search Columns: 3
             Estim. CPU-Costs = 148.550 Estim. IO-Costs = 7
             Access Predicates Filter Predicates

SELECT STATEMENT ( Estimated Costs = 76.169 , Estimated #Rows = 1.768.067 )

       1 TABLE ACCESS FULL MBEWH
         ( Estim. Costs = 76.169 , Estim. #Rows = 1.768.067 )
         Estim. CPU-Costs = 19.049.481.694 Estim. IO-Costs = 74.562
         Filter Predicates

I created the following index:

NONUNIQUE  Index   MBEWH~Z01

Column Name                     #Distinct

LFGJA                                          5
MANDT                                          6

Why this index is not used, and a full scan is made?

Thanks,

Frisoni

[HermannGahm]

Hi Frisoni,

your first examle reads all records (keys) of a small table (MARA) in the most efficient way possible (index fast full scan),

and joins these small number of lines to a big table where for each line a range scan with quite selective fields (MATNR) is done.

for your second example:

>

> Why this index is not used, and a full scan is made?
>

From all the different figures in your post, i don't get how much records your MBEWH table has. However, the optimizer

makes the following assumption:

estimated nr. of records = ( nr of table rows / nr of clients (6) / nr of LFGJA (5) )

so estimated nr. of records = (nr. of table rows / 30 )

A quite unselective index range scan has to be performed. That is probably not as efficient as one full table scan.

Reccomendation for THIS example:

Add the MATNR field to your index and the optimizer will probaly swich from a full table scan to a (fast)full index scan.

But that might still be slower than your join, since your join starts on MARA (few records) and access MBWEH only a few times for the few records.

For your index design you have to use the figures from your productive systems...

nr. of clients in production, nr. of records in MARA and MBWEH how much records fullfil your condition WHERE mbewh~lfgja = '2009' on MBWEH.... with this information one can come up with a recommendation for production...

Kind regards,

Hermann

[Former Member]

Add Mandt on ur select statement

SELECT mbewh~matnr

FROM mbewh

INTO TABLE gt_mbewh

WHERE mbewh~lfgja = '2009'

and mandt = sy-mandt.

[Former Member]

I don't understand what you expect from your index!

Go to this explain TABLE ACCESS FULL MBEWH

Doubleclick on the tablename MBEWH

=> what do the statistics show?

How many records are in the table,

Scroll down what indexes do you see, is there your Z01 index, does it have statistics data?

If no, it can not be used.

If yes, what is the number of distinct values of lfgja. It can hardly be larger than 10.

Siegfried

[guilherme_frisoni]

Hi Siegfried,

the index statistics in MBEWH are as following:

NONUNIQUE  Index   MBEWH~Z01

Column Name                     #Distinct

LFGJA                                          5
MANDT                                          6

Last statistics date                  10.07.2009
Analyze Method               Sample 531.679 Rows
Levels of B-Tree                               3
Number of leaf blocks                    148.100
Number of distinct keys                       12
Average leaf blocks per key               12.341
Average data blocks per key              171.933
Clustering factor                      2.063.200

I can't understand why this index is not used, or what I have to to to create an index by LFGJA.

Thanks,

Frisoni

[HermannGahm]

Hi,

the optimizer uses these figures to calculate the cost for your range scan:

> Levels of B-Tree 3

> Number of distinct keys 12

> Average leaf blocks per key 12.341

> Average data blocks per key 171.933

> Clustering factor 2.063.200

the resulting cost is higher than the cost for a full table scan.

therefore it chooses a full table scan.

For cost comparison the table statistics are missing.

you can try to enforce the index usage:

%_hints oracle("MBEWH~Z01" "MBWEH")

but it will probaly be not faster... (depending on your datadistribution).

in case of unequal data distribution a hint (but not the index hint, rather substitute values and histograms)

could make sense.

Kind regards,

Hermann

[guilherme_frisoni]

Thanks Hermann,

I forced to use Z01 index with %_hints oracle 'INDEX(MBEWH "MBEWH~Z01")', and got a much better result for without join statement:

With join:      96.217
Without join:      93.781

The execution plan was that:

SELECT STATEMENT ( Estimated Costs = 184.550 , Estimated #Rows = 1.768.067 )

       2 TABLE ACCESS BY INDEX ROWID MBEWH
         ( Estim. Costs = 184.550 , Estim. #Rows = 1.768.067 )
         Estim. CPU-Costs = 3.217.515.212 Estim. IO-Costs = 184.279

           1 INDEX RANGE SCAN MBEWH~Z01
             ( Estim. Costs = 12.427 , Estim. #Rows = 4.420.167 )
             Search Columns: 2
             Estim. CPU-Costs = 974.045.977 Estim. IO-Costs = 12.345
             Access Predicates

Now i'm going to test this results in production.

Thanks,

Frisoni

[HermannGahm]

Hi Frisoni,

>

> I forced to use Z01 index with %_hints oracle 'INDEX(MBEWH "MBEWH~Z01")', and got a much better result for without join statement:

> > With join: 96.217

> > Without join: 93.781

that doesnt sound MUCH better... more or less equal...

However BE CAREFULL with forcing index usage if your data distribution is not equal

and your where conditions are changing (different values) you can easily get results

which are MUCH worse than the original full table scan.

Analyze your data distribution and your queries (WHERE conditions) on your production system... THEN think about

how to optimize your accesses...

Kind regards,

Hermann

[HermannGahm]

Hi Frisoni,

as said the estimated cost for the fts is lower:

SELECT STATEMENT ( Estimated Costs = 76.169 , Estimated #Rows = 1.768.067 ) 
       1 TABLE ACCESS FULL MBEWH
       ...

SELECT STATEMENT ( Estimated Costs = 184.550 , Estimated #Rows = 1.768.067 )
 ...
       2 TABLE ACCESS BY INDEX ROWID MBEWH
...
           1 INDEX RANGE SCAN MBEWH~Z01

the reason why it runs faster is probably the uneven data distribution. So, again,

be carefull with forcing index usage when you access the data with different

variables for mandt and lfgja.. .

and btw...

for this query:

SELECT mbewh~matnr

FROM mbewh

INTO TABLE gt_mbewh

WHERE mbewh~lfgja = '2009'.

you could try with an index with an index on MANDT, LFGJA, MATNR.

Then there is NO table access required anymore. Therfore the optimizer may choose

it without a hint since it is a covering index. And the optimizer can go for a

index fast full scan, which is faster than an index range scan (what you have

currently with the index hint).

and

Kind reards,

Hermann

[Former Member]

> With join: 96.217

> Without join: 93.781

before downtime I wanted to write that this is equal, repeat measurement and it might change.

The costs show the opposite as Hermann pointed out.

So if there is a runtime difference, then is depends on the actual distribution of the data in the different years compared to the equal distribution which the optimizer assumes.

One field with 5 different vaules can not really help to get fast access,

only if you search for the value which is very rare much lower than 10%.

If you system has the last 5 years of data, then this years has still less than 1/5 of data, but hopefully after 6 six months already

much more 1/10 = 10% ( > 5% - 10% is the value where the full table scan becomes better)

Siegfried

[Rui_Dantas]

Guilherme, Hermann, Siegfried,

I have just seen this thread and read it from top to bottom, and I would say now is a good time to make a summary..

This is want I got from Guilherme's comments:

1) MBEWH has 71.745 records 
2) There are two hidden clients in the same server with 50 million rocords.
3) Count Distinct mandt = 6
4) In production client, MBEW has 68 million records

First measurement

With join               : 27.166
Without join            :  103970.297

Second measurement

With join               : 96.217
Without join            : 93.781            << now with hint

The original question was to understand why using the JOIN made the query much faster.

So the conclusions are:

1) Execution times are really now much better (comparing only the not using join case, which is the one we are working on), and the original "mystery" is gone

2) In this client, MANDT is actually much more selective that the optimizer thinks it is (and it's because of this uneven distrubution, as Hermann mentioned, that forcing the index made such a difference)

4) Bad news is that this solution was good because of the special case of your development system, but will probably not help in the production system

5) I suppose the index that Hermann suggested is the best possible thing to do (the table won't be read, assuming you really only want only MATNR from MBEWH, and that it wasn't a simplification for illustration purposes); anyway, noone can really expect that getting all entries from MBEWH for a given year will be a fast thing...

Rui Dantas

[guilherme_frisoni]

Hi again,

after some tests in production, i'm writing the results:

MBEWH has 68.466.116 records, all in the same MANDT.

The select statement is as follow:

    SELECT matnr
           bwkey
           lfgja
           lfmon
           SUM( lbkum )
           SUM( salk3 )
      FROM mbewh
      INTO TABLE gt_mbewh
      WHERE lfgja = '2009'
        AND bwtar NE space
      GROUP BY matnr
               bwkey
               lfgja
               lfmon
      %_HINTS ORACLE 'INDEX(MBEWH "MBEWH~Z01")'.   *Used in case 2 only

Z01 index is updated:

MANDT                                          1
LFGJA                                          5

Last statistics date                  14.07.2009
Analyze Method              mple 68.466.122 Rows
Levels of B-Tree                               3
Number of leaf blocks                    190.714
Number of distinct keys                        5
Average leaf blocks per key               38.142
Average data blocks per key              375.429
Clustering factor                      1.877.145

-

---

Case 1, withou use Z01 index:

SELECT STATEMENT ( Estimated Costs = 211.498 , Estimated #Rows = 11.737.048 )

       2 HASH GROUP BY
         ( Estim. Costs = 211.498 , Estim. #Rows = 11.737.048 )
         Estim. CPU-Costs = 40.495.510.483 Estim. IO-Costs = 208.081

           1 TABLE ACCESS FULL MBEWH
             ( Estim. Costs = 95.980 , Estim. #Rows = 11.737.048 )
             Estim. CPU-Costs = 25.870.861.489 Estim. IO-Costs = 93.797
             Filter Predicates

Time to execute: 139.392.371

[guilherme_frisoni]

Continuing last post...

-

---

Case 2. using Z01 index:

SELECT STATEMENT ( Estimated Costs = 529.965 , Estimated #Rows = 11.737.048 )

       3 HASH GROUP BY
         ( Estim. Costs = 529.965 , Estim. #Rows = 11.737.048 )
         Estim. CPU-Costs = 24.964.240.012 Estim. IO-Costs = 527.859

           2 TABLE ACCESS BY INDEX ROWID MBEWH
             ( Estim. Costs = 414.447 , Estim. #Rows = 11.737.048 )
             Estim. CPU-Costs = 10.339.591.018 Estim. IO-Costs = 413.575
             Filter Predicates

               1 INDEX RANGE SCAN MBEWH~Z01
                 ( Estim. Costs = 38.400 , Estim. #Rows = 13.693.223 )
                 Search Columns: 2
                 Estim. CPU-Costs = 3.010.299.450 Estim. IO-Costs = 38.146
                 Access Predicates

Time to execute: 23.509.085

-

---

For now I'll be using Z01 index.

Thanks for all that help with discussions,

Frisoni

[HermannGahm]

Hi Frisoni,

thanks for the update.

Assuming that you get the better run time because of the data distribution, your query may get

slower and slower the bigger the lfgja = '2009' share will get. Maybe it will get slower as the

full table scan as well.

To avoid 'nailing down' an execution plan which is now good an later bad you should consider

histograms and the substitute value hint as well for this qery. With that you provide the optimizer

with all the information about the data distribution and so, it can came up with the optimal execution

plan for any case (range scan for a small share of lfgja = '2009' or a full table scan for a large share

for lfgja = '2009').

Kind regards,

Hermann

[Former Member]

did you see that the estimated costs show just the opposite from your measurement.

So you can check the estimation with the actual behavior, if you tell us how many rows have actually been searched,

the estimation was 11.000.000, I assume that it was much less. This information is also shown in the SQL-Trace.

And as written above, at the end of 2009, the estimation will not change, but the actually read rows will be higher.

Siegfried

[guilherme_frisoni]

Hi,

there are 10.134.924 records for year 2009 in that table.

Frisoni

[HermannGahm]

Hi Frisoni,

that's interesting. That means that there is NO significant uneven data distribution.

The optimizer calculates cost for a full table scan way cheaper than for an index range scan.

However: The index range scan executes much faster than your full table scan.

Since the opmizer besides the object statistics (we talked about) uses parameters and hardware

speed as well we would have to analyze your configuration to find the cause for that. That could

be cumbersome.

Howerver an easy thing to do would be to compare the ressource usage of both statement executions.

Ressources in terms of buffergets (per execution and row) and disk reads (per execution and row).

That could give us at least an idea why the range scan executes faster but not why the optmizer

calculates the cost like this (in favor for a full table scan).

It could be, that your index range scan requires more overall ressources in terms of buffer gets but less

ressources in terms of disk reads, since single block i/O (what the range scan does) caches the data

in a different manner than the multiblock i/O (what the full table scan does). And here your configuration

plays an important role too (i/O speed, cache size, parameters e.g. multiblock i/o size, ...).

Kind regards,

Hermann