SAP transaction ST04 is used to monitor database performance. It provides all kind of historical data and statistics so database performance can be evaluated and improvement opportunity from application point view or system point view can be identified. In my previous post, I have given a high-level introduction on SAP database performance monitor. In this post, I would focus on how to run SAP database performance monitor and how to navigate through various screens.
- How to start SAP database performance monitor.
How to navigate to some frequent accessed screens/functions from application performance point view
- SQL Cursor Cache
- Different “Wait” event monitors like Session monitors, Lock monitor, Latch Monitor
- Oracle work load report which includes ASH, AWR, ADDM
1. How to start SAP database performance monitor
To start SAP database performance monitor, you can either use menu path or run SAP transaction ST04 directly. Database performance monitor is a part of DBA cockpit after SAP release 7.10. When I run ST04, I actually start SAP DBA cockpit but DBA cockpit would present me with database performance monitor screen. The initial screen of ST04 with “newer” SAP release is like
Figure 1 ST04 Initial Screen – Database performance overview screen part 1
Figure 2 ST04 Initial screen – Database Performance overview screen part 2
In “older” SAP release, your initial screen of ST04 might look like the screen showed in Figure 3.
Figure 3 – ST04 Database performance Overview (Old version)
In the top of screen showed in Figure 1 and Figure 3, , Click “Reset”,ST04 would create a reset point for the data displayed in the new screen. Then you can click “Since Reset” to see latest statistical data after the reset point. You click “Since DB start”, it would show statistical data since database was started last time – that is the default display for performance overview screen when you run ST04. You can refresh your display as well. ST04 allows only one reset point – always latest reset point regardless who click the “reset” Icon.
2. How to navigate through frequent accessed screens of database performance monitor
Navigation depends on your ST04 version.
If your ST04 initial screen is similar to Figure 1, you can navigate to other ST04 statistics screens via the navigation panel. Navigation panel is highlighted in Figure-2 and expanded in Figure-1. You can click the entry and navigate to the corresponding screen. I would show navigation for some important screens based on my performance experience.
If your ST04 initial screen is similar to Figure 3, you can navigate to other ST04 statistics screens via “Detail analysis menu” screen similar to Figure 4. Click “Detail analysis menu” in figure 3, SAP ST04 would present you Figure-4 screen(below).
Figure 4 ST04 Detail Analysis Menu (old version)
There are more functions under following menu option for old ST04 version than what is listed in Figure 4(above), Please refer to Figure 5.
Figure 5 ST04old menu path
2.1 SAP ST04 – SQL Cache Monitor
To see SQL statement screen, Click the “Shared Cursor Cache” (Figure 1) or “SQL statement” (Figure 4), SAP would show you a pop-up window to allow you to specify “selection criteria” to limit output and sort display. However if you are not sure what data you should put it, you can leave it blank, SAP would report all data. You do have opportunity to filter the data later.
Figure 6 ST04 Selection Criteria for SQL cache analysis
After you enter selection criteria and click the “continue” in Figure 6 (above), you would get SQL statement screens similar to Figure 7(below).
Figure 7 ST04 SQL cache statistics screen part 1,
Scroll right, there are more columns than what is listed there. Some of them are showed here (Figure 8 below).
Figure 8 ST04 SQL cache statistics screen part 2
What do we normally do during SQL cache review, please refer to table 1.
Table 1 –ST04 Common function for SQL cache analysis
|Your goal||Button should be clicked||Comment|
|Filter display by column||Filter display based on column like Disk reads, Program name etc. You can click a column first or you can select columns for sorting from available fields after you click the filter button.|
|Sort display||Sort in Ascending or Descending in expected column. Click the column name first before the sort button is clicked.|
|Search on the screen||You can search the display based on specific value or string like FIELD in SQL statement or program name etc. to quickly identify the SQL entry which you are looking for.|
|Filter display via table||Only display SQL statement for the specific table.|
|Review SQL execution plan||Access strategy would include index, access predicates etc. Click the row first before click on the explain button.|
|Review source code||Show ABAP source code for the SQL statement. Click the expected row first.|
|Create a Reset point||This is for SQL cache. Separate from the reset point in the performance overview screen.|
|Review SQL since Reset||Show all SQL cache data since last reset.|
|Review all SQL cache||All SQL cache data since database started last time. This is the default display.|
|SQL load break down||Show SQL load at date and hour level for a sql. Click the expected row first.|
Wondering what SQL execution plan looks like, following is a sample which shows the table access path, Using primary index MAKT~0 to get ROWID then use ROWID to get corresponding data record.
Figure 9 ST04 SQL execution plan
Following is sample of SQL analysis report
Figure 10 ST04 SQL analysis report
For older version of ST04, SQL statement statistics screen has a column “Current Executions”.
2.2 SAP ST04 Database session monitor
To access database session monitor similar to Figure 11 (below), you can click “Session Monitor” listed in the Navigation panel in Figure 1 or “Oracle sessions” button in Figure 4
Figure 11 ST04 Database Session Monitor
Please refer to table 1 to understand button in Figure 11. There are more columns or more information for each listed session, you just need to scroll to the right. If there are more sessions, you can scroll down as showed in Figure 11.
You can change layout of SQL cache statistics screen. You can drag a column to a new position in ST04.
2.3 SAP ST04 Database lock monitor
Click on “Lock Monitor” showed in Navigation panel in Figure 1, you would see following “lock Monitor” screen –default tab is “Without DBA Objects” for performance reasons. If you click “With DBA OBJECTS”, table name involved in the lock like “MDVM” would be displayed.
Figure 12 ST04 Lock Monitor
“Without DBA Objects” and “With DBA Objects” show current lock contention. “DML locks” tab shows all current “locks” in the database.
In the old ST04 version, the lock monitor screen (see Figure 13 below ) has only basic information. You can access the lock monitor by clicking “Oracle Exclusive lock waits” in Figure 4.
Figure 13 ST04 lock monitor – older version
2.4 SAP ST04 Active Session History
If your system does not have “Active Session History” function, you can skip this section.
Click “Active Session History” in the navigation panel showed in Figure 1, following screen would show up to allow you to specify a period which active sessions would come from.
Figure 14 ST04 Active Session History Input screen
To select the period, you can click “Avail. Timeframe in” button in the Figure 13 to get a pop-up window showed in Figure 15 (below).
Figure 15 ST04 Active session history Time frames
Click Button “Use GV$” or “Use DBA” based on your need. Here “Use GV$” is clicked – So screen is changed to Figure 16 screen (below)
Figure 16 ST04 Active session history- with input
Now click “load data” button showed in Figure 16 (above). SAP would report active session history in a screen similar to Figure 17 (below).
Figure 17 – ST04 Active session history
You can click “Set to last hour” and then click “Load data” button in Figure 13 to see active session history in the last hour. The DBA views might be too much data to run from ST04 screen.
2.5 SAP ST04 Oracle Workload Reporting
If your system does not have DBA cockpit or you do not have this feature/entry in ST04, you can skip this section. Oracle workload Reporting includes:
- AWR report – Automatic Workload Repository.
- SQL Report
- ASH report – Active Session History.
- ADDM report – Automatic Database Diagnostic Monitor.
Those would be included as part of standard SAP delivery for SAP ABAP server release 7.10 and higher. For more details, please refer to SAP ossnote 1028068.
To access Oracle workload repository report (AWR, SQL, ASH and ADDM), you can click ” Workload Reporting” showed in Navigation panel in Figure 1. Upon click, the display is changed to screen showed in Figure 18(below).
Figure 18 ST04 Workload report Input screen
Based on report you want, you need to provide different input. Please refer to table 2.
Table 2 ST04 – Input for Oracle Performance report
|AWR DIFF report||Two periods defined by: “Begin Snapshot” and “End Snapshot” ; “Begin Snapsh. 2” and “End Snapshot 2|
|SQL report||SQL ID.|
|All other reports||One period defined by: “Begin snapshot” and “End Snapshot”.|
Figure 19 screen provides a sample of “push down” menu where you can find a list of snapshots. You need to select at least two snapshots for any report other than SQL report.
Figure 19 ST04 a list of oracle snapshots
For example, I would like to know Oracle statistics between 12:00:53 and 22:00:08 on Mar 16 2013, so I choose the
begin snapshot – “47527” and
end snapshot – “47528”.
Input is reflected in following screen, please refer to Figure 20
Figure 20 ST04 Oracle workload report input
Some reports are provided with two formats – text and HTML. You can click corresponding button to get needed report in the desired format. Here, a truncated AWR report (Figure 21) and ASH report (Figure 22) are listed:
Figure 21 ST04 Oracle Report – Part of AWR report
Figure 22 ST04 Oracle Report – Part of ASH report
3. How to navigate through frequent accessed screens of database performance monitor
It is my belief that this posts covers navigation to all frequent accessed analysis/monitor functions provided by SAP ST04. Now you might wonder how to interpret those data and how we can use that to improve database and application performance. I would cover that in my next post.