开发者

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 resul开发者_开发知识库ts. 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!


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


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


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.


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

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜