SQL 如何清除 Oracle 中的所有缓存项
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2147456/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to clear all cached items in Oracle
提问by Kevin Babcock
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:
我正在调整 Oracle 数据库上的 SQL 查询。我想确保在运行每个查询之前清除所有缓存的项目，以防止误导性的性能结果。我通过运行以下命令清除共享池（以摆脱缓存的 SQL/解释计划）和缓冲区缓存（以摆脱缓存的数据）：
alter system flush buffer_cache;
alter system flush shared_pool;
Is there more I should be doing, or is this sufficient?
我还应该做更多的事情，还是这样就足够了？
Thanks!
谢谢！
采纳答案by Doug Porter
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:
刷新共享池应该可以，但 Tom Kyte 在下面列出了为什么在某些情况下您可能无法获得预期结果的几个原因：
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6349391411093
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6349391411093
回答by Daniel Emge
Keep in mind that the operating system and hardware also do caching which can skew your results.
请记住，操作系统和硬件也会进行缓存，这可能会影响您的结果。
回答by PenFold
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.
然后清除共享池。
回答by BobC
I would contend that you would be presenting misleading result becauseyou 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).
我认为您会提供误导性结果，因为您已经清除了所有缓存。现实世界中的数据库，在其生命周期中只会处于这种状态一次。事实上，在性能测试时，多次运行查询通常是一种公认的做法，以便您可以看到缓存（和其他优化）的好处。
