如何清除Oracle执行计划缓存以进行基准测试？

Question

如何清除Oracle执行计划缓存以进行基准测试？

sqloracle10g

35

在Oracle 10gr2上，我有几个SQL查询正在比较性能。但是，在它们第一次运行后，v$sql表存储了执行计划以进行缓存，因此其中一个查询从第一次运行的28秒降至0.5秒。

我尝试过：

ALTER SYSTEM FLUSH BUFFER_CACHE;

运行后，该查询始终在5秒左右运行，但我不认为这是准确的。

想着也许可以从缓存中删除相应的行：

delete from v$sql where sql_text like 'select * from....

但我遇到了一个关于无法从视图中删除的错误。

- me me

1

v$sql并不是一个表，它是一个动态性能视图，而且你不能从中删除行。 - spencer7593

4个回答

17

我已经有一段时间没有使用Oracle了，但我认为执行计划会被缓存到共享池中。请尝试以下操作：

alter system flush shared_pool;

缓冲区高速缓存是Oracle用来存储最近使用的数据，以最小化磁盘IO的地方。

- Peter

我的28秒查询在执行该命令后只需要1.5秒。 - me me

1

抱歉，这对您没有起作用。不过，这就是清除缓存执行计划的方法。 :) - Peter

1

最近我们一直在进行性能调优查询的工作，而导致查询性能不稳定的罪魁祸首之一就是Oracle所依赖的文件系统缓存。

有可能当你清空Oracle缓存时，文件系统仍然拥有你所需的数据，这意味着查询仍然会快速返回结果。

不幸的是，我不知道如何清除文件系统缓存 - 我只是使用我们非常乐于助人的系统管理员提供的一个非常有用的脚本。

- Jeremy

0

查找SQL_ID的地址和哈希值 select address,hash_value,inst_id,users_executing,sql_text from gv$sqlarea where sql_id ='7hu3x8buhhn18';

清除共享池中的计划 exec sys.dbms_shared_pool.purge('0000002E052A6990,4110962728','c');

验证 select address,hash_value,inst_id,users_executing,sql_text from gv$sqlarea where sql_id ='7hu3x8buhhn18';

- Vishe

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- spencer7593 · Accepted Answer

Peter 给了你所问问题的答案。

alter system flush shared_pool;

这是你用来“从缓存中删除预编译语句”的语句。

（预编译语句不是唯一从共享池中刷新的对象，该语句还执行了更多操作。）

正如我在之前的评论中所指出的（针对你的问题），v$sql 不是一个表。它是一个动态性能视图，是 Oracle 内部内存结构的方便类似表格的表示。你只有 SELECT 权限，无法从其中删除行。

清空共享池和缓冲区缓存？

以下内容并未直接回答您的问题，而是回答了一个根本不同（也许更重要）的问题：

我们通常应该清空共享池和/或缓冲区缓存来衡量查询的性能吗？

简而言之，答案是否定的。

我认为Tom Kyte在这方面表述得很好：

~~http://www.oracle.com/technology/oramag/oracle/03-jul/o43asktom.html~~
http://www.oracle.com/technetwork/issue-archive/o43asktom-094944.html

<excerpt>

实际上，调整工具不应该这样做。重要的是运行测试，忽略结果，然后运行两到三次并平均出这些结果。在现实世界中，缓冲区高速缓存永远不会没有结果。永远不会。当你进行调整时，你的目标是减少逻辑I/O（LIO），因为物理I/O（PIO）会自动处理。

考虑一下：清除共享池和缓冲区高速缓存甚至比不清除它们更加人为。大多数人似乎对此持怀疑态度，我怀疑，因为这与传统智慧相悖。我将向您展示如何做到这一点，但不是为了测试而使用它。相反，我将用它来证明为什么这是徒劳无益和完全人为的（因此导致错误的假设）。我刚刚启动了我的电脑，并针对一个大表运行了这个查询。我“清除”缓冲区高速缓存并再次运行它：

</excerpt>

我认为Tom Kyte是完全正确的。就性能问题而言，“清除Oracle执行计划缓存”通常不是可靠基准测试的步骤。

让我们解决有关性能的问题。

你告诉我们，即使清除（所有索引和数据块从）缓冲区高速缓存，查询的第一次执行时间比后续执行时间长得多（约28秒与~5秒）。对我来说，这表明硬解析正在进行一些繁重的工作。它要么是很多工作，要么是遇到了很多等待。这可以进行调查和调整。

我在想，也许统计信息不存在，优化器在准备查询计划之前花费了很多时间来收集统计信息。这是我要检查的第一件事，即在所有引用的表、索引和索引列上收集统计信息。

如果您的查询连接了大量的表，CBO 可能会考虑大量的连接顺序排列组合。

Oracle 跟踪的讨论超出了本答案的范围，但这是下一步。

我认为您可能需要跟踪事件 10053 和 10046。

这是一个由 Tom Kyte 讨论的“事件 10053”的链接，您可能会发现有用：

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

与硬解析性能相关的轶事

几年前，我看到一个查询在第一次执行时花费了数分钟的时间，而后续执行只需要几秒钟。我们发现，第一次执行所花费的大部分时间都用于硬解析。

这个问题查询是由一个CrystalReports开发人员编写的，他天真地（或者说幼稚地）将两个庞大的报表视图连接在一起。

其中一个视图连接了62个表，另一个视图连接了42个表。

查询使用基于成本的优化器。跟踪结果显示，这不是等待时间，而是所有CPU时间都花费在评估可能的连接路径上。

每个供应商提供的“报表”视图本身并不太糟糕，但当它们连接在一起时，速度非常慢。我认为问题在于优化器考虑的连接排列数量太多了。虽然有一个实例参数可以限制优化器考虑的排列数量，但我们的解决方案是重新编写查询。改进后的查询只连接了实际需要的十几个表。

（最初的即时短期“临时措施”是在早上较早的时间安排运行查询，以避免在报表生成任务运行时进行硬解析。这使得报表生成“更快”，因为报表生成运行利用了共享池中已准备好的语句，避免了硬解析。

临时措施并不是真正的解决方案，它只是将问题移动到查询的初步执行，当长时间的执行时间不被注意时。

我们下一步可能会实现查询的“存储大纲”，以获得稳定的查询计划。

当然，在Oracle中，语句重用（避免硬解析，使用绑定变量）是规范模式。它可以提高性能、可伸缩性等等。

这个轶事可能与你观察到的问题完全不同。

HTH