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

Question

我想测试两个查询，以了解它们的性能，而不仅仅是查看执行计划。我经常在Tom Kyte的网站上看到他这样做，作为收集证据支持他的理论的一种方式。

我认为性能测试存在许多陷阱，例如，在SQL开发人员中第一次运行查询时，该查询可能返回一些公平的数字。再次运行完全相同的查询会立即返回结果。服务器或客户端上必须进行某种缓存，我理解这很重要 - 但我只对非缓存性能感兴趣。

如何进行性能测试的指南？我如何编写重复查询的性能测试？我只需编写匿名块和循环吗？如何获取时间信息、平均值、中位数和标准差？

- JavaRocky

---

请查看此链接：https://dev59.com/NnI95IYBdhLWcg3wvgh9 - Mike Park

对所有回复者，作为一名开发者，我感到无助。我没有足够的知识或工具。 :| - JavaRocky

Answer 1

为了进一步解释OMG Ponies的回答，基于时间的调优是可能的，但不现实。你必须从一个完全缓存的缓冲区开始，在每种情况下都是如此，或者从一个完全空的缓冲区开始，这两种情况都不能代表现实情况，特别是如果没有竞争负载。

当我进行调优时，通常针对正在运行的系统进行活动，并且我专注于通过使用扩展SQL跟踪（dbms_monitor.session_trace_enable/dbms_monitor.session_trace_disable）和tkprof实用程序，或使用SQL*Plus和set autotrace traceonly来调整逻辑I/O - 它执行查询的所有工作，但将输出丢弃，因为我通常不感兴趣观看无数行滚动。

确切的机制通常涉及绑定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;

在搜索结果中，我要找到预期的计划、某种比较值（如果有的话）以及最重要的是一致性I/O数量。这就是Oracle在一致性模式下为满足查询而必须读取的块数。我无法找到引用此语句的原始来源，但我认为它来自Method R的Cary Milsap。

"调整你的逻辑I/O，物理I/O将随之而来。"

- Adam Musch

Answer 2

Oracle（以及其他数据库）会缓存查询，这就是您所描述的行为。 "硬"解析意味着查询没有查询计划，这使得Oracle基于索引和统计信息来确定查询计划。 "软"解析是当您之后运行相同的查询并立即获得结果时发生的情况，因为查询计划已经存在并且Oracle重用它。有关更多详细信息，请参见Ask Tom问题。

请注意EXPLAIN输出：

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

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

- OMG Ponies

---

嗨OMG Ponies，你的Jedi非常强大。我更新了我的问题。如何计时查询并重复运行它？ - JavaRocky

@JavaRocky：计时查询取决于实例上的资源（部分原因是由于负载），以及网络流量（如果通过互联网或广域网，则更多）-这不是一个有效的基准。TKPROF可能更符合您的需求。 - OMG Ponies

你的看法是对的，执行计划的缓存确实可以提高程序的性能，但是除非系统存在非常严重的解析争用问题，否则不太可能对总体执行时间产生很大的影响。更有可能对已过去的时间产生显著影响的是对实际运行执行计划所需的数据块进行缓存。 - Dave Costa

Answer 3

在性能调整中，如果你只看钟表时间这一部分数据，你只会得到整个情况的一小部分。为了找出最佳的调整查询方法，你需要至少查看执行计划和IO统计信息。

此外，你需要排除其他导致性能问题的原因 - 例如，如果有多个查询存在普遍的性能问题，可能不仅仅是其中一个出现了故障 - 可能是架构问题、数据库上的重要并发活动，或甚至是基础硬件问题。

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

- Jeffrey Kemp

Answer 4

从11g开始，有一些额外的复杂性需要考虑。优化器预先进行峰值已经变得更加智能化，SQL计划稳定性也具有很大影响。这两个功能使数据库自动调整，但在性能测试期间也可能产生意想不到的影响，例如因为测试开始时并不知道和接受计划的所有变化。 这可能是第二次测试运行比第一次运行快得多而没有明显变化的原因。 自11g以来，编写逻辑正确的代码比性能测试更重要。例如，笛卡尔积和过滤一个不同的值可以是功能上正确的，但在大多数情况下是错误的代码，因为它提取比逻辑上需要的更多的数据。 如果查询获取了真正需要且处于正确控制结构中的数据，则数据库进程会在维护窗口期间调整代码。在许多情况下，测试环境和生产之间的差异如此之大，以至于无法安全地进行比较。 不要误解，测试很重要，但在11g之前主要是为了逻辑而不是性能测试，需要采取额外的步骤。 详见Oracle® Database 2 Day + Performance Tuning Guide 11g Release 2 (11.2)。

- user123664