Generic or Fully buffering in open SQL

Step1. Launch Tcode- AL12 which displays all about the sap buffering.

Step2. When any transaction is executed in SAP and it updated the DB table and if any buffering id switched on that table and the buffer memory is allocated to it then the previous buffer entries should be updated with the new/updated values. This takes place via sap standard program and this program runs in some intervals of second ( this interval might depend on the some basis configuration). This interval is called synchronization period.  In this case it is 120 second. So in every 120 second the buffers are updated to sync up the values with the back end db entries. Also we can see the amount of memory allocated to generic key /fully buffering and for single key buffering.

Step3. Form the menu navigate along the highlighted path.

Step4. Here we can see what is the efficiency on table buffers.

Step5. Navigate along the path to see all the tables on which generic or fully buffering is applied.

So here we have list of tables; let’s for the use case select one table: CVERS . So 125 records are buffered for this table.

Step6. From the home screen of tcode- AL12 navigate along the highlighted path.

Step7. Give the table name as decided above as CVERS and display it.

Step8. So here we get the details about the table CVERS.

Step9. In SE11 open the table CVERS and go to the technical settings. So here buffering is switched on and it is a fully buffer on the table.

Step10. Check the number of entries in the table. Its 125 which is exactly match with teh number of entries in the buffer area for the table CVERS.

Step11. We have this program to test whether the record is read from the buffer or from the DB.

But only through program it is hard to find out. So we can get it by tracing the SQL statements in ST05 tcode for our program.

Step12. Go to Tcode- ST05 . Select SQL trace and Buffer Trace and then click on the button Activate Trace with filter.

Step13. Provide user name, program name and the table name . Continue.

Step14. Trace is activated.

Step15. Run the program and select the exact option. This time we want from the buffer. Execute it.

Step16. So we have the O/P.

Step17. Deactivate the trace and display the trace.

Step18. Execute it.

Step19. So here we have the trace details for our program. As the select query is on the table and it contains 125 records , we read 125 records with a time limit of 93 micro seconds. If u observe that there is no connection name means it is read form the buffer.

Step20. From the menu option navigate along the highlighted option to see the consolidated trcae result.

Step21. So here the DB connection column is blank so our select query reads it from the buffer.

Step22. This time in the trace we will trace what happens if  our select query by pass the buffer and reads the records from the DB table. Activate the trace with filter.

Provide our details.

Trace activated.

Run the progam with another option.

Step23. So here we have the O/P.

Step24. Deactivate the trace and display it.

Execute the display.

Step25. So here we have the trace details. This time the connection (conn) column is filled with R/3, as we bypass & read directly from the DB. So this time it took around 890 micro seconds to get the same 125  number of records.

Step26. Navigate along the menu option to see the details of the trace.

Step27. So here we have the details.

Step28. Just a comparison between the two cases. One reading form the buffer and later reading directly from the db.

Reading from the Db is costlier and time expensive rather than reading from buffer.

Step29. comparison on the consolidated level.

Step30. In the Tcode- AL12 also we can get to know what are all the records are currently in buffer.

From AL12 tcode navigate along the menu highlighted path.

Step31. Provide the table name and Select Show Buffered Keys.

Step32. So here we have all the 125 records of the table CVERS are buffered.




Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s