Clear cache with database statment

[Smetje]

Hello,

Is theire someone that knows a database statement to clear the cache?
On my server i have ASA 9,10 & Oracle 10

Reason: I want to test querry performance, so i have to clear
the cache to keep things fairly,

Before i used the oracle 9 statement,
alter session set events = 'immediate trace name flush_cache';

But it seems that this one in oracle 10 doesn't work anymore,

Thx

[davide++]

Hi all.

Try this

ALTER SYSTEM FLUSH BUFFER_CACHE;


Take a look at

http://www.fast-track.cc/10g_21.htm

[Smetje]

Strange when using sql*plus i can use these statement:

alter system flush buffer_cache;
alter session set events = 'immediate trace name flush_cache';
alter system flush shared_pool;
ALTER SYSTEM FLUSH SHARED_POOL;


In SQL tools only thes ones:
alter session set events = 'immediate trace name flush_cache';
alter system flush shared_pool;
ALTER SYSTEM FLUSH SHARED_POOL;

Anyway, it seems that none of theme has effect,
statment of 200 ms at first time duration become much less
even when i clear the cache???

[Smetje]

Seems that theire are som differences between oracle 9&10,
alter system flush shared_pool works with oracle 10 Sql*plus

[Smetje]

With oracle every thing works, anyone an idee how you clear the cache when using een ASA 9 or 10 database?

Thx

[Smetje]

I found one ASA method:

call sa_flush_cache ();

But it seems not to work,
Anyway a nice trick is to shut down and restart the service
--> cache empty

Grtz

[davide++]

Hi all.

Shut down an Oracle database is an extreme way to free the cache. This's a very heavy operation.
A part from this, remember that the free cache is an useful condition when you want compare the performance of two (or more) rewritten queries, in order to choose the best of them. If you have to do tuning on an existing query, that exists in production environment, you shouldn't alter the normal condition (usually the buffer cache is alway full in production environment).