Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

How to Clear buffer for SQL Queries : ecc

0 Kudos

Hi,

Part of Performance improvement to the FM I have created new FM by copying the existing FM for both input-output is same, hitting the same tables. I am using ST05, SAT to measure the changes, In new FM, I have slightly changed SQL queries and ABAP code.

The problem as I am hitting same tables with the same query in both fm's, the first time when I execute any fm the results takes more time(60secs) and buffered then the second fm will execute too fast(4secs).

I tried executing one fm one day another FM in one day - buffer will not save by doing it. here another problem is I am testing in QA the server load is different in every day. Let's say I executed my new/old FM one day it takes 50 secs another day it takes 100secs....with the same query.

We tried with /$SYNC & /$TAB did not help, my basis team also hands up with no help, they say results are buffered at oracle db level to clear that buffer we do not have an option.

Let us know how we can clear the buffer and do testing same time two FM's

Thanks,

Vishwa

13 REPLIES 13

Sandra_Rossi
Active Contributor
0 Kudos

It's a classic "problem". You can't have the same duration if you run it at different times, in different contexts, etc. In extreme cases, a system is dedicated to the performance optimizations. Even for the first measurement, there must be 2 successive executions, and you measure the second one, because of the buffering. If one SQL statement is really too long according to you, then you must analyze its execution plan, change the SQL, then run it twice again and measure the second one. It's not only the duration, there are other factors to analyze (number of segments read, etc.) /$SYNC only clears the current application server SAP buffer (not the database buffer).

DoanManhQuynh
Active Contributor
0 Kudos

I dont realy understand why you have to clear the buffer if it make your queries faster. If you dont want to use buffer, i think you can set buffering not allowed for the table you selecting (in SE11 or SE13), or another simple way is add BYPASSING BUFFER in your query...You can read this thread which talked about buffer, there is a guideline to analysis too:

https://blogs.sap.com/2015/08/27/to-buffer-or-not-to-buffer-a-database-table/

0 Kudos

Hi,

that was a damn good blog on how sap buffering mechanism works.

but my question was different, I am trying to clear the buffer only in QA to test two function modules at the same time.

BYPASSING BUFFER - this we can achieve using $tab or $sync also. this is not my requirement.

Vishwa.

0 Kudos

OK lets say you want to check performance of 2 fm on the same DB set with difference coding approach. I dont think you need to care about buffer here, I think you can get data into buffer (by creating a simple SQL or something just for load data to buffer purpose), then run both fm against that buffer you will have the result you want ( just my ideal 🙂 ). You can make schedule to run those fm at the same time to minimum other affects. anw, you cant expect that it would be the same for every time you run the same fm, like Sandra said.

0 Kudos

A dedicated system for performance optimizations - which is difficult.

Execution plan: Yes I am doing it, but it only gives whether indexes are used or not. As per analysis of execution plan I did some changes to new fm. now I want to test the two fm's and see did I achieve any performance or not.

/$SYNC only clears the current application server SAP buffer (not the database buffer). - Yes, but this is the only option I found in forums/blogs and from the basis.

Vishwa

horst_keller
Product and Topic Expert
Product and Topic Expert
0 Kudos

Don't know anything about "buffered at oracle db level"

but about ABAP's table buffering:

https://help.sap.com/http.svc/rc/abapdocu_752_index_htm/7.52/en-US/index.htm?file=abensap_puffering....

raymond_giuseppi
Active Contributor
0 Kudos

So that's an Oracle matter,

"To get consistent time measurements without being affected by caches, you'll have to clear all caches before executing  a query under test:
•	Clear Oracle's buffer_cache:
	o	alter system flush buffer_cache;
•	Clear Oracle's shared_pool:
	o	alter system flush shared_pool;<br>

Not sure for native-sql command, better look for available tools, so ask database admin for some script?

0 Kudos

Thanks Raymond,

it could an option,we have SQL command editor in sap but I don't have authorization to test. I have to work with DBA.

https://help.sap.com/saphelp_nw73ehp1/helpdata/EN/4d/0abd286dc25c4be10000000a42189e/frameset.htm

Vishwa

0 Kudos

Hi Raymond,

I worked with DBA, for every iteration of testing we have executed two cache statements which you had provided. At the same time we also executed /$tab, /$sync & logoff to SAP and log in again.

First-time FM execution took 15 seconds if we don't clear buffer it takes only 2 seconds. Once we follow all above steps to clear buffer then execute again it takes 5/6 seconds. not sure something else buffer we have to celar.

Vishwa

0 Kudos

Oracle buffer won't be cleared if

  • pinned (actually in use)
  • dirty (some data need yet to be written to disk)

In your case buffer seems to be refreshed(2->5s) how long does it perform a third time aslo you could have executed the script before first call to be more confident in results.

Don't forget you are not alone on the DB and AS server, so many events can cause variable results. You should create a bigger set of data for test to execute some more calls (10, 100, 1000) with different selection criteria.

0 Kudos

use RH_CLEAR_BUFFER to clear the buffer inside the FM

0 Kudos

Thanks Karuna, i tried did not help.

0 Kudos

This will refresh/free some internal table(s), use as buffer in some FM, in its own function group, it's not a general purpose tool...