How to clear all cached items in Oracle

Question

I'm tuning SQL queries on an Oracle database. I want to ensure that all cached items are cleared before running each query in order to prevent misleading performance results. I clear out the shared pool (to get rid of cached SQL/explain plans) and buffer cache (to get rid of cached data) by running the following commands:

alter system flush buffer_cache;
alter system flush shared_pool;

Is there more I should be doing, or is this sufficient?

Thanks!

Pinkeye

Answer 1

Flushing the shared pool should do it, but Tom Kyte lists a couple reasons below why you may not get the result you are expecting in some cases:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6349391411093

Runck

Wow, that's a great link with some very good info. Thanks for sharing! – Pinkeye

Answer 2

Keep in mind that the operating system and hardware also do caching which can skew your results.

Vidavidal

A rather late comment, but it should be noted that if the database is using ASM (Automatic Storage Management) the only local caching will be in the Oracle buffer cache. There will be no Oracle blocks in the filesystem cache. – Aqua

Answer 3

You should also gather statistics - either for your schemas or even whole database:

begin
   dbms_stats.gather_schema_stats('schema_name');
end;

or

begin
   dbms_stats.gather_database_stats;
end;

And then clear the shared pool.

Neurasthenia

Answer 4

I would contend that you would be presenting misleading result because you have cleared all the caches. The database in the real world, is only ever in that state once in its life. In fact, when performance testing, it's generally an accepted practice to run a query multiple times so that you can see the benefit of caching (and other optimizations).

Selwin