SE11 Buffereing

[Former Member]

Hi All,

I have a doubt regarding technical settings of a Transparent Table.

It has an option for Buffering....

What does BUffering Allowed But Switched Off signifies??

Regards,

Ashish

[Former Member]

Buffering

The SAP System ensures that data transfer between the SAP System and the database system is as efficient as possible. To do this, it uses the following techniques:

· Table buffering. The program accesses data from the buffer of the application server.

· Database request buffering. Individual database entries are not read or passed to the database until required by an OPEN SQL statement.

When you analyze trace records, you should also examine the system's buffering mechanisms.

Table Buffering

For tables can be either partially or fully buffered (refer to Buffering Database Tables.), an OPEN SQL statement only accesses the database if the results of the statement are not already in the buffer Consequently, the SQL Trace does not contain a command or command sequence for every OPEN SQL statement. On the other hand, every SQL statement in the trace file has been sent to the database and executed.

Buffering of Database Requests

To keep the number of runtime-consuming PREPARE calls small, each an application's work processes hold a certain number of already translated SQL statements in a special buffer. By default, a process holds up to 250 statements.

If the system must execute a specific OPEN SQL, the system first checks whether this statement is stored in the "Statement cache". If the statement is in the cache, the system executes it immediately using a REOPEN (SELECT) or a REEXEC (INSERT, UPDATE, DELETE).

If the statement is not buffered, a PREPARE operation prepares it for the subsequent OPEN/EXEC. The system administers the buffer according to the LRU algorithm ("least recently used"). When space is needed for new statements, the statements that are rarely used are deleted. As a result of the LRU algorithm, the statement must prepare frequently used statements usually only once.

An application server buffers the DECLARE, PREPARE, OPEN, and EXEC requests within the cursor cache of one work process. As a result, once the system opens a cursor for a DECLARE operation, it can use this cursor over and over again within the same work process.

Buffering Database Tables

Buffering a table improves the performance when accessing the data records contained in the table.

The table buffers reside locally on each application server in the system. The data of buffered tables can thus be accessed directly from the buffer of the application server. This avoids the time-consuming process of accessing the database.

Buffering is particularly important in client/server environments, as it takes considerably longer to access a table with the network than it does to access a table that is buffered locally. Depending on the network load, this factor can lie between 10 and 100.

The difference in performance is somewhat less marked in central systems (systems with only one application server) than in local ones (systems with several application servers). However, even in central systems, a reduction in process changes and increased sophistication of the buffering over that provided by the database system have a noticeable effect on performance.

How are the Buffers Filled?

If a program accesses the data of a buffered table, the database interface determines whether this data is in the buffer of the application server. If this is the case, the data is read directly from the buffer. If the data is not in the buffer of the application server, it is read from the database and loaded into the buffer. The next access to this data can then use the buffer.

The buffering type determines which records are loaded into the buffer during an access.

How are the Local Buffers Synchronized?

A buffered table is generally read on all application servers and held in the buffer there. If a program changes the data contained in the table on an application server, this is noted in the log table by the database interface. The buffers still have the old status on all the other application servers, so that the programs might read obsolete data.

A synchronization mechanism runs at a fixed time interval, usually every 1-2 minutes. The log table is read and the buffer contents that were changed by other servers are invalidated. In the next access, the data of invalidated tables is read directly from the database and updated in the buffer.

Displacement

If more space is required in the buffer due to new data, the data that has not been accessed for the longest time is displaced. The data is displaced asynchronously at certain times that are defined dynamically by the buffer accesses. The data is only displaced if at this time the free space in the buffer is less than a given value or if the access quality is not good enough.

Resetting the Table Buffers

You can reset the table buffers on the corresponding application servers by entering $TAB in the command field. All the data in the buffer is invalidated.

Only use this command if inconsistencies occurred in the buffer! It can take several hours to fill the buffers in large systems. Performance is considerably reduced during this time.

See also:

Local Buffer Synchronization

Which Tables Should be Buffered?

How are Table Buffers Implemented Technically?

Which Accesses Proceed Directly to the Database?

How can you Analyze the Buffer Quality?

Generic Buffering

With generic buffering, all the records in the buffer whose generic key fields match this record are loaded when one record of the table is accessed. The generic key is a part of the primary key of the table that is left-justified.

In this example, the record highlighted in red is read by a program from table SCOUNTER. If the table is generically buffered, all the records read whose generic key fields (MANDT and CARRID) agree are loaded into the buffer.

When Should you Use Full Buffering?

A table should be buffered generically if only certain generic areas of the table are normally needed for processing.

Client-specific, fully-buffered tables are automatically generically buffered since normally it is not possible to work in all clients at the same time on an application server. The client field is the generic key.

Language-specific tables are another example where generic buffering is recommended. In general, only records of one language will be needed on an application server. In this case, the generic key includes all the key fields up to and including the language field.

How Should you Define the Generic Key?

In generic buffering, it is crucial to define a suitable generic key.

If the generic key is too small, the buffer will contain a few very large areas. During access, too much data might be loaded in the buffer.

If the generic key is too large, the buffer might contain too many small generic areas. These can reduce buffer performance since there is an administrative entry for every buffered generic area. It is also possible that too many accesses will bypass the buffer and go directly to the database, since they do not fully define the generic key of the table. If there are only a few records in each generic area, it is usually better to fully buffer the table.

Only 64 bytes of the generic key are used. You can specify a longer generic key, but the part of the key exceeding 64 bytes is not used to create the generic areas.

Access to Buffered Data

It only makes sense to generically buffer a table if the table is accessed with fully-specified generic key fields. If a field of the generic key is not assigned a value in a SELECT statement, it is read directly from the database, bypassing the buffer.

If you access a generic area that is not in the buffer with a fully-specified generic key, you will access the database to load the area. If the table does not contain any records in the specified area ("No record found"), this area in the buffer is marked as non-existent. It is not necessary to access the database if this area is needed again.

[Former Member]

Hi,

enabling this options, it will keep the last processed records (of its table) in the memory buffer. next time, if u try to access the same record, it will be fetched from memory buffer rather than the database.

Regards,

Niyaz

[Former Member]

The situation you describe will occur when a table meets the basic technical conditions for buffering, but the decision has been made not to buffer the file, usually for performance reasons (for example, the table may be too large, or the contents may change too frequently).

The following gives a good deal of information on buffering, and how it relates to performance related issues:

http://www.sap-press.de/download/dateien/259/sap_press_pog_007.pdf

The linked PDF explains what these technical conditions are, and what considerations go into making that decision, including procedures for ongoing monitoring of the system to determine if the buffering status of a particular file should be changed

The buffering status specifies whether or not a table may be buffered.

This depends on how the table is used, for example on the expected volume of data in the table or on the type of access to a table. (mainly read or mainly write access to the table. In the latter case, for example, one would not select buffering).

You should therefore select

- Buffering not allowed if a table may not be buffered.

- Buffering allowed but not activated if buffering is principally allowed for a table, but at the moment no buffering should be active. The buffering type specified in this case is only a suggestion.

- Buffering allowed if the table should be buffered. In this case a buffering type must be specified.

[Former Member]

Hi Ashish,

Go thorough below threads...

<b>Reward points if it is useful..</b>

Thanks & Regards

ilesh 24x7

[Former Member]

Hi,

Buffering Allowed but Not Activated: Buffering allowed but not activated if buffering is principally allowed for a table, but at the moment no buffering should be active. The buffering type specified in this case is only a suggestion.

Regards,

Ruthra