Data Fetching Process is very slow in VA02

[former_member182034]

hi All,

When I am executing VA02 then it's taking too much time(8 to 10 minuts) during field selection and click on Scroll Bar for down.

I analyzed the trace(ST05) of VA02 and there is process of data fetching from table MCHB is very slow and in red high value. please have a look on following small portion of trace and guide me how can i manage this issue.

182 MCHB       PREPARE            0 SELECT WHERE "MANDT" = :A0 AND "MATNR" = :A1 AND "WERKS" = :A2
  1 MCHB       OPEN               0 SELECT WHERE "MANDT" = '600' AND "MATNR" = '000000500000000030' AND "WERKS" = '1000'
451 MCHB       FETCH     145      0
123,137 MCHB       FETCH     145      0
  180,671 MCHB       FETCH     145      0
  119,114 MCHB       FETCH     145      0
  126,028 MCHB       FETCH     145      0
  130,840 MCHB       FETCH     145      0
   90,472 MCHB       FETCH     145      0
   62,027 MCHB       FETCH     145      0
   98,619 MCHB       FETCH     145      0
  115,603 MCHB       FETCH     145      0
  156,984 MCHB       FETCH     145      0
  151,516 MCHB       FETCH     145      0
  113,686 MCHB       FETCH     145      0

Regards,

majamil

[fidel_vales · Accepted solution]

Hi,

I do not think the issue is accessing the data itself (of course, and as usual, there is not enought information to do a proper analysis, only to GUESS)

you mention

it's taking too much time(8 to 10 minuts)

then

I analyzed the trace(ST05) of VA02  

BAD,starting there means that you think it is a DB issue and not ABAP and you miss inforation in your analysis,

 there is process of data fetching from table MCHB is very slow 

are you sure?

in the little piece you show (you should not look at that piece but at the aggregate summary to get a better overview)

I can see that the program retrieves around 1885 rows and it takes less than 1 ms per row.

If that continues 8 minutes (as you mentioned) then you read 1/2 million rows (aprox and from the top of my head, please review the trace, it will tell you how many rows)

Do you really need 1/2 million rows?

I do think the issue is not the "speed" but the wrong conditions

BTW.

I strongly recommend to everybody to check the following blogs (to stat)

1.- [Single Transaction Analysis (ST12) u2013 getting started|http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/15702] [original link is broken] [original link is broken] [original link is broken];

2.- [The SQL Trace (ST05) u2013 Quick and Easy|http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/7205] [original link is broken] [original link is broken] [original link is broken];

and their followups

[Former Member · Accepted solution]

Hi Fidel,

Of course, we can only guess what is happening at the behind of the stage, unless connecting to the remote site. I would like to indicate that I didn't stuck only the database performance, by my recommendations. If you check the previous thread, as I noted below, I already recommended SE30, in order to understand the root cause.

Please refer the thread <a class="jive_macro jive_macro_message" href="" __jive_macro_name="message" modifiedtitle="true" __default_attr="10314047"></a>

There are many points to check such as comparing statistic values on ST03 or SE30 and many more... At this stage, I recommended ST05 with SE30, also. This thread is continuation of the previous thread. Please check the statement in the previous thread, below;

Furthermore, I can recommend you that analyze VA01 
and VA02 transactions by using SE30 and ST05 transactions, 
with a developer, to find out the bottleneck. It may be a user-exit 
or a missing index or wrong CBO stats. There are many points to 
check, in order to identify the root cause from away ;)

Moreover, as you can see that I suggested to check user-exits or some other customer enhancement in VA02.

In summary, yes we can only guess about the performance problems or wrong conditions. Please don't misunderstand me, but I wanted to explain the situation, my friend

Best regards,

Orkun Gedik

[fidel_vales · Accepted solution]

Hi,

Please, don't feel offended.

That was not my intention.

I looked at the OP, you (I assume a different person) pointed to a thread (called "Note 830576 - Parameter recommendations for Oracle 10g") and I did not read that one as it is talking about PARAMETERS.

Sorry I did not read fully to the end and stopped on the first post.

But still, I think all my points are valid.

1.- small part of a trace

2.- 29 lines returning 145 rows each (for a total of 4205 rows)

3.- Still 1 ms per row

4.- Still a lot of row

[former_member182034 · Accepted solution]

hi Fidel,

thanks for taking interest.

Dear, ABAP Runtime Analysis

Transaction     VA02 = Change Sales Order                            

                Execution time in microseconds:

          ABAP                                                  166,393,296  =  90.0%
      Database                                                   18,222,523  =   9.9%
        System                                                      210,481  =   0.1%

               0%                  50%                 100%     184,826,300  = 100,0%

its mean i am getting issue on ABAP level and as you know that i can'nt send full runtime analysis and trace of VA02 into the reply. So, please tell me how can i share whole log with u?

Dear Orkun,

now I created a OSS and SAP support sent me following reply

ST12 is not available in the system and I did not find the correspondingtrace log
 you attached in ST05 either, althrough in STAD I was able to
see some log about VA02 ran by you. There're a couple of database tables 
that may need to look at, for instance MCHB and ATP_EKET. Since the
relative SQL statements cannot be located, I couldn't see the execution
plan, but as a workaround you may consider to build
secondary indexes tosuit the WHERE condition,
for example table MCHB has only the primary index, 
you could try to improve the performance  of a particular 
statement by creating a new index
 that fits it's WHERE clause.

Regards,

[Former Member · Accepted solution]

Hi Majamil,

Could you check which module is causing this problem by executing the VA02 transaction in SE30 and Evaluate -> Go to -> Hit list -> Standard (F5). Then sort the table by "Net" column. By doing so you will able to identify, which module is causing this performance problem and go into the detail with a developer. This is the first step to identify the problem.

As noted in the OSS message clearly, the system is accessing many tables during VA02 transaction. So, you will identify long running statements with ST05 transaction. At this stage, you need to interpret, on both output (ST05 + SE30).

As already mentioned by the other guy, you can also perform these traces on ST12, also.

As a summary of it, identify which module is causing this problem and work with a developer, in order to understand the problem, at this step.

Best regards,

Orkun Gedik

[fidel_vales · Accepted solution]

Hi,

Thank you very much.

That clarifies a lot.

90% of the time is spent at ABAP.

so, if you were able to eliminate ALL DB time (not possible) you would reduce the runtime of that transaction only 10% (18 seconds)

therefore the focus should be done in the ABAP.

I'm not an ABAP guy, so I cannot help much there.

Even if the SAP people insist on "tuning" the DB, that should only be done after reducing the ABAP part I insist, we are talking about 10% of the response time.

You can also tell them how to reproduce the issue so they can use ST12 to trace it and find out where the issue is.

Orkun Gedik is indiating you how to check the module where most of the time is spent

[former_member182034 · Accepted solution]

thx to respected Orkun and Fidel for you valuable replies.

dear Fidel

now system is running fine after changing some DB parameters.

Regards,

majamil

[Former Member]

Hi Majamil,

Create a secondary index on MCHB table with the fields, below, named "Z01";

"MANDT"

"MATNR"

"WERKS"

But, please get a confirmation from OSS, before you create this index.

Please refer the thread

Best regards,

Orkun Gedik

[Former Member]

Hi Majamil,

By the way, did you check where this SQL statement is called from, in VA02 transaction? What is the program, I mean? That may be called in a user-exit and causing this problem.

Best regards,

Orkun Gedik