Friday, January 27, 2012

SAP Workload Analysis - Chapter 6 R3 Table Buffering

R/3 uses several buffers that are local to the application server and hold primarily run-time data:
R/3 repository buffers
Table buffers
Program buffers
R/3 GUI buffers
R/3 roll and paging buffers
R/3 Calendar buffers
Why table buffers?
By specifying that a table is to be buffered, you can reduce the database accesses and thus improve performance.
The difference in the time required for local R/3 buffer accesses and that required for database accesses is significant and affects:
Database server load (CPU usage, size of database buffer required)
Dispatch time for R/3 dialog steps (avoids blocking R/3 work process)

Table buffering types

There are three types of table buffering:
Full buffering (“resident buffering”) : 100% of a table’s content is loaded into the buffer on the first access to any data from the table.

Generic buffering: a number of generic key fields (first n key fields) is specified when setting this option for a table. When accessing any data, all data records whose first n key fields are the same as those in the data accessed are loaded into the buffer.
A typical case of generic buffering is client-independent buffering, where the client name is the first key field. If you specify resident buffering for a client – dependent table, the ABAP dictionary automatically uses generic buffering with one key field.

Single record buffering (“partial buffering”): only single records are read from the database and loaded into the buffer.

All three table buffering types can be seen as special variants of generic buffering:
For full buffering, n = 0
For generic buffering, 0
For single record buffering, n = all key fields.
Buffer Synchronization

Two things happen when a buffered table is updated:
The update is executed on the database table.
The buffer of the local R/3 instance on application server A is either updated or contents are marked as invalid and reloaded on the next access.

An R/3 system landscape consisting of more than one R/3 instance:  one R/3 instance on application server A and one R/3 instance on application server B.

The buffer of the non-local R/3 instance (on application server B) is not updated immediately.
The updating statement is executed by the R/3 database interface (DBIF), which inserts a record into the table DDLOG indicating the change to, for example, table T001
There are two profile parameters which control synchronization between buffers and the database:
Possible values of this parameter are:
-       sendoff, exeauto: used for a central system – an R/3 system with one R/3 instance.
-       Sendon,. Exeauto: used for a distributed system.
-       used to indicate the time interval in seconds.

A non-local R/3 instance (such as application server B in the diagram) performs synchronization every one or two minutes (depending on how the relevant profile parameter is set). That is , the instance reads database table DDLOG to check whether the tables in the instance buffer have been changed in another instance. If there have been changes, the instance invalidates some or all of the buffered tables that are affected by the changes.
In time period between synchronizations, users read the old data. After data is invalidated, on the next access to that data, the data is updated from the database.

Fully buffered tables:               Any change invalidates the buffered table.
Generally buffered tables:       Changes in work area mode invalidate data with the
same generic field keys.
Single record buffered tables                          Changes in work area mode invalidate one buffered record.
In the work area mode, only one line of a table (the work area) is used to transfer data. This occurs, for occurs, for ex, when using the ABAP command: UPDATE (where was defined using command tables ).
Changes not made in work area mode invalidate the whole table. This occurs, for ex, when using the ABAP command: UPDATE where =

Transaction SE13 (Technical settings -> change) allows you to select the type of buffering (or no buffering) for a specific table. The most important question is whether a table should be buffered at all.
A table should only be buffered if the table is read frequently and if it is acceptable from an application or business point of view that changes to the data in the table are not immediately visible on all other R/3 instances.
In addition to full, partial, and general buffering, another buffering type, “installation-dependent”, is sometimes used that enables a table to be initially not buffered, but later set to one of the above buffering types in the running system. This buffering type is used if the table can be buffered from an application point of view, but for ex, the size of the table and the amount of main memory (hence the space for buffering) vary with the R/3 installation.
Normally, the programmer has set a buffering type, which can be revealed in the access profile for the table.
SQL statements by-passing the buffer

SQL statements that cannot be satisfied from the buffer, bypass the buffer and access the database.
Statements that bypass any type of buffering should be avoided when programming with the buffered tables in order to optimize performance. An exception is maintenance transactions on buffered tables, which should use an explicit SELECT…..BYPASSING BUFFER to ensure the retrieval of the most-up-to-date data directly from the database.

Which tables should be buffered?

This slide shows some of the criteria that must be met in addition to the technical criteria, in order to make a table eligible for buffering.

The following rule of thumb can be also used to determine tables that are eligible for buffering:
For a table size of less than 1 MB, there should be no more than 1 % changes per read, and there should be at least 300 sequential reads/day.
For a table size of between 1 and 5 MB, there should be no more than 0.1% changes per read and there should be at least 1000 sequential reads/day.

Monitoring R/3 table buffering
Roadmap 1

This roadmap shows you how to leads you to find problems with table buffering problems:
1) Display the Workload Monitor (Transaction ST03) for task types Dialog and Update
2) For both respective task types, choose Transaction profile and sort according to response time
3) For the programs or transactions that have a large response time, look at the adjacent columns to find out whether they have a lengthy CPU time or a lengthy database time.
4) For transactions with database time > 40% (response time – wait time):
Analyze single records by Viewing statistical records using Transaction STAD.
5) To analyze statistical records with a lengthy database time, double-click the appropriate line.
6) To find out whether a buffer reload occurred in the corresponding dialog step, choose ‘%_and
check for the note Tables were saved in the table buffer.
7) If this note occurs for many statistical records, it indicates frequent buffer reloads.
The table buffer should be analyzed using the Table Call Statistics monitor (Transaction ST10).

To call transaction Table Call Statistics, use Transaction ST10  or, from the R/3 initial screen choose:
Tools -> Adminsitration -> Monitor -> Performance -> Setup/buffers -> Calls.

This roadmap helps you to decide which tables should be buffered.
1) Cll Transaction ST10, Select all tables since startup,  and this server. Then choose Show
Statistics. The screen Performance analysis:Table call statistics is displayed, and contains a list
of tables and the related statistics. To sort the list by the figures in a particular column, place the
cursor anywhere in the column and choose Sort.
2) Sort the list by ,Buffer size (bytes) .
For the tables at the top of the list, consider the buffering rules and decide whether these tables
should be unbuffered.
3) Choose Next view and sort the list by Buffer size [bytes].
For the tables at the top of the list, consider the buffering rules and decide whether these tables
should be unbuffered.
4) Sort the list by column Rows affected under DB activity
For the tables at the top of the list, consider the buffering rules and decide whether these tables
should be unbuffered.
5) Sort the list by column Total under ABAP processor requests.
For the unbuffered tables at the top of the list, consider the buffering rules and decide whether
these tables should be buffered.

No comments: