如何使用 Oracle 正确地对 SELECT 查询进行性能测试？

How do I correctly performance test SELECT queries with Oracle?

提问by JavaRocky

I would like to test two queries to find out their performance as apposed to just looking at the execution plan. I have seen Tom Kyte do this all the time on his website as a way to gather evidence on his theories.

我想测试两个查询以找出它们与仅查看执行计划有关的性能。我在他的网站上一直看到 Tom Kyte 这样做是为了收集关于他的理论的证据。

I believe there are many pitfalls in performance testing, for example, when i run a query in SQL developer for the first time, that query might return some fair number. Running that exact same query again, returns instantaneously. There must be some sort of caching on the server or client going on and I understand this is important - however I am only interested in non cached performance.

我相信在性能测试中有很多陷阱，例如，当我第一次在 SQL developer 中运行查询时，该查询可能会返回一些合理的数字。再次运行完全相同的查询，立即返回。服务器或客户端上必须有某种缓存，我知道这很重要 - 但是我只对非缓存性能感兴趣。

What are the guidelines to performance test? AND how do I write a performance test which repeats the query? Do i just write an anonymous block & loop? How do i get timing information, averages, medians, std deviations?

性能测试的指导方针是什么？以及如何编写重复查询的性能测试？我只是写一个匿名块和循环吗？我如何获得时间信息、平均值、中位数、标准偏差？

回答by OMG Ponies

Oracle (and other databases) cache queries, which is where you see the behavior you describe. A "hard" parse means there's no query plan for the query, which leaves Oracle to figure out the query plan based on indexes and statistics. A "soft" parse is what happens when you run the identical queryafterwards, and receive an instantaneous result, because the query plan exists & Oracle re-uses it. See the Ask Tom question about it for more details.

Oracle（和其他数据库）缓存查询，您可以在此处看到您所描述的行为。“硬”解析意味着查询没有查询计划，这让 Oracle 需要根据索引和统计信息来确定查询计划。“软”解析是当您之后运行相同的查询并收到即时结果时发生的情况，因为查询计划存在且 Oracle 重新使用它。有关更多详细信息，请参阅Ask Tom 问题。

Be aware of the EXPLAIN output:

请注意EXPLAIN 输出：

With the cost-based optimizer, execution plans can and do change as the underlying costs change. EXPLAIN PLAN output shows how Oracle runs the SQL statement when the statement was explained. This can differ from the plan during actual execution for a SQL statement, because of differences in the execution environment and explain plan environment.

使用基于成本的优化器，执行计划可以并且确实随着基础成本的变化而变化。EXPLAIN PLAN 输出显示了在解释语句时 Oracle 如何运行 SQL 语句。由于执行环境和解释计划环境的差异，这可能与 SQL 语句实际执行期间的计划不同。

Focusing on the non-cached performance gives a worst-case scenario, but given that caching will occur - non-cached benchmarks aren't realistic in everyday use.

专注于非缓存性能给出了最坏的情况，但鉴于会发生缓存 - 非缓存基准测试在日常使用中是不现实的。

回答by Adam Musch

To build off OMG Ponies answer, tuning based on timing is something that's possible, but not realistic. You'd have to start either with a fully-cached buffer cache in every case, or a fully-empty buffer cache, and neither of those is going to be representative of reality - especially if there's no competing load.

为了建立 OMG Ponies 的答案，基于时间的调整是可能的，但不现实。在每种情况下，您都必须从完全缓存的缓冲区缓存开始，或者从完全空的缓冲区缓存开始，而这两者都不能代表现实——尤其是在没有竞争负载的情况下。

When I'm tuning, it's generally against a live system with activity, and I focus on tuning logical I/Os, either through using the extended SQL trace (dbms_monitor.session_trace_enable / dbms_monitor.session_trace_disable) and the tkprofutility, or using SQL*Plus and set autotrace traceonly- which does all the work of the query, but throws the output away, because I'm usually not interested in watching a jillion rows scroll by.

当我进行调优时，通常是针对有活动的实时系统，我专注于调优逻辑 I/O，或者通过使用扩展的 SQL 跟踪 ( dbms_monitor.session_trace_enable / dbms_monitor.session_trace_disable) 和tkprof实用程序，或者使用 SQL*Plus 和set autotrace traceonly- 它完成所有工作查询，但将输出扔掉，因为我通常对观看数以百万计的行滚动不感兴趣。

The exact mechanism usually involves bound SQL, using something like the following:

确切的机制通常涉及绑定 SQL，使用类似以下内容：

 variable :my_bind1 number;
 variable :my_bind2 varchar2(30);

 begin
   :my_bind1 := 42;
   :my_bind2 := 'some meaningful string';
 end;
 /

 set timing on;
 set autotrace traceonly;

 [godawful query with binds]

 set autotrace off;

Within the results, I'm looking for the plan I'd expect, a comparative value for sorts - assuming any exist - and most importantly, the number of consistent I/Os. That's how many blocks Oracle had to read in consistent mode to satisfy the query. I can't find the original source of the quote, but I think it's Cary Milsap of Method R.

在结果中，我正在寻找我期望的计划、排序的比较值（假设存在），最重要的是一致 I/O 的数量。这就是 Oracle 在一致模式下必须读取多少块才能满足查询。我找不到引用的原始来源，但我认为它是 Method R 的 Cary Milsap。

"Tune your logical I/Os, and your physical I/Os will follow."

“调整您的逻辑 I/O，您的物理 I/O 将随之而来。”

回答by ik_zelf

What is correctly ? Since 11g there are a few extra complications to take into account. The optimizer pre peeking has become a lot smarter and sql plan stability has a BIG influence. These two features make the database auto tuning but can also have unexpected effects during performance tests, for example because not all variations of the plans are known and accepted at the beginning of the tests. This might be the cause that a second test run, the day after the first run, suddenly runs much quicker, without any apparent changes. Since 11g performance testing is less important, compared to writing logically correct code. For example a Cartesian product and filtering out one distinct value van be functional correct but is in most of the cases wrong code because it fetches more data than logically needed. If the queries fetches the data that is really needed and is in the correct control structure, have the database processes tune the code during the maintenance windows. In many cases the differences between the test environment and production are such that a comparison can not be safely made. Don't get me wrong, testing is important but mostly for the logic compared to performance testing before 11g, there are extra steps to be taken. For nice reading see Oracle? Database 2 Day + Performance Tuning Guide 11g Release 2 (11.2)

什么是正确的？由于 11g，需要考虑一些额外的复杂性。优化器预窥已经变得更加智能，并且 sql 计划的稳定性有很大的影响。这两个特性使数据库自动调优，但也可能在性能测试期间产生意想不到的影响，例如因为在测试开始时并非所有计划的变化都是已知和接受的。这可能是第一次运行后的第二天，第二次测试运行突然变得更快的原因，没有任何明显的变化。由于与编写逻辑上正确的代码相比，11g 性能测试不太重要。例如，笛卡尔积和过滤掉一个不同的值 van 是功能正确的，但在大多数情况下是错误的代码，因为它获取的数据比逻辑需要的多。如果查询获取了真正需要的数据并且处于正确的控制结构中，则让数据库进程在维护窗口期间调整代码。在许多情况下，测试环境和生产环境之间的差异使得无法安全地进行比较。不要误会我的意思，测试很重要，但与 11g 之前的性能测试相比，主要是为了逻辑，需要采取额外的步骤。好看的阅读请看 需要采取额外的步骤。好看的阅读请看 需要采取额外的步骤。好看的阅读请看甲骨文？数据库 2 天 + 性能调优指南 11g 第 2 版 (11.2)

回答by Jeffrey Kemp

In performance tuning, if the only piece of data you look at is wall-clock time, you will only be getting a small part of the whole picture. You need to at least look at the execution plan, as well as IO stats, in order to work out how best to tune the query.

在性能调优中，如果您查看的唯一数据是挂钟时间，那么您只会获得整个图片的一小部分。您至少需要查看执行计划以及 IO 统计信息，以便确定如何最好地调整查询。

Also, you need to eliminate other causes of performance issues - e.g. if there is a general performance issue across many queries, it might not be the fault of just one of them - it might be an architecture problem, or significant concurrent activity on the database, or even an underlying hardware issue.

此外，您需要消除导致性能问题的其他原因——例如，如果许多查询都存在普遍的性能问题，则可能不仅仅是其中一个的问题——它可能是架构问题，或者数据库上的重大并发活动，甚至是潜在的硬件问题。

I've had similar issues to what you describe before; e.g. a certain type of query which should be very fast was taking 30 seconds to run on the first time, then would settle down to a second or two. As soon as I looked at the execution plan, however, it was obvious that it was using a full table scan, because it couldn't use the unique index that had been created. The first time the query ran, most of the data was loaded into the cache (in fact, there were two levels of cache involved - the database buffer cache, as well as a storage-level cache over the disks) so subsequent full table scans were extremely fast.

我遇到过与您之前描述的类似的问题；例如，某种类型的查询应该非常快，第一次运行需要 30 秒，然后会稳定到一两秒。然而，当我查看执行计划时，很明显它正在使用全表扫描，因为它无法使用已创建的唯一索引。第一次运行查询时，大部分数据被加载到缓存中（实际上，涉及到两级缓存 - 数据库缓冲区缓存，以及磁盘上的存储级缓存），因此后续的全表扫描非常快。