Performance issue in transaction MB51

[Former Member]

Hello All,

We've been facing performance issues in MB51 transaction.

We've discussed the issues in discussion post:

http://scn.sap.com/message/13162811#13162811

We've implemented whatever solution is discussed in the above post.

As per OSS Note - 1293807, we've created 2 indexes each on tables MSEG & MKPF.

Furthermore, we've also altered query as mentioned in the OSS note.

After implementing solution there are 2 scenarios:

1. Performance improvement when executed with data from 2011
2. Performance decrement when executed with data from 2012

We are providing the same data in both the cases. The difference being just the year in the date range.

When executed with data from 2011

Execution plan for the select query before the solution was implemented:

Execution plan for the select query after the solution was implemented:

In this case, this query is taking less time after the solution is applied.

In this case solution works well

When executed with data from 2012

Execution plan for the select query before the solution was implemented:

    

Execution plan for the select query after the solution was implemented:

In this case the query after the solution is taking more time to execute than the query which was taking before the solution was implemented.

Our Analysis:

1. For year 2011, select query was earlier executing based on index skip scan but now it is executing with the index created.
2. for year 2012, select query was earlier executing first from table MKPF and then from MSEG. But after the solution was implemented the entry point has changed to MSEG then followed by MKPF.

Please provide a solution so that select query executes well in both cases.

Message was edited by: Prashant Gaur

[volker_borowski2]

Hi,

what fields did you put into the new Z51 indexes on both tables?

Since the DB behaves different when executing with different values, it obviously uses histograms.

Have you tried without histograms before? What was different?

What percentage of data did you select for calculating statistics?

Since collecting stats with a high percentage is expensive, you might like to export your currently active statistics for the tables first, so that you can revert quickly (Seconds) instead of re-calculating (Hours ?) (Exporting stats can be done with brtools).

In addition, you should get both statements as executed against the DB so that you can compare plans in ST05 without need to execute the query.

You could try to hint the statement, that is performing bad to be like the one that is ok.

I would start with

leading(T_xx) index(T_xx,"MSEG~Z51")

xx beinig the alias of that MSEG that is used by the plan to start with (not visible in screenshot).

Volker

[Former Member]

Hi Volker,

Thanks for your reply.

We have created 2 indexes on MSEG and 2 on MKPF.

MKPF:

Z51: MANDT, MBLNR, MJAHR, BUDAT

Z52: MANDT, BUDAT, MBLNR, MJAHR,

MSEG:

Z51: MANDT, WERKS, Plant, LGORT, MBLNR, MJAHR, ZEILE.

Z52: MANDT, MBLNR, MJAHR, WERKS, LGORT, ZEILE

How to try without histograms? Do you mean without providing hints to the select statement?

Is there any standard documentation provided by SAP on the oracle database?

Thanks,

Prashant

[yuri_ziryukin]

Hello Prashant,

I strongly recommend you SAP note 1550000. This is a stable and long-term solution for MB51 performance problems. Invested time definitely worth it.

With one of our top customers implementation of this note resulted in performance improvement of up to factor 1000.

Yuri

[Former Member]

Hi Yuri,

Thanks for your reply.

Considering that this would be a huge DDIC change. It would take some efforts to convince the client to implement this solution.

I had a question, suppose if there is a join on table A and table B. Then what determines that which database table would be accessed first and why is it so?

Thanks,

Prashant

[yuri_ziryukin]

Volker Borowski wrote:
Hi,
what fields did you put into the new Z51 indexes on both tables?
Since the DB behaves different when executing with different values, it obviously uses histograms.
Volker

Btw, Volker. In order to use histograms, special DBI hints should be passed from ABAP code to ensure that DBI is sending values of bind variables to DB. This will not happen automatically. I really doubt that this statement is provided with SUBSTITUTE_VALUES or SUBSTITUTE_LITERALS hint in ABAP.

[yuri_ziryukin]

Hello Prashant,

nevertheless this is an official SAP solution and the customer will be protected by SAP support in case any problems arise.

Regarding your question: Database itself decides what execution plan to use in a particular case. In Oracle, for example, it is normally based on statistics (collected from tables and indexes) which describe the number of distinct values for the fields, sizes in blocks, etc. Actually it is very unlikely that one statement gets different execution plans depending on input data. That may happen if some of the ranges in ABAP select statements are empty or not empty.

In your case we definitely need to see the statement itself in both cases.

Yuri

[volker_borowski2]

Hi Juri,

I am aware of this, and I was only concluding this because of the statement given in the linked thread in the opening posting and the fact, that for both years it starts the same way with MSEG for 2011 and 2012, but decides for a nested loop in 2011 and for a hash join in 2012, which should surely have less data than 2011 in average.

So the optimizer decides different, depending on data of the where clause.

I can only think about histograms doing so...

And with Oracle 11, bind_peeking changed a bit, need to dig documentation for details on this ...

Prashant, can you check the Oracle Parameters related to this?

show parameter peek

show parameter bind

should give the related information

Volker

[Former Member]

Hi Yuri,

In both the cases, the same select statement is getting executed.

Yeah some of the ranges provided to the select statement are empty.

But its same in both the cases so shouldn't query behave in the same way in both the cases?

Thanks,

Prashant

[Former Member]

Hi Yuri,

Yes hints are explicitly sent to the database from the select query.

Select query statement:

select (g_t_fields1)
     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
%_HINTS
ORACLE '&SUBSTITUTE VALUES&'
.
   endif.

[Former Member]

Hi Volker,

How do we check these parameters?

Thanks,

Prashant

[yuri_ziryukin]

Ok, here is the answer. Then because of histograms Oracle decides to use different execution plans for the same statement.

[volker_borowski2]

How is the execution plan in both cases, if you do not supply the Hint ?

( Parameter stuff no more relevant: As Yuri explained histgrams are used ...)

Tipp for operatin the SQL-Trace stuff. Always make copys of the entire Screen in Textmode, to be able to re-calculate in ST05 later. You can do a List save (T-code "%PC") to clipboard  and drop it to a plain text file. This way I like to work most of the times.

Volker

Message was edited by: Volker Borowski If you are convinced that a specific plan is better, you can also try to HINT the execution, either by using USE_NL(t1 t2) or by using NO_USE_HASH... Might require a bit google search on Oracle Hints, or you need to provide us the Statement as sent to the DB because we will need to know the name of the generated table aliases. V.

[Former Member]

Hi Volker,

We have created an OSS message with SAP to further investigate the issue

Thanks for all your support.

Regards,

Prashant