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.

Answer 2

Probably my Oracle 10g is making a inappropriate caching.

Actually it seems like Oracle is doing some entirely appropriate caching. If these tables are going to be used a lot then you would hope to have them in cache most of the time.

edit

In a comment on Peter's response Luis said

flushing before the call I got some interesting results like: 1370,354,391,375,352,511,390,375,326,335,435,334,334,328,337,314,417,377,384,367,393.

These findings are "interesting" because the flush means the calls take a bit longer than when the rows are in the DB cache but not as long as the first call. This is almost certainly because the server has stored the physical records in its physical cache. The only way to avoid that, to truely run against an empty cache is to reboot the server before every test.

Alternatively learn to tune queries properly. Understanding how the database works is a good start. And EXPLAIN PLAN is a better tuning aid than the wall-clock. Find out more.