Oracle 10 MERGEのパフォーマンス

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
ヒントを使用してみましたが、成功しませんでした。しかし、ステートメントを2つの部分に分割すると、最初に
SELECT サブクエリを実行し、結果(rank_id)を変数に格納してからマージします(MERGE …
USING SELECT user_id、rank_id FROM DUAL )src …
すべてがうまくいきます。私が読んだところでは、Oracleの MERGE
には既知の問題がありますが、それらは主にトリガーに関連しています(私の場合はトリガーなし)。また、
MERGEINSERTUPDATE
の組み合わせよりも遅く動作しますが、通常の違いは5-10% 30回ではない…

私が間違っていたことを理解しようとしています…あなたの提案をありがとう。

Update Execution plan is quite long to post it
here, in short : subquery cost by itself is 12737, with
merge – 76305. Stats output for merge
:

> Statistics

         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)

サブクエリのみ:

> Statistics

           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)
ベストアンサー

SQL *
Plus自動トレースを設定する
実際の実行計画のどの部分が最も物理的かつ論理的なI/Oを引き起こし、最も多くのメモリを使用したかを確認するのは数秒かかるでしょう。

このメソッドを使って得られる情報は、単純な explain plan よりはるかに正確です。

コメントする

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です