SAP Table buffer – How is buffer state and table call statistics changed?

You can review a buffer state and call statistics data for a buffered table via SAP transaction ST10. A buffered table can have different status in different time or different status among different servers at the same time. Why is buffered table left a specific “status” and how and when it would be changed to another status? And what influences table call statistics data? Hope that SAP has comprehensive document on this but this is not a reality usually. However, we can do testing to understand those changes or call statistics. This post would share with you my testing done to understand SAP ST10 data. I would talk about

  1. Testing result – how status is changed from one to another,
  2. Testing environment, testing preparation and approach and
  3. Testing details.

1 Testing result

For easy reference, I have copied ST10 table call statistics overview screen header here:

Figure 1 ST10 statistics screen header

1.1 SAP ST10 table buffer state change

If a fully buffered table is changed while it is in “Valid” status, the buffer status would be changed in the following sequence:

Valid -> pending -> loadable -> valid.

When a SAP system has two or more instances, the timing on when buffer state is changed differs from server/instance to server/instance. Valid -> pending status change is done immediately after the change (commit or not commit does not matter) on the server where change is executed. Meanwhile, the status change from “valid” to “pending” on other servers/instances of the system is subject to delay which depends on buffer synchronization parameters setting and times taken by synchronization. Pending is changed to “loadable” after several reading accesses( how soon those reads happens would impact how soon “pending” status is changed to “loadable’ ). “loadable” status is changed to “valid” immediately upon next read access on the buffered table.

SAP Buffer synchronization are controlled by two system parameters:     

    RDISP/bufRefMode – control on or off of buffer synchronization function and

    RDISP/bufTime – control frequency of buffer synchronization.

I tried to test “valid” -> “Invalid” status change by updating data in a buffered table and introduced a long wait before the commit – but I saw “pending” status instead of “invalid” immediately after updating SQL statement is executed during the waiting period. Although SAP document mentions that changes on a buffered table would change it’s status from “valid” to “invalid” – but I have never seen a “invalid” buffer status.

If a table is read while it is in “Absent” status, the buffer status would be changed to “Valid” immediately:

Absent -> valid.

Table buffer status would change from “absent” to “valid” immediately upon next read on the table.

You might ask how a buffered table is left in an “Absent” status. In my understanding, a buffered table state could be changed to “absent” from “valid” status due to buffer swap. You can test this by loading a big “buffered table”.

1.2 SAP ST10 Table call statistics change

1.2.1 Invalidations

Each execution of SQL updating SQL statement would increase number under ” invalidation” column by 1. So number of invalidation is related to number of SQL execution like “update”, “delete” and ” insert”.

1.2.2 ABAP processor requests

Each execution of a SQL DML statement would increase total and one of direct reads, sequential reads or changes. One execution of SQL SELECT statement can return many table rows but it would be only counted 1 execution. Read table via full primary key would increase “direct reads”. Other reads on buffered table would increase “Seq. Reads”. Any update, delete or insert operation would be counted toward to data under “changes” column.

I noticed that one execution of SQL has been increased number under “Seq. read” by 2 sometimes.

1.2.3 DB activities

Let talk about Row affected first.

Read operation: When a table is in “valid” status, read data from a buffered table would not increase “Row affected”. When the table is in other status, reading data from the table would increase row affected. The increase would be equal to number of rows. Especially, when there is a need to reload the table due to swap and invalidation, number of row increased would be equal to number of entries in the buffered table.

Change operation: each time when table data is changed, number of row affected would be increased. The increase would be equal to number of rows changed.

Now let’s talk about database calls.

Read operation: One Select SQL execution can increase number of “database call” up to 3 – since one execution of SQL SELECT statement can involve 3 database operations- namely, preparing cursor, opening cursor, and fetching data. Number of calls impact is sum of times needed for preparing cursor, opening cursor and fetching data.

Change operation: Each execution of change operation would increase database call in ST10 as well.

2. Testing environment, preparation and approach

Our testing environment has two application servers. So we can see how the status is changed on the server where change happened and how the change impact the buffer status for the same table in the other server. Buffer synchronization frequency is every 2 minutes based on system setting.

2.1 Choose a “right” table for testing

“Right” table for such testing is a table which has been buffered and smalls and no other program would access the table during the testing.

SAP transaction ST10 is used to identify a fully buffered table which is not frequently accessed. If a table stays in “absent”, then it is more likely that the table is not frequent accessed. In my case, I have used SAP table A033. You can review and change a table buffer status via SE13

Table size – the table is small having only 74 entries. Loading a big table into a system can potentially impact performance of the system.

A033 is not active in our system and in fact the initial status is “absent” and stay that status for long time until my code is executed and change the status to “Valid”. During the whole testing, there is no other program or process is accessing A033 table.

 

2.2 Create small ABAP programs for the testing

Create two programs – one is to change data in the buffered table and the other is to read data from the table as when it is needed. Following is the sample code. Each change execution or read execution is able to complete in 1 seconds.

 

2.3 Testing approach

Check and adjust table status accordingly, start ST10 table call monitor, test reading operation, change operation, change operation is completed in interval between two buffer synchronization so we can confirm the relation among table data change, table buffer status change, table call statistics data and buffer synchronization.

Three type of testing has been executed:

  • read operation impact on status and table call statistics when table is not in “valid” status,
  • Read operation impact on call statistics when table is in “valid” status and
  • Change operation impact on table status and call statistics.

3 Testing details

3.1 read operation impact on status and table call statistics when table is not in “valid” status

The starting point: Fully buffered table A033 has status of “loadable” in “0” server and “pending” status in “06” server.

Figure 2 ST10 -baseline

Now execute the program to read data from the table in system “0”, you can see that status is changed to “valid” from “loadable”. At the same time, the Seq read operation is increased by 1 to 71 and number of row affected has increased from 272 to 346. The difference is 74 which is # of records in table A033 so the system is loading the whole table into application server buffer. Please refer to figure 3.

Figure 3 -1st read on the 0 server- status is changed to valid from loadable

Now, let’s check buffer state in another server/system “06”. Following snapshots – there is no difference in the two snapshots taken at 19:34 and 20:01.

Figure 4 – 1st read has no effect on remote server 06

Now we execute the read program on server “06”, The buffer status is changed to “loadable” from “pending” after one read as below. # of reads can be verified by “Seq. reads” and Rows affected as well.

Figure 5 2nd read operation change status to loadable from pending

Execute the read again, the status is changed into valid. I would like to highlight that rows affected is increased to 486 from 412. The difference is 72 which is table size as well. Up to now the table is buffered in another server as well. so the status is valid (refer to figure 6).

Figure 6 3rd read operation – change status to valid

Now table is in “valid” status for both servers of the system now.

Figure 7 – table are in “valid” status in bother servers

3.2 Read operation impact on call statistics when table is in “valid” status

Done one more read from 06 server in “valid” status – this shows # of read operation is increased to 77 from 76 but rows affected stay the same since it is reading from buffer. This is what expected since reading is from buffer.

Figure 8 read on buffered table

3.3 Change operation impact on table status and call statistics

What has been done: I executed the program twice immediately after buffer synchronization is finished. One execution would do the same updating twice – each updating would change 2 records. Total number of changes are 8 ( 2( records/per updating) x 2 ( 2 updating per program) x 2 ( program executed two times)) .

Impact on ST10 immediately after execution before buffer synchronization: Change status to “pending” from valid on the local server. # of changes is increased by 4 which is the number of execution of updating statement. Row affected is increased by 8 which is number of row updated. After the program execution, number of invalidations is increased by 4 which is the number of updating as well.

Following screen shot is taken within seconds of completing testing so no buffer synchronization has taken place.

Figure 9 update change local server to “pending”

While same time, the buffer status for the same table in another server *0 is not changed due to synchronization cycle.

Figure 10 – no change on remote server “status” before synchronization

You can use SAP report RSDBBUFF to monitor SAP buffering status Based on following figure, next synchronization would start after 20:38:53

Figure 11 Buffer synchronization monitor

After the synchronization period, the status is changed to “pending”. Invalidations has been increased by 4 but other data is not changed. Please pay attention to snap-shot timestamp.

Figure 12 remote server – status is changed to “pending”.

Bonus: When a buffered table is in “loadable” status, SAP would load the whole table into buffer during the next read access. In ST05/ST12 SQL trace, it would shows that buffer loading statement is linked to your local code as showed below.

Figure 13 buffer loading statement and their trigger

  1. Further clarification

Here, I am using a fully buffered table to demonstrate “typical” buffer status and call statistics changes to help you understand ST10 and table buffering better. This should help you understand “Multiple” status for generic key buffered table.

Please click how to run SAP ST10 and do performance analysis on table buffering for further information. SAP table call statistics data is closely linked to SAP memory/buffer configuration as well. You can use SAP ST02 to monitor and analyze SAP memory/buffer status.

 

 

 

 

 

 

 

 

 

 

 

Leave a Reply