Table buffering is an important performance area of application. Buffering a table has two positive performance benefits: speed up data retrieval; move load from database server to application server and possible network traffic. Accessing data from a buffered table is many times( could be 100 times) faster than access same data from disc. Moving load away from a database server(CI) is especially important in a big SAP system where there are several application server and only one database server. From performance point view, we need to make sure three things- enable buffering on a table which should be buffered, remove buffering on a table which should not buffered and choose right buffer method for a table. We need information to make such decision. SAP transaction ST10 would provide table access information which is helpful in table buffering access. In this post, I would cover following
- How to run SAP operation system monitor and navigate through important SAP ST10 screens,
- How to understand information provided by SAP ST10 screens and
- How to use SAP ST10 for table buffering performance analysis.
1 How to run SAP table call monitor and navigate through important ST10 screens
1.1 How to start SAP table call statistics monitor
To start SAP table call statistics monitor, you can either use menu path , run sap transaction ST10 directly.
After you execute SAP transaction ST10, following screen would show up for you to specify what information you would expect from ST10.
Figure 1 Part 1 of 2 SAP ST10 selection screen
The above parameter screen allows you to specify data reporting scope in terms of tables, time frame and servers. Following is detail
Table 1 – ST10 input option
|All tables||report table access statistics for all tables defined in the system.||Since start up||All table access since the system is started/bounced last time. Default option.
Buffer status is available when this option is selected with single server option
|All servers||Table access info from All servers in the sap system. Buffer status information is not applicable for this option|
|Not buffered||report statistics on table which has no buffering. Default option.||Today/this week/this month||Table access happened in today/this week/this month||This server||The server where ST10 is kicked off. Default option.|
|Single Key buffered||ST10 would report tables where single record buffer is enabled.||Previous day/week/month||A period in the past – you would be asked to provide exact time frame.||Sever pull down menu||You can specify another server of the system via the pull down box|
|Generic Key buffered||ST10 would report table access statistics for all tables which are under generic or full buffering.|
You make your choice based on your analysis purpose. If you would like to review whether a table has been buffered correctly, you can select table option “single key buffered” and/or “Generic key buffered”. If you would like to review whether an un-buffered table should be buffered, then you can select “Not buffered” option. You can always select tables access statistics on “all tables” in the system – the only concern is that it would take longer and you have all information in one screen. I would suggest that one time focus analysis on one buffering status.
1.1.1 SAP ST10 – navigate to call statistics screen for un-buffered tables
In figure 1 screen, the options selected indicates that I would like to review table access statistics information for un-buffered table from all servers in the past month.
Figure 2 – ST10 time frame selection
I double click the month – Feb 2013 , then ST10 would report table access statistics overview information for all un-buffered tables happened from all servers in the system for period of Feb 2013
Figure 3 Table call statistics overview screen for Not buffered tables
Above is an overview screen. From the above screen, you change your display to let ST10 to display different information relate to the current server like “all tables” or “generic buffered table” by clicking corresponding button in the screen.
To see statistics details for a table, double click on the entry, or move the cursor to the expected line and click on the button “Choose”. You would see single table call statistics screen –
Figure 4- ST10 single table call statistics detail with all servers option
1.1.2 SAP ST10 – navigate to call statistics screen for generic buffered tables
If I specified “Generic key buffered” option in figure 1 , other selections are the same, you would see table call statistics screen for generic key buffered and full buffered tables:
Figure 5 – ST10 table call statics over view for generic/full buffered table
Double click on expected table name, call details for that particular table would show up
Figure 6 – ST10 table call details for a generic buffered table with all servers option
Table call details screen has two additional sections “buffer administration data” and buffer “quality” when ST10 data is from one server. State in Buffer is always the buffer status in the server when the ST10 transaction is run. Qualities is for the period selected earlier .
Figure 7 ST10 Table call details for buffered table
Following is the table call statics over view screen for single record buffered tables with “all servers” option.
1.1.2 SAP ST10 – navigate to table call statistics snap-shot screen
In figure 1, if you select “generic key buffered” or “single key buffered” with “this server” and “since system start”, this would show table buffering status in current server as well. Following is the table call statistics snap-shot screen.
Figure 8 ST10 table call statistics snap-shot screen
Buffer status information is only reported by SAP st10 transaction overview screen when “since startup” option is selected with a single server selection. You can find buffer status in single table call details screen. Apparently this column does not make much sense for historical options. A buffered table can have different status on different servers of a SAP system at the same time.
2 How to understand information provided by SAP ST10
2.1 How to understand information in SAP ST10 table call statistics overview screen
The overview screens would show call statistics for all tables specified in period and server interested.
|Table||Table name. For pooled table, it would display pool name then table name for the pooled table.|
|Buffer State||Status of the buffer ( buffered table only) :
Buffer state information is not applicable with ST10 table option – “not buffered”.
|Buf Key Opt||Table buffering type(buffered table only):
|Changes/Total (%)||Change ratio = number of changes operation / total operation X 100%.|
|Invalidations||Number of invalidations. Each SQL change operation would cause one invalidation. One change operation can change more than one record of a table.|
|Total||Sum of direct reads, sequential reads and changes operation.|
|Direct Reads||Number of Read by primary key.|
|Sequential reads||Other reads other than direct reads.|
|Changes||Number of changes. Changes include update, insert and deletion SQL operation.|
|Calls||Number of calls database received. Each SQL cursor preparation, open and fetch operation is counted as one call.|
|Row affected||Sum of following: Number of rows transported to application server from database, number of rows updated and number of rows inserted. Initial load for buffering is not counted into this.|
All above information except “Buf Key Opt” is a server dependent information except when “all servers” option is selected. In ST10 table call statistics overview screen, you can bring up the SAP online documentation on buffer state by placing the cursor on buffer state column and press “F1” key.
I would like to share some more information on row affected, invalidations and swap for buffered table.
Reading data from application server buffer has no impact on the number in “Row affected” column. Any operation which needs to access database, it would increase the number.
Table data/structure changes can cause buffer invalidation and increase “Row affected”.
Bypassing buffer operation would increase “Row affected” as well. Using SAP data browser transaction SE16 would always get data from database table instead of buffer. There are certain SQLs which can bypass buffer. Please refer to section “Review Table buffer strategy” in post SQL performance analysis for a list of SQLs which bypass SQL.
SAP ST10 table call statistics monitor shows no SWAP information – but SWAP would impact “Row affected” number reported by ST10. After data is swapped, data is not available in the memory, this would leave an “absent” status, next read on the swapped data would result in reloading the buffer from the database table.
I would like to highlight the difference between SAP swap and invalidation related to a buffered table. SWAP is due to buffer space setting. Invalidation is due to table change. SWAP is server dependent and invalidation is based on table regardless of server. If you have two application servers and one fully buffered table with 100 records, change one record would cause invalidation on both servers, “Row affected” would be increased at least “200” – 100 each per server. If the table is swapped on one server but not another server, then “Row affected” due to this swap would be increased “100” not “200”.
SWAP information is recorded at sever and buffer space level not at table level at current SAP version. Swap information for buffered objects is available via SAP transaction ST02 – memory/buffer. If you would like to know more about ST02, please refer to post – How to run SAP transaction ST02 and do SAP memory and buffer performance analysis.
Invalid status is not observed, any changes on a buffered data would change the status from “valid” to “pending” instead of “invalid”. Those are based on my testing on how table buffer status is changed.
2.2 How to understand information in SAP ST10 table call statistics details screen
The information in single table call statistics detail screen is straightforward. The quality is to measure data fulfill rate from buffer. Changes in overview screen are breaking down into SQL update, delete and insert operations.
For buffered table option, the call details screen contains “Buffer administration data” section which is showing the current buffer status for the table as well as how big the buffered table is in the memory.
Select single is contributed by reading table via full primary key.
Now you have understood information provided by SAP transaction ST10. So what is the information used for?
3 How to use SAP ST10 table call statistics monitor to do performance analysis
As mentioned in the beginning of this post, Information from SAP transaction ST10 is used to
- Identify a table which could be buffered,
- Remove buffering on a table which should not buffered and
- Choose right buffer method for a table.
Now with the knowledge of how to run the transaction ST10 and understanding of the information provided the table call statistics monitor, we are ready to cover above points.
What table should be buffered? A table can be considered for buffering if it meets following conditions:
- Table content is quite stable and seldom changed,
- Table is frequently queried,
- How table is accessed and
- Table size is not big.
Using the above conditions, you can test a buffered table to see whether the table buffering is justified.
An un-buffered table meets above conditions – this does not guarantee that the table should be buffered some times, vice versa. Those conditions are able to be assessed for a table based on information present in SAP transaction ST10. There are other factors we need to consider when we make buffering choice. I would come out a post on table buffering choice to cover all factors which I think it is important.
3.1 Identify a table which might be buffered via SAP ST10
You run SAP transaction ST10 using “not buffered” option for one or several periods. You can use table call statistics overview screen (see figure 3) to find whether any “not buffered” table should be buffered. You can sort the display in several ways to check and verify information. Column “Changes/Total (%)” is an indicator on how stable a table content is. The table can be considered for buffering is that those table whose changes rate is under 0.5% – this is not a hardline which cannot be crossed.
When I review the sorted ST10 table call statistics data, i normally check
- Is the local table on the top with least change rate?
- Is number of direct read and seq. reads significant? – Buffering a table with a few activities would have very limited performance benefits. Buffering would benefit more if the table is frequently accessed.
- Is number of rows affected significant? – Number of row affected can be due to se16 or transportation. If number of affected row is high and there is no changes, then buffering the table is good for performance.
If a table passes above check, then it is a candidate for table buffering. Sometime you need to check several periods and make sure the period you checked is significant enough so all business scenarios are occurred in those selected periods. Normally monthly period and plus weekly period is good enough.
Figure 9 – ST10 “not buffered” table call statistics
Standard SAP tables have a pre-setting for table buffer, which is working fine in most cases. You can change it when there is a real need. So we focus on local table. Normally, you do not buffer a transaction table which falls into “changed too often” category etc.
3.2 Remove buffering on a table which has been wrongly buffered
A buffered table which is frequently changed should be removed from the buffer. You can find such candidate via SAP transaction ST10. In the table call statistics overview screen for buffered table, there is no change ratio listed but that there is an invalidation column. The data in invalidation column and Changes column can be used to judge the extent of the changes on a table. So they can be used to identify potential table candidates whose buffering might be removed after a complete review. Row affected in ST10 can be used as an indicator as well providing you have fully assess the information based on what I described in previous section of this post.
I normally consider following factors to identify a potential table for buffer removing:
- Is the local table on the top with a lot of data changes and/or invalidation. Table with a lot of changes and invalidations might not need to be buffered.
- Is number of direct read and seq. reads significant? –a table might not be needed to be buffered if it is not queried a lot.
- Is number of rows affected significant?
3.3 Choose right buffering type
If a table is a good candidate for buffering, then what is buffering type? SAP ST10 table call statistics monitor can give us some indication on the buffering type. You can use ABAP processor requests to determine what is a possible buffering type for the table. If all read access are direct reads, then single record buffering should be good enough. If all table accesses on the table is “Seq. reads”, then you need to consider “generic” or “full” table buffering type. You need more details information to choose “generic key” or “full” buffering type. This would be covered in my future post on SAP table buffering.
If a table is already buffered, using direct and seq. reads information, we can know whether the buffering type is correct based on what stated in the above paragraph.
4 Further clarification
This post covers how to run SAP transaction ST10 and how to use it for table buffering performace analysis. Information provided by SAP ST10 table call statistics analysis is straightforward for buffering , like a not buffered table is being accessing many times without change…If this is not a case, what other factors we should consider to put on a buffer or remove a buffer, those are covered in my next post – SAP table buffering.