After I posted my blog on how to run SAP ST04, SAP introduced a new feature into ST04 – DBA history which can show history of SQL stats which is only available via separate SQL script before. This is a very helpful feature in SQL performance analysis. In this post, I would talk about the new feature of DBA History of SQL Cursor cache:
- Where you can access DBA history of SQL cursor cache
- What you can use DBA history or history of SQL stats for
Where you can access DBA history of SQL cursor cache
DBA history is an option of Selection Criteria for Shared Cursor Cache screen. You can access the screen: ST04 -> SQL Statement Analysis -> Shared Cursor Cache. After you click “Shared Cursor cache”, screen of “Selection Criteria for Shared Cursor Cache” would show up as below
Figure 1 ST04 DBA history – Selection Criteria for Shared Cursor Cache
Figure 1 has two options for SQL stats sources – “Cursor Cache” and “DBA History”. Default option is Cursor Cache. If you click “DBA history” option, the screen would be changed to allow you to select the period. The default period is the total available period depends on a system’s data retention setting.
Figure 2 Figure 2 SAP ST04 – Default input for DBA history
If you execute DBA history with default input, it would get history stats for all SQL statements for the whole available period.
In following example, I chose first 8 days period for a specific SQL-ID 8fu8ych339swj
Figure 3 SAP ST04 – SQL DBA history for specific SQL statement
Execute above selection, I got the same SQL stats data for the SQL statement we normally see with SQL share cursor.
Figure 4 SAP ST04 – SQL history stats
Above screen clearly indicated the period which I selected and SQL stats for the SQL statement from the period.
What you can use DBA history for
In summary, it can be used to compare a SQL performance from two periods. You need to do this to find out whether a SQL performance is changed between two periods and what leads to that changes. Usage case could be performance validation for a SQL improvement or performance trouble-shooting.
ST04 DBA history is useful for performance trouble-shooting when following conditions meet:
- The transaction is spending significant more time on database side than it used to be. If database time is back to normal, than your issue would be fixed.
- Most of database time is on one or a few SQL statements
If SQL related performance issue is linked to system level change like upgrading or migration, you can compare SQL stats between pre-change and post-change to understand what leads to SQL execution time changes.
You can compare SQL stats from two periods to see possible causes – starting with Elapse Time/Exec, then Disc Read/Exec, followed by Buffer gets/Exe, Rproc/Exec, Wait time/exec etc..
I have used DBA history feature in many incidents involving both performance validation for improvement and performance incident trouble-shooting with or without system-level change. It did help me to quickly identify/confirm root cause and passed it on for resolution. I would give an example to demonstrate this later.
Sometimes, you might not find SQL history stats, this could be due to your system setting on database snap-shot capture.