It is common knowledge that buffering of database tables improves the system performance, provided the buffering is done judiciously – i.e. only those tables that are read frequently and updated rarely are buffered. But how exactly can we determine if a table is read frequently or updated rarely?
Also, the state of a buffered table in the buffer area is a runtime property which keeps changing with time. How can an ABAP developer, know whether a buffered table actually exists in the buffer, at a given time instant? This is a critical question to be considered while analyzing the performance of queries on buffered tables.
This blog post attempts to answer the above questions.
This blog post is divided into 3 sections and structured as follows:
:!: You might find the blog to be slightly lengthy but the content will NOT be more than what you can chew. Trust me! :grin:
Section 1: Prerequisites (Recap of Table Buffering Fundamentals and its Mechanism)
Buffering is the processing of storing table data (which is always present in the database) temporarily in the RAM of the Application Server. Buffering is specified in the technical settings of a table’s definition in the DDIC.
The benefits of buffering are:
The buffering mechanism can be visualized in Figure 1 below:
Figure 1: Buffering Mechanism
The SAP work processes of an application server have access to the SAP table buffer. The buffers are loaded on demand via the database connection. If a SELECT statement is executed on a table selected for buffering, the SAP work process initially looks up the desired data in the SAP table buffer. If the data is not available in the buffer, it is loaded from the database, stored in the table buffer, and then copied to the ABAP program (in the internal session). Subsequent accesses to this table would fetch the data from the buffer and the query need not go to the database to fetch it.
It must be understood that RAM space in the application server is limited. Let’s say – dbtab1 is a buffered table whose data is present in the buffer. When there is a query on another buffered table - dbtab2, its data will have to be loaded into the buffer. This might result in the data of dbtab1 getting displaced from the buffer.
When there is a write access to a buffered table, the change is done in the database and the old table data which is present in the buffer (of the application server from which the change query originated) is just flagged as “Invalid”. At this instant, the buffer and the database hold different data for the same table. A subsequent read access to the table would initiate a reload of the table data from the database to the buffer. Now the buffer holds the same data as the database.
Buffering a table that gets updated very frequently might actually end up increasing the load on the DB and increasing the network traffic between the application layer and the database. This would slow down the system performance and defeat the purpose of buffering.
Key Takeaways from Section 1:
(a) Read frequently
(b) Updated rarely
(c) Contains less data
Section 2: How to use the Table Call Statistics Transaction
This is accessed by the Tcode – ST10. The following is the initial screen:
Figure 2: ST10 - Initial Screen
A few points may be noted in Figure 2:
Let’s explore the results returned by the transaction when the radiobutton – “Not Buffered” is chosen.
Figure 3: Results of ST10 when the radio buttons - “Non-Buffered”, “This Server” and “From startup” are chosen
Let me explain the significance of each column –
Total = Direct Read + Seq. Reads + Changes.
Let’s explore the results when the radio button – “Generic Key Buffered” is chosen:
Figure 4: Results of ST10 when the radio buttons - “Generic Key Buffered”, “This Server” and “From startup” are chosen
There are some new columns here, which were not present in Figure 3. They are:
(a) SNG – Single Record Buffered Table
(b) FUL – Fully Buffered Table
(c) GEN – Generic Area Buffered Table
(a) VALID - The table content in the buffer is valid. Read access takes place in the buffer.
(b) ABSENT – The table has not been accessed yet. So the table buffer is not yet loaded with data.
(c) DISPLACED – The table buffer has been displaced
(d) INVALID - The table content is invalid and there are open transactions that modify the table content. Read access takes place in the database.
(e) ERROR - The table content could not be placed in the buffer, because insufficient space.
(f) LOADABLE – The table buffer in the buffer area is invalid, but can be loaded in the next access.
(g) MULTIPLE – Relevant only in the context of Generic Area Buffered Tables. These have different buffer statuses.
NOTE: All the table buffers in the current application server can be cleared by entering the Tcode- “/$TAB”.
Note that the user can toggle between one result set and another by using the buttons in the Application Toolbar (as shown in Figure 5):
Figure 5: Application Toolbar of the primary list screen of ST10.
Figure 6: Secondary List
Section 3: Interpreting the results of the Table Call Statistics Transaction to answer the questions posed above.
How to determine a non-buffered table which is suited to be buffered?
(a) Low Change Rate (under 0.5%)
(b) High number of reads (Direct Reads + Seq.Reads)
(c) Data volume not too large
If it is to be buffered, what should be its buffering type?
How to determine the efficiency of the buffer setting of already buffered tables?
NOTE: Ensure that the time frame for which the transaction is run is significant enough such that all the reports/applications were run in that period and all business scenarios occurred in that period. Only then, can this transaction guide us effectively in deciding which table’s buffer settings are to be altered.
Case Study:
Based on the above guidelines, let’s consider some examples in Figure 7, which shows the Non-Buffered Tables:
Figure 7: List of accesses to non-buffered tables.
I would like to draw your attention to the 3 tables enclosed by a green rectangle. Based on the trends for these three tables, it can be temporarily concluded that:
The above points are not the final decisions but just guidelines. Other aspects like data volume, size category, access frequency etc are to be considered.
How can an ABAP developer, know whether a buffered table actually exists in the buffer, at a given time instant?
Figure 8: Buffer State of TSTC table after clearing the buffers using - /$TAB.
DATA: GW_TSTC TYPE TSTC.
CONSTANTS: C_SE38 TYPE TSTC-TCODE VALUE 'SE38'.
SELECT SINGLE *
FROM TSTC
INTO GW_TSTC
WHERE TCODE = C_SE38.
Figure 9: Buffer State of TSTC table after the above code snippet is run
Figure 10: ST05-SQL Trace when the above code snippet is run for the first time. Data is fetched from database.
Figure 11: ST05-Buffer Trace when the above code snippet is run for the second time. Data is fetched from buffer.
Conclusion:
ST10 is a very useful transaction that can guide you in answering the following questions:
References:
[1] Gahm, H., “Chapter 3 – Performance Analysis Tools,” ABAP Performance Tuning, 1st ed., Galileo Press, Boston, 2010, pp. 51-54.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
3 | |
2 | |
2 | |
2 | |
2 | |
2 | |
1 | |
1 | |
1 |