Case Study – Use ST04 SQL Stats history in Performance analysis

This post covers how I used SQL historical stats from SAP ST04 in analyzing a true performance incident related to SAP system cloud migration. I used SQL stats from “good” and “bad” periods and compare the stats and found where the problem was. This led to resolution of performance issue. You can refer to my post “SAP ST04 Performance Feature – DBA History” on how to get SQL history stats for specific period.

The background

Business users were reporting that a daily transaction was running much longer with no changes on the code/volume after their SAP system was migrated to DXC cloud solution – ESO environment. Migration was completed on Feb 11, 2018. Performance analysis found that the program is mainly spent their time in one SQL-ID 54u3rqzyff94q. Following is the SQL execution stats from ST04 DBA history for post-migration period.

Figure 1 SAP ST04 – SQL stats in post-ESO period

So naturally, the thinking is to how the sql is performing in the past.

What is performance stats for this SQL in pre-migration period

Business said performance was good prior to ESO migration, I got following SQL stats prior to ESO migration via ST04 DBA history feature.

Figure 2 SAP ST04 – SQL stats in pre-ESO period

Figure 2 shows the SQL execution time was about 68 seconds per execution pre-ESO migration vs 278 seconds in post-migration showed in Figure 1. Why? Further analysis of SQL remaining stats like Disc read/execution, Buffer get/execution, Row processed/execution can normally give hints. Longer response time is normally contributed to more disc read, more buffer gets and more records fetched per execution. In this case, you noticed there was no much difference on those metrics pre and post ESO migration. So I looked further to time breakdown of SQL execution, I found that over 90% of SQL time is spent on I/O as reported under column “User I/O Time/Exec”. So the performance issue is related to slow “I/O” performance. Many factors can be contributed to slow “I/O” performance. In this case, our storage expert found out this was related to “IOPS” setting. Action was taken to increase IOPS setting on March 16. After that the SQL performance is back to pre-migration level as showed below.

Performance stats after IO setting change

SAP ST04 DBA history reported following performance stats for this SQL in post-IO setting changes.

Figure 3 SAP ST04 – SQL stats after IO Tuning

You can see that average execution time is 48 seconds which is much better than 278 seconds during the period when the issue was occurring and even better than pre-migration period. And this improvement is mainly due to IO improvement – IO time per execution is down to 48 seconds from 274 seconds showed in Figure 2.

I got relevant performance stats from Figure 1, Figure 2 and Figure 3. Put them into following table for easier comparison.

 

Pre-ESO migration

Post-ESO migration

Post-ESO IO tuning

Elapse time (Sec)

68

278

48

User I/O time (Sec)

64

274

44

Disc Read

112,830

75,099

106,883

Row processed

1.2

1.5

1.6

 

And business operation has confirmed that their transaction performance is back to normal ever after. What you can see that User I/O is still a significant part of SQL execution time. This can be improved via Oracle cache technically – this needs to weight many factors which are not scope of this posting.

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *