Regarding ST05

[Former Member]

Hi,

got a query regarding this:

if in st05, if there are more than 1 prepare and open statements for a table?

what does this imply?

when we open ST05, what are things we have to give importance?

[Former Member]

..... 80% means 80% of the duration is ABAP and not SQL. I am wondering why this numbers create so much confusion. Applications are so different, that there not correct and wrong percentages.

In prinpciple DB% should be around 30%, between 15 and 40. But some applications do not really more than a database update, for them numbers up to 80% can be correct.

So think about what you would expect and compare it with what was measured.

Red SQL numbers in ST05, no meaning just more than 100ms, similar in SE30 overview, there it is more than 50%. This colors are meaningless. .... because it always depends ....

Siegfried

[Former Member]

Hi,

This is an excerpt from a [blog|https://www.sdn.sap.com/irj/sdn/weblogs?blog=/pub/wlg/7205] [original link is broken] [original link is broken] [original link is broken]; by Siegfired. He Says:

When the trace result is displayed the extended trace list comes up. This list shows all executed statements in the order of execution (as extended list it includes also the time stamp). One execution of a statement can result in several lines, one REOPEN and one or several FETCHES. Note that you also have PREPARE and OPEN lines, but you should not see them, because you only need to analyze traces of repeated executions. So, if you see a PREPARE line, then it is better to repeat the measurement, because an initial execution has also other effects, which make an analysis difficult.

Regards,

Madhumitha

[Former Member]

i have already gone thru this.

But i really dint understand what he means?

[Former Member]

Hi,

you re-run the program and take the st05 result. actually...u fill up the buffer by executing the program couple of time before analysing its performance.

when using se30 also, run it couple of times to take the average time to get the better result.

regards,

madhu

[Former Member]

the 1st time when i did st05 i got a prepare statemnt but now im just getting reopen and fetch.

what does it mean?

[Former Member]

the prepare is the operation where the database determines which access path and index should be used for a SELECT. The result is stored and used again when the statement is executed again.

The OPEN opens the connection to the database, the fetch gets the data.

The REOPEN reuses the result of the prepare.

The costs are not so much different, BUT there are statement which use buffered tables. In the first execution they will read the data to fill the buffers, maybe the whole table is buffering is full table buffering. In the second execution the data will be read from the buffer and not from the database anymore.

The difference is something to zero, i.e. huge.

Therefore analyse the trace of a repeated execution.

And switch to the condensed view 'Summary by SQL statements', this gives a much better overview.

Siegfried

[Former Member]

when i go for summarize for sql statements, it shows me the no . of executions for a table.

and it is 1 or 2.

is this fine?

[Former Member]

all executions which are not more than 50 are o.k., performance is determined by the duration, there you should look whether something is o.k.

[Former Member]

ok. 1 last doubt.. if the ABAP time in se30 is more than 80% what does it mean?

and in SQL trace if the duration is in red color what does it mean?

please let me know.

[Former Member]

..... 80% means 80% of the duration is ABAP and not SQL. I am wondering why this numbers create so much confusion. Applications are so different, that there not correct and wrong percentages.

In prinpciple DB% should be around 30%, between 15 and 40. But some applications do not really more than a database update, for them numbers up to 80% can be correct.

So think about what you would expect and compare it with what was measured.

Red SQL numbers in ST05, no meaning just more than 100ms, similar in SE30 overview, there it is more than 50%. This colors are meaningless. .... because it always depends ....

Siegfried

[Former Member]

thank you so much Siegfried.

i was really confused with all these colors.

thanks.