How to clear all cached items in Oracle

问题:

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!

回答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

回答2:

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

回答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.

回答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).