Skip to Content
author's profile photo Anjana Rao
0
Anjana Rao

Fetch FETCH NEXT CURSOR taking long time in pre-prod compared to Prod

Posted on Nov 04, 2019 at 01:51 PM 252 Views

Hi

We are scheduling a standard program RFKKOP04 in production and pre-production(Data refreshed from production last week).Program is running very slow in pre-prod we have checked all the resources between the systems and they have same capacity.

When i look at ST04 and explain statement,


In pre-Prod : Estimated cost =3,985,263 Estimated CPU cost = 46,538,639,201

In production : Estimated Cost = 840,778 Estimated CPU-Costs = 9,447,973,201

I have checked indexes in both systems they are same.

What else should we check to know the reason for extra time in pre-production?

Thanks for your help.

Cheers,

Anjana

Add a comment
10|10000 characters needed characters exceeded

Comment on This Question

Assigned Tags

Related questions

5 Answers

  • author's profile photo Frederic Girod
    Frederic Girod
    Posted on Nov 04, 2019 at 02:17 PM
    0

    There is a program to recalculate the quality of the index, do you know it ?

    (the table is not buffered ?)

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Juan Reyes
    Juan Reyes
    Posted on Nov 04, 2019 at 02:24 PM
    0

    Have you done an SQL trace and see where is the time been spent...

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Anjana Rao
    Anjana Rao
    Posted on Nov 04, 2019 at 02:15 PM
    -1

    Hi Frederic,

    Thanks for your response.We have tried that already.

    Still we have this issue.Anything else you can suggest us?

    Cheers,

    Anjana

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Anjana Rao
    Anjana Rao
    Posted on Nov 04, 2019 at 02:22 PM
    -1

    Thanks for your response Fredric.

    No i am not aware of it.. but indexes are same in both production and pre-production.

    Production job finishes in 1 hour but in pre-prod it is taking 21 hours to finish.

    Add a comment
    10|10000 characters needed characters exceeded

    • Frederic Girod
      Nov 04, 2019 at 02:39 PM

      (use the comment button, instead of creating each time an answer)

      The index could be the same, but if the quality is not the same, the result will be totally different.

      The first things is the note & the stats. SAP determines the best way to find the data. Based on the notes/stat. That means for a system it could choose to use index_1 and for the second system to use index_2

      Second point, like the defrag tool in windows, your index could be fragmented, this will be detected by the note of the index. If you rebuilt correctly the index, SAP rebuild fully the index.

      https://blogs.sap.com/2016/07/05/index-storage-capacity-analysis-re-build/

      Note 771929 - FAQ: Index fragmentation

  • author's profile photo Anjana Rao
    Anjana Rao
    Posted on Nov 04, 2019 at 02:37 PM
    -1

    Yes i have done that it has massive time difference between 2 systems.

    Now after Fredric suggestion i am in T-Code DB02OLD ->Go to-> Tables & Indexes and i can see difference in pre-prod and production in KBytes and Blocks and Extents for Object DFKKOP.

    Pre-Prod : 6,846,464(KBytes),855,808(Blocks),288(Extents)

    Production: 14,359,552(KBytes),1,794,944(Blocks),415(Extents)

    Not sure what they actually mean.

    but i can see these difference do you think that could be causing issues?

    Add a comment
    10|10000 characters needed characters exceeded

    • Anjana Rao
      Nov 04, 2019 at 03:50 PM

      Thanks for your detailed explanation Fredric.

      Index Storage Quality in both systems.

      DFKKOP~1 ==> 64(Production ) == 91(Pre-Production)

      DFKKOP~2 ==> 41(Production) == 88(Pre-Production)

      DFKKOP~3 ==> 43(Production) == 85(Pre-Production)

      DFKKOP~4 ==> 63(Production) == 93(Pre-Production)

      DFKKOP~5 ==>.58 (Production) == 94(Pre-Production)

      DFKKOP~6 ==> 42(Production) == 82(Pre-Production)

      DFKKOP~Z1 ==> 64(Production) == 90(Pre-Production)

    Comment on This Answer

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.