We recently had an issue where a particular insert was generating a large amount of CPU time requirements. When we examined the PL/SQL code, we found the developer had coded the following…
begin
insert into table values(pk);
exception
when dup_val_on_index then
null;
end;
/
We found the insert was executed over 25 million times per half hour, with only 600 rows actually being inserted. In other words, 99.999% of the time, the exception handler was being fired and any changes generated (for undo, at a minimum) for the insert up to that point needed to be rolled back.
The purpose of this post is to provide an argument for using a MERGE SQL statement for a single row insert to handle this type of situation. We start with the current code and insert the same primary key value (or attempt to) 100,000 times…
SQL> create table t0301(c number primary key);
Table created.
SQL> select value from v$mystat where statistic# = (select statistic# from v$statname where name = 'redo size');
VALUE
----------
21688
SQL> select value from v$mystat where statistic# = (select statistic# from v$statname where name = 'CPU used by this session');
VALUE
----------
3
SQL> begin
2 for i in 1..100000 loop
3 begin
4 insert into t0301 values(1);
5 exception
6 when dup_val_on_index then
7 null;
8 end;
9 end loop;
10 end;
11 /
PL/SQL procedure successfully completed.
SQL> select value from v$mystat where statistic# = (select statistic# from v$statname where name = 'CPU used by this session');
VALUE
----------
2916
SQL> select value from v$mystat where statistic# = (select statistic# from v$statname where name = 'redo size');
VALUE
----------
45226476
SQL> drop table t0301 purge;
Table dropped.
SQL> exit
We then ran SQL code that was similar functionally, but replaced the INSERT with a MERGE…
SQL> create table t0301(c number primary key);
Table created.
SQL> select value from v$mystat where statistic# = (select statistic# from v$statname where name = 'redo size');
VALUE
----------
21748
SQL> select value from v$mystat where statistic# = (select statistic# from v$statname where name = 'CPU used by this session');
VALUE
----------
2
SQL> begin
2 for i in 1..100000 loop
3 begin
4 merge into t0301 a
5 using (select 1 c from dual) b
6 on (a.c = b.c)
7 when not matched then
8 insert values(1);
9 end;
10 end loop;
11 end;
12 /
PL/SQL procedure successfully completed.
SQL> select value from v$mystat where statistic# = (select statistic# from v$statname where name = 'CPU used by this session');
VALUE
----------
350
SQL> select value from v$mystat where statistic# = (select statistic# from v$statname where name = 'redo size');
VALUE
----------
23588
SQL>
As you can see, the INSERT generated about 45MB of redo, whereas the MERGE generated almost none. Also, the INSERT generated over 29 seconds of CPU utilization, while the MERGE was about three seconds.