Strange performance problem

[TMNielsen]

Hi

We have huge performance problems with ATP check when creating production order - but only in development system.

I have debugged and isolated the problem to one select statement that is performed for every component in BOM. The select is found in SAPLATP2 FORM RESB_DB_READ.

This select uses about 300 seconds, but I have tried to copy the select statement to a test program, and tested with same matnr and werks. Then the select statement uses less than 1 second.

This I find very strange - any explanation will be appreciated.

        
...
IF  s_sbed > 0.
ENHANCEMENT-SECTION     RESB_DB_READ_05 SPOTS ES_SAPLATP2.
          SELECT *
          FROM   atp_resb
          INTO   atp_resb
          WHERE  matnr = atpmat-matnr
          AND    werks = atpmat-werks
          AND    xloek = space
          AND    kzear = space
          AND    sobkz IN (space, sobkzk)
          AND    bdmng > atp_resb~enmng
          AND    txtps = space
          AND    schgt = space
          AND    dbskz = space
          AND  ( vorab IS NULL
              OR vorab = space ).
            CLEAR atp_resb-sobkz.
*         checks
            resb_check atp_resb
                       p_atpmex.
          ENDSELECT.
END-ENHANCEMENT-SECTION.
...

[ThomasZloch]

Did you compare the ST05 execution plans for both the actual case and your test case? Please paste them here to allow a remote analysis. Plus whatever the actual experts here might want to know from you.

I assume your test case was performed in the same box where the problem occurs.

Thomas

[TMNielsen]

Hi Thomas

I'm not sure what you mean by "ST05 execution plans"?

Best regards Thomas

[HermannGahm]

> I'm not sure what you mean by "ST05 execution plans"?

have a look here:

/people/hermann.gahm/blog/2010/03/04/explaining-the-explain-plan

KInd regards,

Hermann

[brad_bohn]

Assuming all of the parameters are the same for both executions, make sure your SQL statement cache is cleared before each execution (DB02 or ask your Basis counterpart for assistance).

[Former Member]

Table RESB has index M that would work very well with this query. I do not think the select statement is a problem. It could have something to do with the following macro.

resb_check atp_resb
                       p_atpmex.

Double click 'resb_check' and it will show you the following code in include LATP2TMR.

DEFINE resb_check.
  case &1-bdart.
    when skbed or brbed.
      perform resb_skbed_brbed_check
              tables &2
              using  &1.
    when arbed or mrbed.
      perform resb_arbed_mrbed_check
              tables &2
              using  &1.
  endcase.
END-OF-DEFINITION.

Take a look at subroutines 'resb_skbed_brbed_check' and 'resb_arbed_mrbed_check'. Your problem might lie there rather than in the select statement you are looking at.

[TMNielsen]

Hi

Brad Bohns suggestion actually was the solution. I did clear the SQL statement cache in ST04 and the problem was gone.

The SQL explain of the statement in ST05 showed that the statement would use index M and O on table RESB, but the actual execution explain in ST04 showed a full table scan.

For some unknown reason the program would find a wrong cached SQL statement, so when the cache was cleared, the problem was gone.

I have heard of this possibility before - in 2004 when I took the BC490 SAP course - ABAP Performance Tuning, but I have never actually seen the problem before. I don't think this info was a part of the official SAP course, but I remember that the instructor mentioned this possible problem.

Lesson learned and problem solved.

Best regards

Thomas Madsen Nielsen

[HermannGahm]

Hi Thomas,

just for clarifcation:

are you running on MSSQL? or another database?

Kind regards,

Hermann

[TMNielsen]

We use MSSQL.

[HermannGahm]

Hi Thomas,

the creation of the index will invalidate the statement of the cache. Therefore for any statement executed after

the new index was created the optimizer will see it and take it into account and will not use an old cached plan.

As far as i know this is true on all databases that are supported by SAP. (i never came across problems here).

Data distribution changes... now we talk about statistics. Same databases do not have a problem with this.

E.g. MaxDB since they do a so called evaluation all the time. The new version of DB6 (9.5 or 9.7) uses so

called real time statistics and at least theoretically should see the new data distribution as well (of course

there is some threshold where this feature kicks in, below the threshold the old behaviour is valid. The old

behaviour is what we have on all other databases as far as i know. The old cached plan is used the new

data distribution is only taken into account when new statistics are created. The creation of new statistics

leads to invalidation of the old plans.

For your problem it is most likely (this is an educated guess), as you said, the parameter issue. MSSQL

evaluates the parameters for the FIRST execution of a statement. These parameters led to a full table

scan in your case. All other statements with different parameters will use that plan until the sql statement

is invalidated in the cache. On MSSQL this happens if:

- a new index is created

- new statistics are created

- the statement is recompiled / reparsed (then the parameters for the execution with the recompile / reparse hint are used).

- the statement cache is cleared / invalidated

A new plan will be build as well if the statement is displaced from the statement cache.

Kind regards,

Hermann

[TMNielsen]

Thanks Herman

This was a very usefull overview.

Both the info about how different databases use statistics and the info about cache invalidation is very interesting.

I was wondering when the cache was cleared, and you answered that question before I even asked it.

Amazing!

[Former Member]

Actually, the recommendations to check the execution plan/explain is the common approach. There you would figure out, that they are different.

If you know that, it is not so difficult to come to the conclusion, that a refresh of the cache can help. The cache can be outdated, if a new index was created or if the data distribution has changed.

I do not like the random guessing approach too much.

Siegfried

[TMNielsen]

Hi Siegfried

I also don't like the random guessing approach.

However in this case I don't understand what was the cause of the problem.

I know for sure that it was not a new index created and that it was not because the data distribution has changed.

But I guess that the first attempt to execute this SQL statment after the last time the cache was cleared, was in some way with an atypical parameter list, that resulted in a full table scan.

- and yes - I know it - I used the "guess" word again ;o)

best regards

Thomas

[Former Member]

> I'm not sure what you mean by "ST05 execution plans"?

My comment referred to this, because I think it is more important to know how to analyse than to know the internal of the databases. It depends of course whether you use exactly one db or several even in different releases.