How to run SAP transaction ST04 – database performance monitor _ SAP application performance

How to run SAP transaction ST04 – database performance monitor | SA...

1 of 15

http://www.sap-perf.ca/run-sap-st04/

SAP application performance

How to run SAP transaction ST04 – database performance monitor Posted on Sunday April 14th, 2013 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. 1. How to start SAP database performance monitor. 2. How to navigate to some frequent accessed screens/functions from application performance point view A. SQL Cursor Cache B. Different “Wait” event monitors like Session monitors, Lock monitor, Latch Monitor C. 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

05-Feb-14 5:56 PM


2 of 15


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.

05-Feb-14 5:56 PM


3 of 15


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

05-Feb-14 5:56 PM


4 of 15


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.

05-Feb-14 5:56 PM


5 of 15


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.

05-Feb-14 5:56 PM


6 of 15


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

05-Feb-14 5:56 PM


7 of 15


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

Comment

clicked

Filter display by

Filter display based on column like Disk

column

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

You can search the display based on specific

screen

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

Only display SQL statement for the specific

table

table.

Review SQL

Access strategy would include index, access

execution plan

predicates etc. Click the row first before click on the explain button.

Review source

Show ABAP source code for the SQL

code

statement. Click the expected row first.

Create a Reset

This is for SQL cache. Separate from the

point

reset point in the performance overview screen.

Review SQL since

Show all SQL cache data since last reset.

Reset

05-Feb-14 5:56 PM


8 of 15


Review all SQL

All SQL cache data since database started

cache

last time. This is the default display.

SQL load break

Show SQL load at date and hour level for a

down

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

05-Feb-14 5:56 PM


9 of 15


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.

05-Feb-14 5:56 PM


10 of 15


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.

05-Feb-14 5:56 PM


11 of 15


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

05-Feb-14 5:56 PM


12 of 15


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

05-Feb-14 5:56 PM


13 of 15


Based on report you want, you need to provide different input. Please refer to table 2. Table 2 ST04 – Input for Oracle Performance report

You need

Input

AWR DIFF

Two periods defined by: “Begin Snapshot” and “End Snapshot” ; “Begin

report

Snapsh. 2 and “End Snapshot 2

SQL report

SQL ID.

All other

One period defined by: “Begin snapshot” and “End Snapshot”.

reports

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

05-Feb-14 5:56 PM


14 of 15


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

05-Feb-14 5:56 PM


15 of 15


we can use that to improve database and application performance. I would cover that in my next post. This entry was posted in SAP transaction and tagged how to run SAP transaction ST04; SAP ST04; SAP ST04 navigation; SAP database performance monitor by eric. Bookmark the permalink [http://www.sap-perf.ca/run-sap-st04/] .

05-Feb-14 5:56 PM

How to run SAP transaction ST04 – database performance monitor _ SAP application performance

Recommend Documents