Merge vs. Insert-Exception-Update

Recently I had a discussion with one of our developer about how to bring new rows in a table or, if the row already exists, update it. His approach was a PL/SQL procedure that first tries to insert a single row and if that fails because the row is already there, catch that exception and do an update instead. I told him, that this approach has at least three issues, that make it slow. First, it does things row-by-row, this is much more inefficient than set operations and it does not scale well. Second, it has lots of context switches between SQL and PL/SQL which are costly. And third, the exception handling is expensive too. My advise was to switch the code to use a single MERGE-statement. And to prove that I am right, I wrote a small example which I’ll share with you.

Let’s create two tables with identical structure, both will have an unique index and a trigger, that fills the ID using a sequence.

SQL> create unique index tm_txt_uq on test_merge(txt);

Index created.

SQL> create table test_except (
  2   id  number,
  3   txt varchar2(30),
  4   last_updated date,
  5   constraint pk_test_except primary key (id)
  6  )
  7  /

Table created.

SQL> create unique index te_txt_uq on test_except(txt);

Index created.

SQL> create sequence tm_seq;

Sequence created.

SQL> create sequence te_seq;

Sequence created.

SQL> create or replace trigger tm_bir
  2  before insert on test_merge
  3  for each row
  4  begin
  5    :new.id := tm_seq.nextval;
  6  end;
  7  /

Trigger created.

SQL> create or replace trigger te_bir
  2  before insert on test_except
  3  for each row
  4  begin
  5    :new.id := te_seq.nextval;
  6  end;
  7  /

Trigger created.

In the first run I populated both tables with 100k rows, since both tables are empty, this will lead to INSERTs of cause. And SQL*Plus will do the timing for me.

SQL> set timing on
SQL> merge into test_merge
  2  using (
  3    select 'Zeile ' || to_char(level) zeile from dual connect by level  set serveroutput on
SQL> declare
  2    l number;
  3    i number :=0;
  4    u number :=0;
  5  begin
  6    for l in 1..100000 loop
  7      begin
  8        insert into test_except (txt)
  9    values ('Zeile ' || to_char(l));
 10    i := i+1;
 11  exception
 12    when dup_val_on_index then
 13      update test_except set last_updated=sysdate where txt = 'Zeile ' || to_char(l);
 14      u := u+1;
 15      end;
 16    end loop;
 17    dbms_output.put_line(to_char(i) || ' rows inserted.');
 18    dbms_output.put_line(to_char(u) || ' rows updated.');
 19  end;
 20  /
100000 rows inserted.
0 rows updated.

PL/SQL procedure successfully completed.

Elapsed: 00:00:14.62

We see, that the MERGE is more than 2x faster than the PL/SQL approach. When I now run both statements a scond time, it will lead to UPDATEs since all the rows are already in the tables.

SQL> merge into test_merge
  2  using (
  3    select 'Zeile ' || to_char(level) zeile from dual connect by level  declare
  2    l number;
  3    i number :=0;
  4    u number :=0;
  5  begin
  6    for l in 1..100000 loop
  7      begin
  8        insert into test_except (txt)
  9    values ('Zeile ' || to_char(l));
 10    i := i+1;
 11      exception
 12    when dup_val_on_index then
 13      update test_except set last_updated=sysdate where txt = 'Zeile ' || to_char(l);
 14      u := u+1;
 15      end;
 16    end loop;
 17    dbms_output.put_line(to_char(i) || ' rows inserted.');
 18    dbms_output.put_line(to_char(u) || ' rows updated.');
 19  end;
 20  /
0 rows inserted.
100000 rows updated.

PL/SQL procedure successfully completed.

Elapsed: 00:00:55.36

The MERGE statement is even faster compared to the first run. That might be due to the fact, that there is less internal work to do, no index block splits etc. But the elapsed time of the PL/SQL aproach dicreased dramatically, it took 4x longer than it’s initial run and is now 11x slower then the MERGE.
So now it is really obvious, that the PL/SQL approach might not be the best performing solution. The MERGE solution maybe needs some more thinking and might not readable as the PL/SQL solution, but if done regularly one should get used to it.
The good thing is, I convinced my collegue.

Size of Flashback Logs

The todays blog post is about the Flashback Database feature inside the Oracle Database. I use this feature mostly in the context of Data Guard, but there are also other possible use cases like having a fallback during updates/upgrades etc. For Data Guard setups, the Flashback Database feature is simplyfies and speeds up a reinstantiation of a formerly primary database that got lost and a failover the standby database was performed. With Flashback Database there is no need to perform a complete duplication of the database to bring it up and running as a standby database. The database is simply rolled back to just before the SCN when the standby database was activated by the failover. It can then just start applying redo that it gets from the new primary database.
But how does it work? When activating this feature, the database writes some additional logs that cotain information to rollback the database. These logs are stored in the Fast Recovery Area and there is no way to influence that. Let’s have a look at that.

SQL> select group#, status, bytes from v$log;

    GROUP# STATUS                BYTES
---------- ---------------- ----------
         1 CURRENT          1073741824
         2 INACTIVE         1073741824
         3 INACTIVE         1073741824

SQL> alter database flashback on;

Database altered.

SQL> select * from v$flashback_database_logfile;

NAME                                                                                   LOG#    THREAD#  SEQUENCE#      BYTES FIRST_CHANGE# FIRST_TIME          TYPE          CON_ID
-------------------------------------------------------------------------------- ---------- ---------- ---------- ---------- ------------- ------------------- --------- ----------
/u03/app/oracle/fast_recovery_area/MMISI2/flashback/o1_mf_h1xp7lv0_.flb                   1          1          1 1073741824       3886676 2020-01-15 10:18:44 NORMAL             0
/u03/app/oracle/fast_recovery_area/MMISI2/flashback/o1_mf_h1xp7pnk_.flb                   2          1          1 1073741824             0                     RESERVED           0

SQL> !ls -l /u03/app/oracle/fast_recovery_area/MMISI2/flashback/
total 2097168
-rw-r----- 1 oracle dba 1073750016 Jan 15 10:22 o1_mf_h1xp7lv0_.flb
-rw-r----- 1 oracle dba 1073750016 Jan 15 10:18 o1_mf_h1xp7pnk_.flb

Obviously the logs are the same size as the redologs. And they consume space inside your FRA. But what if one does not have sufficient space and/or wants to shrink those flashback logs? Since the size is being inherited from the size of the redologs, let’s try shrinking the redologs by creating new groups and dropping the old ones.

SQL> alter database add logfile group 10 size 256m;

Database altered.

SQL> alter database add logfile group 11 size 256m;

Database altered.

SQL> alter database add logfile group 12 size 256m;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> alter database drop logfile group 1;

Database altered.

SQL> select group#, bytes, status from v$log;

    GROUP#      BYTES STATUS
---------- ---------- ----------------
        10  268435456 CURRENT
        11  268435456 UNUSED
        12  268435456 UNUSED

Now let’s check how the flashback logs look like:

SQL> select log#, thread#, sequence#, bytes, type from v$flashback_database_logfile;

      LOG#    THREAD#  SEQUENCE#      BYTES TYPE
---------- ---------- ---------- ---------- ---------
         1          1          1 1073741824 NORMAL
         2          1          1 1073741824 RESERVED

Still unchanged. May it requires some work to be done to cycle through the logs.

SQL> create table dings tablespace users as select * from dba_source;

Table created.

SQL> insert into dings select * from dings;

283698 rows created.

SQL> insert into dings select * from dings;

567396 rows created.

SQL> insert into dings select * from dings;

1134792 rows created.

SQL> insert into dings select * from dings;

2269584 rows created.

SQL> insert into dings select * from dings;

4539168 rows created.

SQL> commit;

Commit complete.

SQL> select group#, sequence#, bytes, status from v$log;

    GROUP#  SEQUENCE#      BYTES STATUS
---------- ---------- ---------- ----------------
        10         11  268435456 ACTIVE
        11         12  268435456 ACTIVE
        12         13  268435456 CURRENT

SQL> select log#, thread#, sequence#, bytes, type from v$flashback_database_logfile;

      LOG#    THREAD#  SEQUENCE#      BYTES TYPE
---------- ---------- ---------- ---------- ---------
         1          1          1 1073741824 NORMAL
         2          1          1 1073741824 RESERVED

Ok, no change here. There isn’t even a new flashback log. Maybe it wasn’t enough change? So let’s try some row-by-row deletion. And please do not do this in production, deleting rows can’t be more inefficient as that.

SQL> begin
   2   for rec in (select rowid from dings) loop
   3     delete from dings where rowid = rec.rowid;
   4     commit;
   5   end loop;
   6 end;
   7 /

PL/SQL procedure successfully completed.

SQL> select group#, sequence#, bytes, status from v$log;

    GROUP#  SEQUENCE#      BYTES STATUS
---------- ---------- ---------- ----------------
        10         53  268435456 CURRENT
        11         51  268435456 INACTIVE
        12         52  268435456 INACTIVE

SQL> select log#, thread#, sequence#, bytes, type from v$flashback_database_logfile;

      LOG#    THREAD#  SEQUENCE#      BYTES TYPE
---------- ---------- ---------- ---------- ---------
         1          1          1 1073741824 NORMAL
         2          1          1 1073741824 RESERVED

At lease this produced quite some redologs, but no new flashback logs. Maybe the “db_flashback_retention_target” parameter has an impact on these logs?

SQL> show parameter flashb

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     1440

SQL> alter system set db_flashback_retention_target=10;

System altered.

SQL> select log#, thread#, sequence#, bytes, type from v$flashback_database_logfile;

      LOG#    THREAD#  SEQUENCE#      BYTES TYPE
---------- ---------- ---------- ---------- ---------
         1          1          1 1073741824 NORMAL
         2          1          1 1073741824 RESERVED

Simply speaking, no, it hasn’t. Maybe other operations influence the generation of flashback logs, like index maintenance?

SQL> insert into dings select * from dba_source;

283698 rows created.

SQL> commit;

Commit complete.

SQL> create index dings_ix1 on dings(owner, name, line) tablespace new;

Index created.

SQL> insert into dings select * from dings;

283698 rows created.

SQL> insert into dings select * from dings;

567396 rows created.

SQL> insert into dings select * from dings;

1134792 rows created.

SQL> insert into dings select * from dings;

2269584 rows created.

SQL> commit;

Commit complete.

SQL> select group#, sequence#, bytes, status from v$log;

    GROUP#  SEQUENCE#      BYTES STATUS
---------- ---------- ---------- ----------------
        10         65  268435456 ACTIVE
        11         66  268435456 ACTIVE
        12         67  268435456 CURRENT

SQL> select log#, thread#, sequence#, bytes, type, first_time from v$flashback_database_logfile;

      LOG#    THREAD#  SEQUENCE#      BYTES TYPE      FIRST_TIME
---------- ---------- ---------- ---------- --------- -------------------
         3          1          3 1073741824 NORMAL    2020-01-15 11:27:47
         4          1          1 1073741824 RESERVED
         1          1          1 1073741824 NORMAL    2020-01-15 10:18:44
         2          1          2 1073741824 NORMAL    2020-01-15 11:26:36

SQL> !ls -l /u03/app/oracle/fast_recovery_area/MMISI2/flashback/
total 4194336
-rw-r----- 1 oracle dba 1073750016 Jan 15 11:26 o1_mf_h1xp7lv0_.flb
-rw-r----- 1 oracle dba 1073750016 Jan 15 11:27 o1_mf_h1xp7pnk_.flb
-rw-r----- 1 oracle dba 1073750016 Jan 15 11:28 o1_mf_h1xt6z4y_.flb
-rw-r----- 1 oracle dba 1073750016 Jan 15 11:27 o1_mf_h1xt95vj_.flb

Ok, finally there are some new flashback logs. But the size of those logs is still at 1GB.
I am running out of ideas, the last chance is to disable and enable the flashback datbae feautre.

SQL> alter database flashback off;

Database altered.

SQL> alter database flashback on;

Database altered.

SQL> select log#, thread#, sequence#, bytes, type, first_time from v$flashback_database_logfile;

      LOG#    THREAD#  SEQUENCE#      BYTES TYPE      FIRST_TIME
---------- ---------- ---------- ---------- --------- -------------------
         1          1          1  268435456 NORMAL    2020-01-15 11:30:01
         2          1          1  268435456 RESERVED

SQL> !ls -l /u03/app/oracle/fast_recovery_area/MMISI2/flashback/
total 524304
-rw-r----- 1 oracle dba 268443648 Jan 15 11:30 o1_mf_h1xtf8yz_.flb
-rw-r----- 1 oracle dba 268443648 Jan 15 11:30 o1_mf_h1xtfck6_.flb

Here we go, the flashback logs are finally shrinked. But with the consequences of loosing all the flashback information that was previously there. Keep that in mind and schedule wisely when changing the size of your flashback logs.