topic Re: Performance Tuning in Application Development and Automation Discussions

Performance Tuning

ishwarya_doss — Mon, 29 Mar 2021 06:54:19 GMT

Hi Experts,

I've few queries related to performance optimization.

1. What is the best way to get the exact response time? I read somewhere like we should repeat the activity (like saving/editing) twice or thrice before starting the trace. Each time, we get different times in trace. - SAT/ST12

2. What is the optimal net time for a select statement or maximum allowed time for a simple select statement. Though it may depend on the conditions. I just wanted to know the max net time for an ideal case.

3. Which tool is best to quickly analyze the performance issues? Though we have ST05, ST12, SAT for exclusive tracing of SQL, ABAP, Process blocks, I'm trying to know what's the one best tool to use for a quick initial analysis.

I'm would like to know more in this regard. Pls, share your suggestions based on your experience.

Thanks in advance!

Re: Performance Tuning

matt — Mon, 29 Mar 2021 07:52:52 GMT

There is no exact response time. Far too many factors affect it. You can only get the average of a number of measurements. Usually though you don't need exact response time - you just need it to be less than it was, or "within 3 seconds".

Depends on what you're doing. If the program is in background then who cares? If it is in foreground and you want a report with 20000 lines as opposed to one with 100 lines it's going to take a bit longer. There is no "max net time". It is all down to user tolerance in the end.

The best tools are ST05, ST12, SAT... Whatever it is exactly you're wanting to do. I use ST05 to establish long running SQL. I use SAT to establish where the bulk of the work in a program is being done.

Re: Performance Tuning

ishwarya_doss — Tue, 30 Mar 2021 16:01:36 GMT

Thanks for your response, Matthew. Also, could you pls help me how to analyze this? From this, how will I come to know whether the select statement has no issue with index? I know barely about index unique scan and full table scan. But not in detail.. Could you pls shed some light?

Re: Performance Tuning

matt — Tue, 30 Mar 2021 16:26:55 GMT

I need the full table definition and the definition of all keys. Also the database being used. And number of rows in the table.

And probably an address where I send the invoice. (joke*)

* Sort of.

In the meantime ask the basis team to rebuild the statistics. It may be that your table is suboptimal.

Re: Performance Tuning

ishwarya_doss — Fri, 09 Apr 2021 15:39:16 GMT

Thanks for your response! Sorry for my delayed response. I'm interested to learn if we have any documents on how to analyze such statements. I mean some blogs to understand Access/Filter predicates, Estimated costs etc.. TIA!

Re: Performance Tuning

matt — Sat, 10 Apr 2021 12:05:21 GMT

Statements in the explain, like "WINDOW NOSORT STOPKEY", are documented in the database documentation and further explain in blogs https://use-the-index-luke.com/sql/explain-plan/oracle/operations

Re: Performance Tuning

gasparerdelyi — Tue, 01 Jun 2021 13:58:40 GMT

Actually, taking SAT or ST12 traces are extremely helpful for analysis but influence the runtime as the information for the trace needs to be collected and written.
If you are interested in pure measurement without analysis of where the time is spent, then STAD and ST03N are your friends.
In certain cases it makes most sense to measure without tracing a number of times and then trace for detailed analysis.

The usual recommendation on repeating before measuring or tracing is 5 times on the instance you are using fpr measurement or tracing.
That ensures that table buffer is filled for those areas that are not changed.

With respect to the best tool: in general I prefer to use ST12 in general. mainly for storage of data including SQL summary into the database and for its hierarchies that assist in analysis a lot.
However there are special use cases when ST12 has some limitations (for example: not all kinds of performance traces are handled in ST12, just to name the most important limitation)