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

how do i clear oracle execution plan cache for benchmarking?

提问by spencer7593

On oracle 10gr2, i have several sql queries that i am comparing performance, but after first run, the v$sql table has the execution plan stored for caching, so for one of the queries i go from 28 seconds on first run to .5 seconds after.

在 oracle 10gr2 上，我有几个比较性能的 sql 查询，但是在第一次运行后，v$sql 表存储了用于缓存的执行计划，因此对于其中一个查询，我从第一次运行时的 28 秒变为 0.5秒后。

I've tried

我试过了

ALTER SYSTEM FLUSH BUFFER_CACHE; -- after running this, the query consistently runs at 5 seconds, which i do not believe is accurate.

ALTER SYSTEM FLUSH BUFFER_CACHE; -- 运行此命令后，查询始终以 5 秒运行，我认为这不准确。

thought maybe deleting the line item itself from the cache: delete from v$sql where sql_text like 'select * from.... but get an error about not being able to delete from view.

想也许从缓存中删除行项目本身：从 v$sql 中删除，其中 sql_text 像 'select * from.... 但得到一个关于无法从视图中删除的错误。

回答by spencer7593

Peter gave you the answer to the question you asked.

彼得回答了你提出的问题。

alter system flush shared_pool;

That is the statement you would use to "delete prepared statements from the cache".

这是您将用于“从缓存中删除准备好的语句”的语句。

(Prepared statements aren't the only objects flushed from the shared pool, the statement does more than that.)

（准备好的语句不是从共享池中刷新的唯一对象，该语句的作用不止于此。）

As I indicated in my earlier comment (on your question), v$sqlis not a table. It's a dynamic performance view, a convenient table-like representation of Oracle's internal memory structures. You only have SELECT privilege on the dynamic performance views, you can't delete rows from them.

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

---

---

flush the shared pool and buffer cache?

刷新共享池和缓冲区缓存？

The following doesn't answer your question directly. Instead, it answers a fundamentally different (and maybe more important) question:

以下内容没有直接回答您的问题。相反，它回答了一个根本不同（也许更重要）的问题：

Should we normally flush the shared pool and/or the buffer cache to measure the performance of a query?

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

In short, the answer is no.

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

I think Tom Kyte addresses this pretty well:

我认为 Tom Kyte 很好地解决了这个问题：

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

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

<excerpt>

<摘录>

Actually, it is important that a tuning tool not do that. It is important to run the test, ignore the results, and then run it two or three times and average out those results. In the real world, the buffer cache will never be devoid of results. Never. When you tune, your goal is to reduce the logical I/O (LIO), because then the physical I/O (PIO) will take care of itself.

实际上，调优工具不这样做很重要。运行测试，忽略结果，然后运行两到三次并平均这些结果是很重要的。在现实世界中，缓冲区缓存永远不会没有结果。绝不。调整时，您的目标是减少逻辑 I/O (LIO)，因为这样物理 I/O (PIO) 就会自行处理。

Consider this: Flushing the shared pool and buffer cache is even more artificial than not flushing them. Most people seem skeptical of this, I suspect, because it flies in the face of conventional wisdom. I'll show you how to do this, but not so you can use it for testing. Rather, I'll use it to demonstrate why it is an exercise in futility and totally artificial (and therefore leads to wrong assumptions). I've just started my PC, and I've run this query against a big table. I "flush" the buffer cache and run it again:

考虑一下：刷新共享池和缓冲区缓存比不刷新它们更人为。我怀疑，大多数人似乎对此持怀疑态度，因为它与传统智慧背道而驰。我将向您展示如何执行此操作，但不是这样您就可以将其用于测试。相反，我将用它来说明为什么它是一项徒劳且完全人为的练习（因此会导致错误的假设）。我刚刚启动了我的电脑，我已经对一个大表运行了这个查询。我“刷新”缓冲区缓存并再次运行它：

</excerpt>

</摘录>

I think Tom Kyte is exactly right. In terms of addressing the performance issue, I don't think that "clearing the oracle execution plan cache" is normally a step for reliable benchmarking.

我认为 Tom Kyte 是完全正确的。在解决性能问题方面，我认为“清除 oracle 执行计划缓存”通常不是可靠基准测试的一个步骤。

Let's address the concern about performance.

让我们解决对性能的担忧。

You tell us that you've observed that the first execution of a query takes significantly longer (~28 seconds) compared to subsequent executions (~5 seconds), even when flushing (all of the index and data blocks from) the buffer cache.

您告诉我们，您已经观察到，与后续执行（约 5 秒）相比，查询的第一次执行时间（约 28 秒）要长得多，即使在刷新（所有索引和数据块）缓冲区缓存时也是如此。

To me, that suggests that the hard parseis doing some heavy lifting. It's either a lot of work, or its encountering a lot of waits. This can be investigated and tuned.

对我来说，这表明硬解析正在做一些繁重的工作。这要么是很多工作，要么是遇到了很多等待。这可以被调查和调整。

I'm wondering if perhaps statistics are non-existent, and the optimizer is spending a lot of time gathering statistics before it prepares a query plan. That's one of the first things I would check, that statistics are collected on all of the referenced tables, indexes and indexed columns.

我想知道统计信息是否不存在，并且优化器在准备查询计划之前花费了大量时间收集统计信息。这是我要检查的第一件事，即收集所有引用表、索引和索引列的统计信息。

If your query joins a large number of tables, the CBO may be considering a huge number of permutations for join order.

如果您的查询连接了大量表，则 CBO 可能正在考虑对连接顺序进行大量排列。

A discussion of Oracle tracing is beyond the scope of this answer, but it's the next step.

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

I'm thinking you are probably going to want to trace events 10053 and 10046.

我想您可能想要跟踪事件 10053 和 10046。

Here's a link to an "event 10053" discussion by Tom Kyte you may find useful:

这是 Tom Kyte 的“事件 10053”讨论的链接，您可能会觉得有用：

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

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

---

---

tangentially related anecdotal story re: hard parse performance

切线相关的轶事故事：硬解析性能

A few years back, I did see one query that had elapsed times in terms of MINUTES on first execution, subsequent executions in terms of seconds. What we found was that vast majority of the time for the first execution time was spent on the hard parse.

几年前，我确实看到一个查询在第一次执行时以 MINUTES 为单位，随后以秒为单位执行。我们发现第一次执行的大部分时间都花在了硬解析上。

This problem query was written by a CrystalReports developer who innocently (naively?) joined two humongous reporting views.

这个问题查询是由一个 CrystalReports 开发人员编写的，他无辜地（天真地？）加入了两个庞大的报告视图。

One of the views was a join of 62 tables, the other view was a join of 42 tables.

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

The query used Cost Based Optimizer. Tracing revealed that it wasn't wait time, it was all CPU time spent evaluating possible join paths.

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

Each of the vendor supplied "reporting" views wasn't too bad by itself, but when two of them were joined, it was agonizingly slow. I believe the problem was the vast number of join permutations that the optimizer was considering. There is an instance parameter that limits the number of permutations considered by the optimizer, but our fix was to re-write the query. The improved query only joined the dozen or so tables that were actually needed by the query.

每个供应商提供的“报告”视图本身并不算太糟糕，但是当其中两个加入时，速度慢得令人痛苦。我相信问题在于优化器正在考虑的大量连接排列。有一个实例参数限制优化器考虑的排列数量，但我们的修复是重新编写查询。改进后的查询只加入了查询实际需要的十几个表。

(The initial immediate short-term "band aid" fix was to schedule a run of the query earlier in the morning, before report generation task ran. That made the report generation "faster", because the report generation run made use of the already prepared statement in the shared pool, avoiding the hard parse.

（最初的即时短期“创可贴”修复是在报告生成任务运行之前安排在早上早些时候运行查询。这使得报告生成“更快”，因为报告生成运行利用了已经在共享池中准备语句，避免硬解析。

The band aid fix wasn't a real solution, it just moved the problem to a preliminary execution of the query, when the long execution time wasn't noticed.

创可贴修复不是真正的解决方案，它只是将问题转移到查询的初步执行中，而没有注意到执行时间长。

Our next step would have probably been to implement a "stored outline" for the query, to get a stable query plan.

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

Of course, statement reuse (avoiding the hard parse, using bind variables) is the normative pattern in Oracle. It mproves performance, scalability, yada, yada, yada.

当然，语句重用（避免硬解析，使用绑定变量）是 Oracle 中的规范模式。它提高了性能、可扩展性、yada、yada、yada。

This anecdotal incident may be entirely different than the problem you are observing.

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

---

---

HTH

HTH

回答by Peter

It's been a while since I worked with Oracle, but I believe execution plans are cached in the shared pool. Try this:

自从我使用 Oracle 以来已经有一段时间了，但我相信执行计划缓存在共享池中。尝试这个：

alter system flush shared_pool;

The buffer cache is where Oracle stores recently used datain order to minimize disk io.

缓冲区缓存是 Oracle 存储最近使用的数据以最小化磁盘 io 的地方。

回答by Peter

We've been doing a lot of work lately with performance tuning queries, and one culprit for inconsistent query performance is the file system cache that Oracle is sitting on.

我们最近在性能调优查询方面做了大量工作，导致查询性能不一致的一个罪魁祸首是 Oracle 所在的文件系统缓存。

It's possible that while you're flushing the Oracle cache the file system still has the data your query is asking for meaning that the query will still return fast.

有可能在您刷新 Oracle 缓存时，文件系统仍然拥有您的查询所要求的数据，这意味着该查询仍将快速返回。

Unfortunately I don't know how to clear the file system cache - I just use a very helpful script from our very helpful sysadmins.

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