An Enhanced Workload Statistics Collector for large customer systems - Part I

[Former Member]

This blog is about the Workload Statistics Collector for releases up to NW2004.

Motivation

The Workload Statistics Collector collects single statistical records from all instances of a systems and creates the profiles visible in ST03N. From a technical point of view, these statistics can be classified into two types:

1. Daily statistics for a single instance. These profiles are built using the single statistical records.
2. Weekly and monthly statistics for a single instance and daily, weekly and monthly statistics for the systemwide "TOTAL". These profiles can be built using the daily statistics of each single instance.

The number of single statistical records for one day for a single instance may be quite large, but the Workload Statistics Collector is able to handle that. The amount of memory used by it in this case is limited. On the other hand, when building up the weekly and monthly statistics, memory consumption grows, especially when creating the monthly statistics for the systemwide "TOTAL".

In some cases (e.g. lots of instances) the Workload Statistics Collector fails to create those statistics due to memory shortage and aborts with a TSV_TNEW_PAGE_ALLOC_FAILED short dump (or another short dump indicating memory shortage).

Note: If you never saw such a short dump in your system, this blog is purely informational for you, no need to change anything in your system.

SAP offers an enhanced version of the Workload Statistics Collector to resolve these problems, it is described in note 945279 (and some more notes attached to that one). This enhanced version is an additional piece of software, no changes at existing coding are necessary.

How does the enhanced Workload Statistics Collector work? Where's the difference?

Let's have a look at the two types of statistics: The daily statistics per instance built from the single statistical records do not take up so much memory that it can be a problem, but the weekly and especially the monthly statistics built from the daily statistics may do so. Therefore the enhanced Workload Statistics Collector only covers the second type of statistics. The daily, instance specific staticstics are still built up by the standard Workload Statistics Collector.

To build up weekly or monthly statistics, the standard collector loads the profiles from the database, does the aggregation and stores them back into the database. So far, so good. But it loads all profiles together into main memory; transaction profile, time profile, memory profile, RFC profiles, ... All profiles you can see in ST03N for a given time period are loaded together into main memory for aggregation. This may lead to memory shortage if the profiles are large.

The enhanced Workload Statistics Collector works different. It loads one profile, does the aggregation, stores the result back to the database, frees the memory and then loads the next profile. So it will never keep all the profiles mentioned above together in main memory.

Unfortunately, all profiles are stored together in the database in one cluster, so when touching such a cluster, all profiles are touched.

To alleviate that, the first step is to load all the profiles for one instance for one day (created by the standard collector) into the memory and then save one profile after the other into a new database table, not creating one big cluster but many smaller ones -- the transaction profile gets its own cluster, so does the memory profile, each RFC profile (there are four different types of RFC profiles), the time profile, ... you get the point.

The next step is the aggregation, but instead of loading the big cluster containing all profiles, we now can handle each profile separately, built the weekly or monthly statistics, save the profile back to the database and free the main memory before continuing with the next profile.

Next challenge: ST03N does not know anything about the new database table where all the profiles are located separately. It still looks at the well-known MONI table. So the third step is the re-combination of the single profiles (but now for weekly or monthly statistics!) into one big chunk and save that into the MONI table.

Step four is merely cleaning up, deleting all pieces (profiles) that are not used anymore for further aggregation.

How to activate the enhanced Workload Statistics Collector for large systems

To activate the enhanced Workload Statistics Collector, you first have to deactivate the standard one - but not completely! It still has to built the daily statistics per instance. Just go to ST03N --> Switch to "Expert mode" --> Open "Collector and performance DB" --> Open "Workload collector" --> Double-click "Collector & Reorganization".

Within the next screen, disable the flags for "Cumulate statistics for period 'Week'", "Cumulate statistics for period 'Month'" and "Cumulate server statistics for total system-wide stats".

...

Finally, you can schedule a background job with program RSTOTL00 as only step. Let it run and finish once before scheduling it as a daily job, because the initial run may take a long time to process. The enhanced collector will process all available daily statistics from all instances in its first run and two collectors should not run at the same time.

Remarks

When switching to the enhanced Workload Statistics Collector, initially it takes all available daily instance statistics to build weekly and monthly statistics. Afterwards, it copies these statistics back to MONI, possibly overwriting already existing statistics. This means, that some weekly and monthly statistics may be incomplete if not all necessary daily data is available. Example: Per default, daily statistics are kept for fourteen days. Given that you switch to the enhanced Workload Statistics Collector on July, 7th, daily statististics starting from June, 23rd are available. After the initial run, the monthly statistics for June will only contain data starting from June, 23rd. June, 1st up to June, 22nd will be lost. This is caused by the different algorithm the enhanced collector uses. On the other hand, if the standard collector is not working properly anymore due to memory shortage, monthly statistics probably will be incomplete anyway.

The enhanced Workload Statistics Collector can handle amounts of statistical data that are a lot larger than the standard Workload Statistics Collector can handle. However, it cannot handle everything. Quite often, the four types of RFC profiles are getting really big, causing memory problems even with the enhanced collector. The RFC profiles contain about twelve key fields and five data fields. By omitting some of the key fields, the size of the RFC profiles can be reduced. The enhanced collector offers this feature, including an analysis tool to find out, how much memory can be saved when omitting which key fields. I will shed more light on this in the An Enhanced Workload Statistics Collector for large customer systems - Part II.

The enhanced Workload Statistics Collector trades off memory usage against performance. Due to the algorithm, database load may be higher than it would be with the standard collector and more redo logs may be created. If the standard collector does its job without problems, don't switch to the enhanced collector.

The enhanced Workload Statistics Collector maintains several tables to record its progress. First, there is the log which can be viewed with program RSTOTL05. Second, table SAPWLMTOC1 contains entries for the daily statistics including flags if they have been splitted into SAPWLMONI, if they have been aggregated to weekly or monthly statistics and how many seconds the aggregation needed. You can view this table with transaction SE16 and watch the flags changing while the enhanced collector is running. Third, table SAPWLMTOC2 contains entries for weekly and monthly statistics, including flags if the statistics have been changed by the collector (then it's necessary to put the data back to table MONI) or if the statistics are complete. This is all quite technical information, but if you want to watch the collector doing its work, this might be interesting.

SAP Notes to look at

0945279 Workload Collector terminates due to memory problems
0955618 Workload Collector: Increased aggregation of RFC profile
1030486 Workload Collector: Increased aggregation of RFC profile II
1036771 Workload Collector: Analyzing & compressing RFC profiles

ABAP programs used for the enhanced Workload Statistics Collector

RSTOTL00 This program is intended to be scheduled as periodic (daily) background job and executes RSTOTL10..40 in the appropriate order.
RSTOTL10 This program is splitting up the clusters from table MONI containing all profiles into single clusters in table SAPWLMONI containing only one profile each.
RSTOTL20 This program is doing the aggregation of all profiles into weekly and monthly statistics per instance and daily, weekly and monthly statistics for the systemwide TOTAL.
RSTOTL30 This program is re-combining the clusters from table SAPWLMONI containing one profile each into one large cluster in table MONI containing all profiles.
RSTOTL40 This program is cleaning up (deleting) profiles from table SAPWLMONI which are not needed for further aggregation work anymore.
RSTOTL05 This program can display the (verbose) log of programs RSTOTL10..40.
RSTOTL45 This program can analyze the existing RFC profiles in table SAPWLMONI to help with a decision which key fields should be omitted to reduce the size of the RFC profiles. It additionally can re-aggregate the existing RFC profiles in SAPWLMONI to actually reduce their size.