Performance issue with FS00

[Former Member]

Hello Experts,

We have a problem saving changes in FS00 in one client in our Dev system (client 100). It takes around 30 mins to save whereas the same change gets saved in client 300 (same Dev system) in less than 5 mins.

We have analysed the trace and could see this. The execution plan of the REOPEN statement on FAGLFLEXT shows the same information in both clients. However, the FETCH statement following the REOPEN in client 100 shows longer duration and Return code 1403 whereas RC is 0 in client 300. Sample trace records given below.

Duration       Obj. name     Op.              Recs.     RC    Statement

    12           FAGLFLEXT  REOPEN                     0     SELECT WHERE "RCLNT" = '300'

13,321         FAGLFLEXT  FETCH         16384      0

       10            FAGLFLEXT  REOPEN                 0     SELECT WHERE "RCLNT" = '100'

1.744.222        FAGLFLEXT  FETCH        30      1403

Thanks

SS

[ThomasZloch]

Saving a G/L account should take a second or less, not 5 minutes or even 30. Do you have any customer enhancements active? If not, did you look for SAP notes addressing such a problem?

Since you already did the SQL trace, please post the SQL explain plan for both statements. How many records do you have in FAGLFLEXT in both clients?

Not sure what RC 1403 means, what is your DB, is there any short dumps in ST22?

Thomas

[Former Member]

Hi Thomas,

Thanks for your reply.

There are no customer enhancements and no dump in ST22. Database is Oracle 11.2.

Execution plan for REOPEN on FAGLFLEXT in both the clients is as shown below

Partial trace in client 300 is as given below

      544 FAGLFLEXT  PREPARE            0 SELECT WHERE "RCLNT" = :A0 AND "RLDNR" = :A1 AND "RRCTY" = :A2 AND "RVERS" = :A3 AND "RBUKRS" = :A4 AND "RYEAR" BETWEEN :A5 AND :A6 AND "

        7 FAGLFLEXT  OPEN               0 SELECT WHERE "RCLNT" = '300' AND "RLDNR" = 'I1' AND "RRCTY" = '0' AND "RVERS" = '001' AND "RBUKRS" = '1001' AND "RYEAR" BETWEEN 2010 AND

3.994.066 FAGLFLEXT  FETCH       0   1403

      615 FAGLFLEXT  PREPARE            0 SELECT WHERE "RCLNT" = :A0

        7 FAGLFLEXT  OPEN               0 SELECT WHERE "RCLNT" = '300'

   12.901 FAGLFLEXT  FETCH   16384      0

        9 FAGLFLEXT  REOPEN             0 SELECT WHERE "RCLNT" = '300'

   11.975 FAGLFLEXT  FETCH   16384      0

        9 FAGLFLEXT  REOPEN             0 SELECT WHERE "RCLNT" = '300'

   12.034 FAGLFLEXT  FETCH   16384      0

       10 FAGLFLEXT  REOPEN             0 SELECT WHERE "RCLNT" = '300' AND "RLDNR" = 'L1' AND "RRCTY" = '0' AND "RVERS" = '001' AND "RBUKRS" = '1001' AND "RYEAR" BETWEEN 2010 AND

4.858.625 FAGLFLEXT  FETCH       0   1403

       13 FAGLFLEXT  REOPEN             0 SELECT WHERE "RCLNT" = '300'

   12.234 FAGLFLEXT  FETCH   16384      0

       38 FAGLFLEXT  REOPEN             0 SELECT WHERE "RCLNT" = '300'

   11.923 FAGLFLEXT  FETCH   16384      0

        9 FAGLFLEXT  REOPEN             0 SELECT WHERE "RCLNT" = '300'

   11.965 FAGLFLEXT  FETCH   16384      0

       10 FAGLFLEXT  REOPEN             0 SELECT WHERE "RCLNT" = '300' AND "RLDNR" = 'L2' AND "RRCTY" = '0' AND "RVERS" = '001' AND "RBUKRS" = '1001' AND "RYEAR" BETWEEN 2010 AND

7.885.875 FAGLFLEXT  FETCH       0   1403

       12 FAGLFLEXT  REOPEN             0 SELECT WHERE "RCLNT" = '300'

   12.514 FAGLFLEXT  FETCH   16384      0

        9 FAGLFLEXT  REOPEN             0 SELECT WHERE "RCLNT" = '300'

    7.575 FAGLFLEXT  FETCH   16384      0

Partial trace in client 100 is as given below.

        5 FAGLFLEXT  REOPEN             0 SELECT WHERE "RCLNT" = '100' AND "RLDNR" = 'I1' AND "RRCTY" = '0' AND "RVERS" = '001' AND "RBUKRS" = '1019' AND "RYEAR" BETWEEN 2010 AND

      216 FAGLFLEXT  FETCH       0   1403

        5 FAGLFLEXT  REOPEN             0 SELECT WHERE "RCLNT" = '100'

1.692.726 FAGLFLEXT  FETCH      30   1403

        8 FAGLFLEXT  REOPEN             0 SELECT WHERE "RCLNT" = '100'

1.589.049 FAGLFLEXT  FETCH      30   1403

        8 FAGLFLEXT  REOPEN             0 SELECT WHERE "RCLNT" = '100'

1.577.585 FAGLFLEXT  FETCH      30   1403

        6 FAGLFLEXT  REOPEN             0 SELECT WHERE "RCLNT" = '100' AND "RLDNR" = 'L1' AND "RRCTY" = '0' AND "RVERS" = '001' AND "RBUKRS" = '1019' AND "RYEAR" BETWEEN 2010 AND

      313 FAGLFLEXT  FETCH       0   1403

        5 FAGLFLEXT  REOPEN             0 SELECT WHERE "RCLNT" = '100'

1.573.530 FAGLFLEXT  FETCH      30   1403

        7 FAGLFLEXT  REOPEN             0 SELECT WHERE "RCLNT" = '100'

1.565.147 FAGLFLEXT  FETCH      30   1403

        8 FAGLFLEXT  REOPEN             0 SELECT WHERE "RCLNT" = '100'

1.557.773 FAGLFLEXT  FETCH      30   1403

        6 FAGLFLEXT  REOPEN             0 SELECT WHERE "RCLNT" = '100' AND "RLDNR" = 'L2' AND "RRCTY" = '0' AND "RVERS" = '001' AND "RBUKRS" = '1019' AND "RYEAR" BETWEEN 2010 AND

      304 FAGLFLEXT  FETCH       0   1403

        5 FAGLFLEXT  REOPEN             0 SELECT WHERE "RCLNT" = '100'

1.596.781 FAGLFLEXT  FETCH      30   1403

        8 FAGLFLEXT  REOPEN             0 SELECT WHERE "RCLNT" = '100'

Thanks

SS

[Former Member]

Find the include where this select is being done and then compare the source between the two systems.

For some reason the program is selecting 5,000,000 records while updating a single record.

Rob

[Former Member]

Hi Rob,

The program remains the same as these are not 2 systems, just 2 different client of the same system.

Thanks

SS

[former_member223537]

Hi SS,

Well in client 100, the bukrs used is 1019 & in client 300 the bukrs used is 1001.  Please check table FAGLFLEXT for the number of entries for these values.

Client 100 seems to be only for development/config. & client 300 seems to be for testing. There might not be enought data in client 100.

Thanks,

Best regards,

Prashant

[Former Member]

Hi Prashant,

I have pasted sample of the trace from both clients. Trace in both clients have bukrs 1019 and 1001, this set of trace keeps repeating for all company codes.

       10 FAGLFLEXT  REOPEN             0 SELECT WHERE "RCLNT" = '300' AND "RLDNR" = 'L1' AND "RRCTY" = '0' AND "RVERS" = '001' AND "RBUKRS" = '1001' AND "RYEAR" BETWEEN 2010 AND

4.858.625 FAGLFLEXT  FETCH       0   1403

This part looks fine to me as it takes more or less the same time in both. It is the below portion that looks different in both systems. The duration for this portion appears to be in milliseconds in client 300, while in seconds in client 100. Repetion of this accounts to the long time taken

Client 300

        9 FAGLFLEXT  REOPEN             0 SELECT WHERE "RCLNT" = '300'

   11.975 FAGLFLEXT  FETCH   16384      0

        9 FAGLFLEXT  REOPEN             0 SELECT WHERE "RCLNT" = '300'

   12.034 FAGLFLEXT  FETCH   16384      0

Client 100

        7 FAGLFLEXT  REOPEN             0 SELECT WHERE "RCLNT" = '100'

1.565.147 FAGLFLEXT  FETCH      30   1403

        8 FAGLFLEXT  REOPEN             0 SELECT WHERE "RCLNT" = '100'

1.557.773 FAGLFLEXT  FETCH      30   1403

the no. of records and RC code is the same for all company codes.

Thanks,

SS

[yuri_ziryukin]

Hello SS,

this looks really strange now.

If I remember correctly, the return code 1403 is "not found". So, your select from FAGLFLEXT does not find anything in the table with client 100. Why does it take so long then? Most probably due to the full table scan. DB looks for records with client 100 and does not find anything, but for this purpose it needs to scan the full table.

In the first case, client 300, many records are found and the single fetches are fast. But how many of them are coming? If the application is really requesting all records of client 300, then there should be many of them.

What also concerns me is the performance of this select:

SELECT WHERE "RCLNT" = '300' AND "RLDNR" = 'L1' AND "RRCTY" = '0' AND "RVERS" = '001' AND "RBUKRS" = '1001' AND "RYEAR" BETWEEN 2010 AND

If I look at your trace, it takes around 5 seconds! I assume that table statistics is not up-to-date.

Please run transaction DB20, enter table FAGLFLEXT and update statistics for it.

Yuri

P.S. Any by the way, I think there is something wrong with the application. It should NOT select data from this table with client only. This looks wrong from my PoV. Can you show us the place in ABAP coding where the select "SELECT WHERE "RCLNT" = '300'" is coming from?

[volker_borowski2]

Selecting with client only may indicate someone did activate buffering?

Easily to spot, when jumping to the ABAP source and the statement

there is completely different then the one in explain.

Buffering would be no good idea for a beast like FAGLFLEXT.

Volker

[yuri_ziryukin]

Ah, exactly! Normally you also see ORDER BY at the end in addition.

But I hardly could imagine that someone changes technical settings for this table