Oracle 10 MERGE 性能

Question

我在 Oracle 10 中遇到了一个与 MERGE 性能相关的非常奇怪的问题。简而言之，我有一个存储过程，它根据用户在系统中的事件计算和存储用户排名，并且只包含一个 MERGE 语句:

MERGE INTO user_ranks target USING 
([complex query that returns rank_id and user_id])src ON 
(src.user_id = target.user_id)
WHEN MATCHED THEN UPDATE SET target.rank_id = src.rank_id
WHEN NOT MATCHED THEN INSERT (target.user_id, target.rank_id)
  VALUES (src.user_id, src.rank_id);

// user_ranks table structure:
CREATE TABLE user_ranks (user_id INT NOT NULL 
PRIMARY KEY USING INDEX (CREATE UNIQUE INDEX UQ_uid_uranks ON user_ranks(user_id)),
rank_id INT NOT NULL,
CONSTRAINT FK_uid_uranks FOREIGN KEY (user_id) REFERENCES users(id),
CONSTRAINT FK_rid_uranks FOREIGN KEY(rank_id) REFERENCES ranks(id));
// no index on rank_id - intentionally, ranks table is a lookup with 
// a very few records and no delete/update allowed

用作 MERGE 源的子查询最多返回 1 条记录(user_id 作为参数传递给过程)。它相当昂贵，但执行时间是可以接受的(1-1.2 秒)。问题是 MERGE 执行时间增加到 40 多秒，我不知道为什么。我尝试使用 LEADING 提示但没有成功。但是，如果我将语句分成两部分，第一个部分 - 运行 SELECT 子查询并将结果(rank_id)存储到变量中，然后合并 - MERGE ... USING (SELECT user_id, rank_id FROM DUAL)src ... 一切正常。从我读到的内容来看，Oracle 的 MERGE 存在已知问题，但它们主要与触发器有关(在我的情况下没有触发器)。它还说 MERGE 的工作速度比 INSERT 和 UPDATE 的组合慢，但我相信“正常”差异约为 5-10%，而不是 30 倍......

我试图了解我做错了什么...感谢您的建议。

更新
执行计划在这里发布很长，简而言之:子查询本身的成本是 12737， merge - 76305。 merge 的统计输出:

> 统计

         108  recursive calls
           4  db block gets
    45630447  consistent gets
       24905  physical reads
           0  redo size
         620  bytes sent via SQL*Net to client
        1183  bytes received via SQL*Net from client
           2  SQL*Net roundtrips to/from client
           2  sorts (memory)
           0  sorts (disk)

单独的子查询:

> 统计

           8  recursive calls
           0  db block gets
          34  consistent gets
           0  physical reads
           0  redo size
         558  bytes sent via SQL*Net to client
         234  bytes received via SQL*Net from client
           1  SQL*Net roundtrips to/from client
           1  sorts (memory)
           0  sorts (disk)

Answer 1

如果您有 set up SQL*Plus autotrace只需几秒钟即可查看实际执行计划的哪一部分导致了最多的物理和逻辑 I/O 并使用了最多的内存。

请注意，使用此方法获得的信息比简单的 explain plan 精确得多。 .