How to understand SAP ST04 information and use it for performance analysis

Posted on
My previous posts give an introduction on SAP database performance monitor and talk about how to run SAP transaction ST04 and navigate to frequent accessed ST04 screens. This post would choose several frequent referenced ST04 screens and talk about
  1. How to understand the information in ST04 screens.
  2. How to use ST04 information from those screens to do performance tuning.
This post is based on experiences on the SAP system I am working with. Before I go down to the details, I would like to highlight that it is important to get yourself familiar with the normal status of database performance and establish database performance baselines which can be used for comparison for future performance issue. In general, database performance can be improved via application/SQL code tuning, database itself tuning and OS-level tuning. Often, system and application performance can be improved via application/SQL code tuning. Poor database performance is normally an outcome of inappropriate application/SQL design other than database configuration and Server setting.

1 SAP database performance overview

1.1 The database performance overview screen


Figure 1 ST04 database performance overview

1.2 Understand the information in ST04 database performance overview screen

Table 1 Explanation of ST04 database overview screen

Data buffer It is a memory area to store database data/blocks so future access on the same data can be fulfilled from memory. Accessing data in memory is much faster than accessing data in the disk.Goal of data buffer is to reduce physical I/O as much as possible.
Field Size displays configured memory size.Quality indicates how often would a requested data is found in memory without disk access. Quality = number of memory reads/reads x 100%. Quality is also known as hit ratio.
Reads displays the sum of number of disk access and memory access since start of database server.Physical reads: Total number of disk access.
Buffer Busy wait: Total number of buffer busy wait. This wait happens when a session wants to access a database block in memory but it cannot because the buffer is busy. This is related to concurrent accessing on the same database table.
Shared Pool It is a memory area to store executable SQL version of SQL codes and Database objects.Goal of Shared Pool is to promote reusing of parsed SQL and avoid disc access for database objects.
DD-Cache Quality indicates how often would a requested data object is found in cache. The Data Dictionary cache stores information on objects in the Oracle database, such as their names, definition and access to them.
SQL Area GetRatio and PinRatio Indicates how often parsed SQL statements are found in SQL Area of the shared pool. The Shared SQL Area (also called as Shared Cursor Cache) stores the parsed SQL statements (parsing means processing SQL statements to derive an access path to the data within the database).
Log buffer It is a circular memory area to store every database change before they are written to log files. Every database change would create one “redo” entry in log buffer.Changes in log buffer can be written to a redo log file before the corresponding transaction is committed. If the redo log buffer fills, or another transaction commits, all entries in the log buffer would be written to a redo log file by Oracle LGWR process, even though some redo records may not be committed. If necessary, the database can roll back these changes. 
The log buffer is flushed to persistent storage based on Oracle design:

o every 3 seconds

o every commit

o when 1/3 full

o when 1 meg full
Size field shows memory size for the log bufferEntries shows total number of redo entry since database was started
 Allocation Retries & Allocation fault rate show the number of failed attempts to allocate free space in the redo log buffer and the ratio.
Redo Log wait & Log files display wait situation and number of log files used for recovery. log file sync waits are a wait on lgwr process to complete a write to disk.
Call Display important statistics on database operation.
Calls displays the total number of user calls in the database since it was started.Commits displays the total number of committed transactions since the database was started.Rollbacks
displays the number of rolled back transactions since the database was started.Recursive calls
displays the number of recursive calls.Recursive calls/user calls display total number of recursive calls and user calls. SAP mentions total number of recursive calls should be smaller than the number of user calls by a factor of 5 in a productive environment.
Parses displays the total number of parsed SQL statements. SAP mentions the rate of Parses to User Calls should be under 25%.
Reads / User calls displays the number of Oracle blocks read on average from the data buffer to answer user queries. If this number is greater than 30, this points to “expensive” SQL statements.
Time Statistics Display summary of database wait situation. Wait itself would not consume resources but a result of resource contention. Wait situation should be reviewed and mitigated whenever possible.
Busy wait time shows the cumulative time that is used up, because the database system had to wait for a resource that was not available at the time of the request. If this value is high, you must perform a detailed analysis via Oracle wait event analysis.CPU time:
total CPU time used since database was started. CPU usage:
average CPU usage since database was started.
Redo Logging Information here is related to redo log output performance.
Table scans & Fetches Display summary of sequential read as well as total number related to chained row/continued row or read via index.From performance point view, we need to avoid sequential read on long tables
Short tables field displays the total number of sequential read operations on small tables ( tables with less than 5 Oracle data blocks ) since database was started.
Long tables field displays the number of sequential read operations on large tables ( tables with 5 or more Oracle data blocks ) since database was started. Fetch By rowid
fields displays total number of table rows chosen by index or by a unique ID (row ID, ROWID) in the SQL statement since database was started.

Continued Row displays the number of chained Rows fetched since database was started.
Sorts Displays the total number of sort operations performed in the main memory and on the disk.
The Sorts section displays the total number of sort operations performed in the main memory and on the disk.
Memory displays the number of sorts in the main memory. These sorting processes are normally faster than sorting processes on the disk.
Disk displays the number of sorting processes temporarily written to the tablespace PSAPTEMP.

Buffer busy wait: You can refer to Oracle online document for more information on buffer busy wait. It mentions three main causes for buffer busy wait: accessing a data block which is under changes; insert into the same block at the same time; concurrent reads on the same physical block.
Recursive call: following is excerpt from Oracle document on Recursive calls
Sometimes, to execute a SQL statement issued by a user, the Oracle Server must issue additional statements. Such statements are called recursive calls or recursive SQL statements. For example, if you insert a row into a table that does not have enough space to hold that row, the Oracle Server makes recursive calls to allocate the space dynamically if dictionary managed table spaces are being used.
Recursive calls are also generated:

  • When data dictionary information is not available in the data dictionary cache and must be retrieved from disk
  • In the firing of database triggers
  • In the execution of DDL statements
  • In the execution of SQL statements within stored procedures, functions, packages and anonymous PL/SQL blocks
  • In the enforcement of referential integrity constraints
In a standard SAP environment, Recursive calls if there is any are transparent to SAP developers in my view.

1.3 Performance analysis via ST04 database performance overview screen

Information from ST04 performance overview screen cannot lead to a conclusion or solution and is servicing a hint/direction for further investigation in most cases.
Table 2 Analysis via ST04 database performance overview

Data buffer It is generally recommended that buffer quality/hit ratio should be greater than 95%. To improve the hit ratio, you need to analyze SQL cache to tune expensive SQLs and/or increase data buffer.However high ratio could be inflated by a very frequent executed SQL which get data from buffer. For example, Database has 3 tables which is the same size but buffer can hold one table at one time. If one table is accessed 100 times, the remaining two tables are accessed 1 time, the hit ratio might be much higher than each table is accessed 34 times. If the number of wait situations is over 5% of reads, you must perform an extensive analysis of the database; wait situations may also occur due to a data buffer that is too small so if catch hit rate is low at the same time, you might need to increase the data buffer first.
Shared Pool Dictionary cache quality above 98% in a production system.
The value SQL Area get/pinratio should be above 99% in a production system.If not, then you should consider to increase Shared Pool size.
Log buffer If number of failed attempts is higher, you need to review the LOG_BUFFER_WAIT event further. Possible mitigate action is to increase redo log buffer and/or tune IO system.
Calls Sap mentions that the number of recursive calls should be smaller than the number of user calls by a factor of 5 in a productive environment. The rate of Parses to User Calls should be under 25%.The value Reads / User calls displays the number of Oracle blocks read on average from the data buffer to answer user queries. If this number is greater than 30, this points to “expensive” SQL statements.You need to analyze SQL cache to tune expensive SQL.
Time Statistics Busy wait time shows the cumulative time that is used up, because the database system had to wait for a resource that was not available at the time of the request. If this value is high, you must perform a detailed analysis using wait event analysis and CPU load analysis on database server. CPU usage 20% or 30% itself is not high. However usage from 20% to 30% represent a 50% changes, this could be significant. Detail investigation is needed to see what leads to this “dramatic” change and possible impact.
Redo Logging This area might be related to log output performance. Whether there is a performance concern, we need to refer to database (Oracle) wait event analysis.
Table scans & Fetches If fetches by continued row is high, then you need to analyze index/tables fragmentation situation and consider index/table reorganization. For short table, sequential read might be better than index read. For long table, reading via index read is generally better than sequential read. If huge number of records (like 20%) is needed, then sequential read might be better bet from performance point view. Further analysis on sequential read needed to be done via SQL cache analysis.
Sort Ideally, sort should be in memory from performance point view. SAP mentions that the ratio of disk to main memory should be under 5%. If this is not the case, increase the Oracle parameter sort_area_size

You can reference to SAP document for some threshold values mentioned in table 2. You can use reset point to see how statistics are changed after reset in comparison with information since database was started.

2 SAP ST04 – SQL Cache Monitor screen

2.1 SAP ST04 – SQL cache monitor screen


Figure 2 ST04 SQL cache monitor screen part 1


Figure 3 ST04 SQL cache monitor screen part 2


Figure 4 ST04 SQL execution plan

Following is sample of SQL analysis report

Figure 5 ST04 SQL analysis report

2.2 Understand the information in ST04 SQL cache monitor screens

All information in ST04 SQL cache monitor screen is a snapshot taken at the time of running SQL cache monitor since database was started or latest reset.
Table 3 Explanation of ST04 SQL cache monitor screen

Field Explanation Field Explanation
Executions Total number of times which a SQL statement is executed. Elapsed Time Accumulated runtime for all execution of a SQL.
Disk reads Number of disc blocks reads. Elapse Time/Exec Average run time per SQL execution.
Reads/Exec Average number of disc reads per SQL execution. CPU Time Total CPU time consumed for all execution of a SQL.
Buffer gets Total number of buffer read. CPU time/Execution Average CPU time per SQL execution.
Bgets/Exec Average number of buffer read per SQL execution. Wait time Total wait time occurs during the SQL execution.
Proc. Rows Total number of rows processed/returned. SQL statement SQL statement in the cache. SQL statement in lower case is related to system operation.
Rproc/Exec Average number of rows per SQL execution. Program name Program name who issues a SQL statement.
There are more screen fields than what listed in table 3 like fields related to memory usage etc.
Figure 4 SQL execution plan screen tells you the access strategy used by database to fulfill the SQL statement. Following are corresponding field explanation in Figure 4.    
Table 4 Explanation of ST04 SQL execution plan screen

Field Explanation Field Explanation
Estimated Costs This number is an estimate of the number of disk I/Os and the amount of CPU and memory used in executing the SQL. Higher the number is, more expensive the access is. Search Column Number of field used in accessing index.
Estimated Rows Projection on number of rows returned by the SQL Access Predicates A list of fields used in accessing index.
Sequence number 1, 2 etc. The sequence of operation chosen by oracle for the SQL. Filter Predicates A list of fields used in filter records

Oracle CBO always choose a plan with lowest cost based on calculation. However the cost calculation can be influenced by many factors. One of factors is table statistics so obsoleted table statistics can lead to unreliable cost which leads to improper SQL execution plan.
I have not mentioned column selectivity and index cluster factor screen which can be brought up if you double click on the index in execution plan. You might need to review this information.
You can refer to table 3 to understand the information in SQL analysis report screen (figure 5).In some cases, load distribution can help to understand the performance change of a transaction or business application.

2.3 SQL Performance tuning via ST04 SQL cache screens

Most of database performance issues are related to application design (code and table/index design), which needs application owner to fix it. If issue cannot be fixed via code/application design change, then it might need database level changes like statistics updating and table/index reorganization which is normally owned by Basis/database guy. It seldom happens that we change a database configuration design to fix a performance issue. Database configuration changes can include IO design like table distribution, data striping etc. to mitigate hotspot for concurrent accesses. ST06 can be used to identify hot disc based on utilization.
SAP ST04 cache screen contains a lot of statistics data like number of execution, disc read etc. as you have already seen. Sorting on those statistics in descending, most expensive SQL statement would be at top of the list so you can review those top SQL statements to identify tuning opportunity. Tuning expensive SQL code can improve underlying application performance, sometimes, tuning expensive SQL can have big improvement on overall database performance if total physical read and logical reads is reduced. For example, reducing physical read can reduce demand on buffer so database would needs less buffer space to execute a SQL statement, this means less existing data is moved out of buffer.

2.3.1 SQL cache analysis

SQL cache analysis is a critical performance tuning area for database and application performance. Following is a list of performance analysis based on SQL cache review.
Table 5 SQL performance analysis via ST04 SQL cache

Tuning category Scope Approach
Tune
SQL disc read		 Focus on top offenders based on “disc read” and disc “Reads/exec“.Objective is to reduce SQL disc read. Disc IO can be reduced via appropriate index, reducing SQL execution, using alternative table, changing SQL where-clause, or changing table/index etc. Combining similar jobs can help reduce Disc IO read.
Tune SQL memory read Focus on top offenders based on “buffer gets” and “Bgets/Exec” is to reduce buffer read. Similar to disc read. Combining similar job steps would help more to reduce buffer read.
Tune SQL response time Focus on top offenders based on “Elapsed time” and “Elapsed time/Exec“Objective is to make SQL run faster when possible. Response time can be reduced via appropriate index, reducing SQL execution, using alternative table, changing SQL where-clause, changing table/index etc. Moving operation like “sort” to application server and using parallel solution etc. can help as well if applicable.
Tune SQL CPU time Focus on top offenders based on “CPU time” and “CPU time/Exec“.Objective is to reduce CPU load especially when contention CPU time can be reduced via appropriate index, reducing SQL execution, using alternative table or changing SQL where-clause etc. moving operation like “sort” to application server can help as well.
Tune SQL Row processed Focus on top offenders based on “Proc. Rows“ and “Rproc/Exec “. Objective is to reduce rows traffic between database server and application server. Row processed can be reduced via reducing SQL execution, consolidating similar SQL statements, using alternative table, do aggregation operation on database server side and adding more filters to the selection etc.

Approach in table 5 mentioned is mainly from application solution point view. There might be database level changes which can be done to improve performance like index/table reorganization, table statistics updating(including histogram) and index/table sorting, table/index compression, other oracle memory/storage parameter changes, disc stripping etc. which might be needed based on storage analysis and wait event analysis.
There are overlapped among different categories. For example, one SQL can show up as Top expensive SQL under response time and Top expensive SQL under Disc IO as well.
Here, I mentioned to combine similar jobs and/or job steps to reduce disc IO, buffer gets. However there are other factors we need to consider before we consolidate jobs or job steps. For example, memory utilization, business performance goal as well as relation between program performance and volume.
SQL tuning is focusing on “select” SQL statement other than “insert” statement in most cases. There is really no way for you to reduce number of rows and number of insertion or control which table should be inserted into in a SAP system. But for “select” statement, this is tricky since the same information usually shows up in different SAP tables, many records can meet one selection criteria as well as SQL where-clause design can influence oracle’s table access strategy.
When there is a database performance issue, you can use reset point in SQL cache monitor to see what is most expensive SQL for a problematic period after reset, you might find this helpful sometimes.

2.3.2 SQL Execution plan review

SQL is either getting data from base table or from the index table. When it read data from table, it either access table via index or without index. When you analyze SQL execution plan, please pay attention to following points:
  • Is full table scan appropriate?
    For small tables or occasional access, full table is better than index read. Index is limited resources and has additional cost.
  • Is the right index used to read the table?
    Check index used against with what is specified in SQL where-clause: If no index field is referred in SQL where-clause, then it is not correct for the system to use the index to read the table. In this case, a “Full” table scan is more efficient than index read.

    You also need to check whether there is a better index for the SQL statement – this can happens when fields in where-clause show up in several indexes of a table. If Oracle chooses a wrong index for a SQL execution, you can correct this via statistics updating or Oracle hints. Oracle hints needs to change related program code.
  • Is right/enough column of the index used to read the table?
    Check to see whether all available fields from where-clause are used to access index.
  • Is right/enough column from the where-clause used to filter the read?
    Check whether fields from where-clause are correctly used to filter data read from table.
  • Is right joint operation used like nested-loop, hash joint etc.?
    Based on my experience with my system, most often seen joint operation is “nested-loop”. I did see “hash joint”. In my 7 year’s performance work, I encountered 1 or 2 cases where joint operation was the concern. If you would like to know more on joint operation, please refer to Oracle document – Understanding Joins.
ST04 SQL cache analysis would not tell you about duplicated selection. To get such information, you need to run ST05/ST12 trace on execution of related program. Then you can analyze the SQL trace. If you wonder how to do that, you can refer to my post – SAP ST12 trace SQL performance analysis.

I did encounter performance issue due to fields in “Search Column”. Those error are Oracle CBO error – could be due to Oracle bug or table/system statistics status. The solution to fix this is normally statistics updating or Oracle patches or solution change.
It is important to review table cluster factor and index column selectivity as well. You can find such information by double clicking index name in SQL execution plan screen. Lower selectivity and high cluster factor can be a reason for poor performance which can be addressed via table/index/program changes( the former ) and table/index reorganization( the latter ).
SQL analysis report is used to identify load changes over time.

3 SAP ST04 Database session monitor

You can use ST04 session monitor to understand current activities of Oracle session. This helps to understand the performance of system and applications

3.1 SAP ST04 database session monitor screen


Figure 6 ST04 database session monitor screen

3.2 Understand information in SAP ST04 session monitor screen

Following are some of field/column explanation in ST04 session monitor
Table 6 Explanation of ST04 session monitor screen

Field Explanation Field Explanation
SID Oracle Session ID Logical Reads Total number of physical read and memory read occurs under the session
Op. Sys. PID Operating system ID related to the Oracle session. Physical Reads Physical reads
Client system Related system where client process is running. Block Change Number of block changed by the session
Client Proc. Corresponding client process for the Oracle session. One Client process can create more than one oracle sessions PGA_USED_MEM Current used PGA memory by the oracle session
Status Session status PGA_MAX_MEM Max memory used by the oracle session
Event Event where current session is waiting for User Related SAP user-id which is related to Oracle session
SQL statement Corresponding SQL statement which is executing Client info Related SAP program.

3.3 Performance analysis via SAP ST04 session monitor

Table 7 performance analysis via ST04 session monitor

Performance analysis Approach
Understand process status Refresh oracle session monitor to see whether any number under logical read, physical read, block changes and consistent changes fields are changed. If no number changes between two refresh, then the oracle session is stuck such as corresponding business job or process.So this information can be used to answer whether a job or program is stuck when SM50 show “database operation” action as well how expensive the SQL is.
Check SQL execution plan and corresponding code You can check corresponding SQL execution plan for an “active” SQL to understand why a process in SM50 spends long time on “sequential read” or “direct read”. You can compare SQL source code with SQL code in the cache, this might help you to understand whether a performance issue can be addressed by executing SQL using different data.
Memory utilization You can sort the PGA_USED_MEM or PGA_MAX_MEME to see which Oracle session is using most of memory when needs arise – but I have not used this so far in my work.
Event analysis You can sort on the event column to see whether many processes are stuck on the same event and their corresponding jobs/processes. This can be used to job scheduling or user training.

4 SAP ST04 Database lock monitor

4.1 SAP ST04 database lock monitor screen

Following is a truncated ST04 lock monitor screen to help you to know what the screen looks like

Figure 7 ST04 Lock Monitor

4.2 Understand information in ST04 database lock monitor screen

Session ID and client id has been covered in session monitor. The lock type/Request mode and hold mode is standard Oracle term. What we need to pay attention to normally is “status” column/field. The row with “HOLD” value in Status column is the process which has placed locking while other process which has “_WAIT” value in “status” column are waiting for.

4.3 Performance analysis via ST04 database lock monitor screen.

Database performance monitor can be used to identify current lock contention. SAP transaction DB01 can be used to monitor database lock as well. But ST04 lock monitor screens would show root lock directly. If process A is hold a lock which process B is waiting for. At the same time, B process is holding a lock which process C is waiting for.. DB01 and ST04 lock monitor would report lock contention like:
Table 8 Display difference between SAP ST04 lock monitor and DB01

DB01 display St04 database lock monitor
Parent Child Parent Child
A   A  
  B   B
B   C
  C  

When many processes are locking each other and you would like to figure out which one is root locker… SAP ST04 lock monitor can provide a straightforward answer.

5 SAP ST04 Oracle Workload Reporting

Oracle workload Reporting includes:
  • AWR report – Automatic Workload Repository.
  • SQL Report.
  • ASH report – Active Session History report.
  • ADDM report – Automatic Database Diagnostic report.
AWR report allows you to compare oracle workload between two periods – this might be helpful if there is an issue with database and you would like to figure out what causes the performance difference. ADDM report would generate database tuning proposal.
Those are standard reports. You can refer to PDF document from Oracle company website to understand how to use those reports for performance analysis.

6 Further clarification

In addition to this post, I have other two posts related to database performance monitor – SAP database performance monitor introduction and how to run SAP database performance monitor ST04. My focus is mainly on how to tune SQL from application performance point view and general understanding database performance monitor.
It is not my intention or within my abilities to cover all areas of database tuning. There is a ton of documents on database tuning already – Wait event analysis is critical for database performance tuning, you can refer to online Oracle document Automatic Performance Statistics for further reading. You can go to click here to understand more in details on database performance tuning.

Leave a Reply