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!

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

Answer 2

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.

Answer 3

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