如何清除oracle执行计划缓存进行基准测试? [英] how do i clear oracle execution plan cache for benchmarking?

问题描述

在oracle 10gr2上，我有几个要比较性能的sql查询，但是在第一次运行后，v $ sql表已存储了执行计划以进行缓存，因此对于其中一个查询，我第一次运行时需要28秒到.5秒后.

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.

我尝试过

ALTER SYSTEM FLUSH BUFFER_CACHE; -运行此命令后，查询始终在5秒钟后运行，我认为这是不正确的.

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

可能会从缓存中删除订单项本身: 从v $ sql删除，其中sql_text如'select * from.... 但收到有关无法从视图中删除的错误.

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.

推荐答案

Peter为您提出了问题的答案.

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

正如我在较早的评论(关于您的问题)中指出的那样，v$sql不是表.这是一个动态性能视图，是Oracle内部内存结构的便捷表状表示.您仅对动态性能视图具有SELECT特权，不能从其中删除行.

As I indicated in my earlier comment (on your question), v$sql is 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.

刷新共享池和缓冲区高速缓存?

以下内容不会直接回答您的问题.相反，它回答了一个根本不同(也许更重要)的问题:

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.

我认为汤姆·凯特(Tom Kyte)很好地解决了这个问题:

I think Tom Kyte addresses this pretty well:

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

<摘录>

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

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.

考虑一下:刷新共享池和缓冲区高速缓存比不刷新它们更人为.我怀疑，大多数人对此表示怀疑，因为它在传统观念面前飞了起来.我将向您展示如何执行此操作，但不会，因此您可以将其用于测试.相反，我将用它来证明为什么它是徒劳的，而且是完全人为的(因此导致错误的假设).我刚启动我的PC，并且已针对一个大表运行了此查询.我刷新"了缓冲区缓存，然后再次运行它:

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:

</摘录>

我认为汤姆·凯特(Tom Kyte)完全正确.在解决性能问题方面，我认为清除oracle执行计划缓存"通常不是可靠基准测试的步骤.

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.

让我们解决对性能的关注.

Let's address the concern about performance.

您告诉我们，您已经观察到，即使刷新查询(来自其中的所有索引和数据块)，查询的第一次执行比后续的执行(〜5秒)要花费更长的时间(〜28秒).缓冲区缓存.

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.

对我来说，这表明硬解析正在做一些繁重的工作.它要么是很多工作，要么是等待很多时间.可以对此进行调查和调整.

To me, that suggests that the hard parse is 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.

如果您的查询联接了大量表，则CBO可能正在考虑联接顺序的大量排列.

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

有关Oracle跟踪的讨论超出了此答案的范围，但这是下一步.

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

我认为您可能想跟踪事件10053和10046.

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

以下是汤姆·凯特(Tom Kyte)的活动10053"讨论的链接，您可能会发现它有用:

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

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

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

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

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.

此问题查询是由CrystalReports开发人员编写的，他无辜(天真?)加入了两个庞大的报表视图.

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

其中一个视图是62个表的联接，另一个视图是42个表的联接.

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

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

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

每个供应商提供的报告"视图本身都还不错，但是当其中两个被加入时，它的速度非常慢.我认为问题在于优化程序正在考虑的大量联接置换.有一个实例参数可以限制优化程序考虑的排列数量，但是我们的解决方法是重新编写查询.改进的查询仅加入了查询实际需要的十几个表.

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.

当然，语句重用(避免使用绑定变量进行硬解析)是Oracle中的规范模式.它提高了性能，可扩展性，yada，yada，yada.

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

此轶事可能与您所观察到的问题完全不同.

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

HTH

这篇关于如何清除oracle执行计划缓存进行基准测试?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！