我的应用程序中有一条 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
需要考虑的事项:
- 初始化参数是否相同?您暗示缓存……这可能很重要。
- 每台服务器的相对负载是多少?“快速”服务器是否几乎不使用,因此数据不会很快从缓存中过期?
- 硬件真的一模一样吗?微小的差异(例如 RAID 控制器)可能会造成巨大的差异。
欢迎来到性能调优的世界 :-/
答案3
“甚至 SQL 执行计划都完全相同。”
首先,您如何知道这一点。在 10g 中,您应该在 v$sql 中找到游标,然后在对 DBMS_XPLAN.DISPLAY_CURSOR 的调用中使用 sql_id。这显示了实际使用的解释计划,而不是 EXPLAIN PLAN 语句,后者是对可能使用什么计划的预测。
“我注意到,在运行 SQL 的服务器中,缓冲区获取的数量与物理读取的数量相比要高得多”
愚蠢的问题,但是数据相同吗?
如果“快速设备”的缓冲获取量比“慢速设备”少,则其处理的数据较少。如果“快速设备”的缓冲获取量比“慢速设备”多,则其处理的数据较多。
不要专注于物理读取本身。如果数据和查询计划相同,则相同的逻辑块将以相同的顺序进行处理。如果它们恰好在缓存中并且不需要物理读取,它将运行得更快,但这并不是您真正可以控制的。
理论上,这种情况可能表明快速机器上用于缓存的内存比慢速机器上多,但如果硬件相同,则意味着您的生产和测试实例在硬件级别有可用内存,但数据库配置为不使用它,这种情况不太可能发生。更有可能的是,在这些机器上运行的其他程序已将不同的数据块强制放入缓存中。
答案4
你的解释计划可能会有所不同。
做这个:
设置 200 行解释计划
您的 SQL 将不会执行。您将收到一条消息
“解释”
然后这样做
从表 (dbms_xplan.display) 中选择*;
在两个数据库中执行此操作并进行比较。这是 oracle 访问表的方式。很可能它们是不同的。
确保您的表在两个数据库和索引中都经过分析,并且数据相同。