I had a very strange performance problem with MERGE in Oracle 10. In a few words, I have a stored procedure that calculates and saves the user rating based on its activity in the system and contains only one 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
The subquery, which is used as the source for MERGE , returns no more than 1 record (user_id is passed as a procedure parameter). It is quite expensive, but the execution time is acceptable (1-1.2 seconds). The problem is that MERGE runtime increases to over 40 seconds, and I don't know why. I tried using the LEADING hint without success. But if I divided the expression into 2 parts, first one - run the SELECT subquery and save the result (rank_id) into a variable, and then change - MERGE ... USING (SELECT user_id, rank_id FROM DUAL)src ... everything works fine. From what I read, there are known issues with Oracle MERGE , but they are mostly related to triggers (in my case there are no triggers). It also says that MERGE is slower than the combination of INSERT and UPDATE , but I believe that the “normal” difference is about 5-10%, not 30 times ...
I am trying to understand what I did wrong ... Thanks for your suggestions.
Update The execution plan is quite long to publish it here, in short: the cost of the subquery itself is 12737, with MERGE - 76305. Statistics 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)
subquery only:
> 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)