9 Replies Latest reply: Jun 25, 2012 4:18 PM by Yuri Ziryukin RSS

Performance issue with FS00

S S
Currently Being Moderated

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

  • Re: Performance issue with FS00
    Thomas Zloch
    Currently Being Moderated

    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

    • Re: Performance issue with FS00
      S S
      Currently Being Moderated

      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

      Execution plan_client 100.JPG

      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

      • Re: Performance issue with FS00
        Rob Burbank
        Currently Being Moderated

        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

        • Re: Performance issue with FS00
          S S
          Currently Being Moderated

          Hi Rob,

           

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

           

          Thanks

          SS

          • Re: Performance issue with FS00
            Prashant Patil
            Currently Being Moderated

            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

            • Re: Performance issue with FS00
              S S
              Currently Being Moderated

              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

              • Re: Performance issue with FS00
                Yuri Ziryukin
                Currently Being Moderated

                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?

Actions