How to disable oracle cache for performance tests

Question

I'm trying to test the utility of a new summary table for my data.

So I've created two procedures to fetch the data of a certain interval, each one using a different table source. So on my C# console application I just call one or another. The problem start when I want to repeat this several times to have a good pattern of response time.

I got something like this: 1199,84,81,81,81,81,82,80,80,81,81,80,81,91,80,80,81,80

Probably my Oracle 10g is making an inappropriate caching.

How I can solve this?

Answer 1

EDIT: See this thread on asktom, which describes how and why not to do this.

If you are in a test environment, you can put your tablespace offline and online again:

ALTER TABLESPACE <tablespace_name> OFFLINE;
ALTER TABLESPACE <tablespace_name> ONLINE;

Or you can try

ALTER SYSTEM FLUSH BUFFER_CACHE;

but again only on test environment.

When you test on your "real" system, the times you get after first call (those using cached data) might be more interesting, as you will have cached data. Call the procedure twice, and only consider the performance results you get in subsequent executions.