Oracle MERGE Performance Issues

 

The Oracle "merge" statement combines an insert and update which is why it's also called an "upsert".  At a high level the merge statement simplifies the syntax, which is essentially a simple "insert, if already exists, update".

Sadly, there are some serious performance issues with MERGE, as noted here.

The biggest problem with Oracle merge performance relates to triggers.  The repeated firing of triggers can wreak havoc and make merge performance miserable.

One DBA notes that it's sometimes better to write your own procedural alternative:

"If there is an alternative mechanism for avoiding the MERGE then I'd be very glad to hear it, but this method may stand on its own if the alternatives do not allow direct path operations on the MV table or the sorting of the table data."

Also, Adrian Billington notes that a custom SQL procedure is faster than the merge statement:

"We can see that MERGE performed less well than our two-part SQL solution; with it taking over twice as long. It generated more redo and used more latches."

SQL> exec runstats_pkg.rs_start();

PL/SQL procedure successfully completed.

SQL> MERGE
  2     INTO  target_table tgt
  3     USING source_table src
  4     ON  ( src.object_id = tgt.object_id )
  5  WHEN MATCHED
  6  THEN
  7     UPDATE
  8     SET   tgt.object_name = src.object_name
  9     ,     tgt.object_type = src.object_type
 10  WHEN NOT MATCHED
 11  THEN
 12     INSERT ( tgt.object_id
 13            , tgt.object_name
 14            , tgt.object_type )
 15     VALUES ( src.object_id
 16            , src.object_name
 17            , src.object_type );

86889 rows merged.

Now we will pause runstats, rollback the merged data to reset it, and resume our resource snapshots.

SQL> exec runstats_pkg.rs_pause();

PL/SQL procedure successfully completed.

SQL> ROLLBACK;

Rollback complete.

SQL> exec runstats_pkg.rs_resume();

PL/SQL procedure successfully completed.

We will now run a bulk update and insert as separate SQL statements. The update is written as an updateable in-line view which is often the fastest technique for bulk updating one table from another. The sqlplus feedback gives us the breakdown of the previous merge rowcount.

SQL> UPDATE ( SELECT src.object_name AS src_name
  2           ,      src.object_type AS src_type
  3           ,      tgt.object_name AS tgt_name
  4           ,      tgt.object_type AS tgt_type
  5           FROM   source_table src
  6           ,      target_table tgt
  7           WHERE  src.object_id = tgt.object_id )
  8  SET tgt_name = src_name
  9  ,   tgt_type = src_type;

43485 rows updated.

SQL> INSERT INTO target_table tgt
  2  SELECT *
  3  FROM   source_table src
  4  WHERE  NOT EXISTS ( SELECT NULL
  5                      FROM   target_table tgt
  6                      WHERE  src.object_id = tgt.object_id );

43404 rows created.

For more details from Adrian Billington visit

http://www.oracle-developer.net/display.php?id=203 

---