topic Re: Regarding ST05 in Application Development and Automation Discussions

Regarding ST05

Former Member — Tue, 08 Jul 2008 10:17:27 GMT

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?

Re: Regarding ST05

Former Member — Tue, 08 Jul 2008 10:22:31 GMT

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

Re: Regarding ST05

Former Member — Tue, 08 Jul 2008 10:29:42 GMT

i have already gone thru this.

But i really dint understand what he means?

Re: Regarding ST05

Former Member — Tue, 08 Jul 2008 10:36:46 GMT

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

Re: Regarding ST05

Former Member — Tue, 08 Jul 2008 10:43:56 GMT

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

what does it mean?

Re: Regarding ST05

Former Member — Tue, 08 Jul 2008 11:22:53 GMT

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

Re: Regarding ST05

Former Member — Tue, 08 Jul 2008 13:26:18 GMT

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?

Re: Regarding ST05

Former Member — Wed, 09 Jul 2008 07:54:12 GMT

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.

Re: Regarding ST05

Former Member — Wed, 09 Jul 2008 10:40:07 GMT

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.

Re: Regarding ST05

Former Member — Wed, 09 Jul 2008 10:59:30 GMT

..... 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

Re: Regarding ST05

Former Member — Wed, 09 Jul 2008 11:02:28 GMT

thank you so much Siegfried.

i was really confused with all these colors.

thanks.