为什么此 SQL 在具有相同硬件/数据的 Oracle 服务器中的运行速度比另一个 Oracle 服务器快得多?

为什么此 SQL 在具有相同硬件/数据的 Oracle 服务器中的运行速度比另一个 Oracle 服务器快得多?

我的应用程序中有一条 SQL,在生产环境中的 Oracle 服务器上运行大约需要 30 分钟。在测试 Oracle 服务器上运行所需的时间大致相同。

由于某种原因,在另一个 Oracle 服务器中,它运行得更快:仅 5 分钟左右!

这些时间实际上仅适用于 SQL(没有应用程序处理开销)。我从 Oracle Enterprise Manager 获得它们。而且,它们是一致的,即,如果您再次运行 SQL,您将获得大致相同的时间。

这三台服务器的硬件、Oracle版本(10g)、数据都相同。甚至SQL执行计划也完全相同。

什么使得 SQL 在该测试环境中运行得如此之快?

该 SQL 是一个合并:

MERGE /*+ USE_NL(DORMANT_POINTS) */
INTO MKT_CURVE_POINT DORMANT_POINTS
USING (SELECT
// big select   
) ACTIVE_POINTS
    ON (
// ..
)
WHEN MATCHED THEN
UPDATE
SET DORMANT_POINTS.ACTIVE_PARENT_PRICE = ACTIVE_POINTS.ACTIVE_PARENT_PRICE
WHERE DORMANT_POINTS.ACTIVE_PARENT_PRICE <>
ACTIVE_POINTS.ACTIVE_PARENT_PRICE;

我怀疑这是与缓存有关的问题。我注意到,在运行 SQL 速度较快的服务器中,缓冲区获取次数与物理读取次数相比较高。在运行速度较慢的服务器中,该比率较低。

如何解释这种巨大的性能差异?

答案1

并发性、锁定和闩锁可能起到一定作用。我猜生产服务器除了等待这个特定查询外还在做其他事情?

内存策略是否设置为自动?生产服务器可能以不同的方式分配了其 SGA 池。

这也是一个关于共享池和缓冲区缓存中实际有哪些数据的问题。测试服务器的缓冲区中可能有更多的相关数据,不会被其他生产查询刷新。

然后是硬件配置。一些简单的东西,比如写回缓存,就能带来巨大的差异。

虽然很有趣,但我们不要把时间都浪费在猜测上。跟踪查询并查看到底发生了什么 :)

答案2

需要考虑的事项:

  1. 初始化参数是否相同?您暗示缓存……这可能很重要。
  2. 每台服务器的相对负载是多少?“快速”服务器是否几乎不使用,因此数据不会很快从缓存中过期?
  3. 硬件真的一模一样吗?微小的差异(例如 RAID 控制器)可能会造成巨大的差异。

欢迎来到性能调优的世界 :-/

答案3

“甚至 SQL 执行计划都完全相同。”

首先,您如何知道这一点。在 10g 中,您应该在 v$sql 中找到游标,然后在对 DBMS_XPLAN.DISPLAY_CURSOR 的调用中使用 sql_id。这显示了实际使用的解释计划,而不是 EXPLAIN PLAN 语句,后者是对可能使用什么计划的预测。

“我注意到,在运行 SQL 的服务器中,缓冲区获取的数量与物理读取的数量相比要高得多”

愚蠢的问题,但是数据相同吗?

如果“快速设备”的缓冲获取量比“慢速设备”少,则其处理的数据较少。如果“快速设备”的缓冲获取量比“慢速设备”多,则其处理的数据较多。

不要专注于物理读取本身。如果数据和查询计划相同,则相同的逻辑块将以相同的顺序进行处理。如果它们恰好在缓存中并且不需要物理读取,它将运行得更快,但这并不是您真正可以控制的。

理论上,这种情况可能表明快速机器上用于缓存的内存比慢速机器上多,但如果硬件相同,则意味着您的生产和测试实例在硬件级别有可用内存,但数据库配置为不使用它,这种情况不太可能发生。更有可能的是,在这些机器上运行的其他程序已将不同的数据块强制放入缓存中。

答案4

你的解释计划可能会有所不同。

做这个:

设置 200 行解释计划

您的 SQL 将不会执行。您将收到一条消息

“解释”

然后这样做

从表 (dbms_xplan.display) 中选择*;

在两个数据库中执行此操作并进行比较。这是 oracle 访问表的方式。很可能它们是不同的。

确保您的表在两个数据库和索引中都经过分析,并且数据相同。

相关内容