How to count the inserts and updates from merge ?

This blog will make two attempts to count the inserts and updates from merge without changing merge statement. One is by programming, another by Oracle stats. There are some discussions on this topic in AskTom: How to count the inserts and updates from merge.

Note: Tested in Oracle 19c

1. Test Setup

drop table source_tab;
drop table target_tab;
 
-- 7000 rows, x from 1 to 7000
create table target_tab as select level x, rpad('TTT', 2000, 'X') y from dual connect by level <= 7000;
 
-- 5000 rows, x from 3001 to 8000
create table source_tab as select 3000+level x, rpad('SSS', 2000, 'X') y from dual connect by level <= 5000;
 
alter table target_tab add (constraint target_tab#p primary key (x));
alter table source_tab add (constraint source_tab#p primary key (x));
 
exec dbms_stats.gather_table_stats(user, 'TARGET_TAB', cascade=> true);
exec dbms_stats.gather_table_stats(user, 'SOURCE_TAB', cascade=> true);

2. Test Run

declare
  l_time          date;
  l_merge_cnt     number;
  l_before_cnt    number;
  l_after_cnt     number;
  l_before_change number;
  l_before_insert number;
  l_after_change  number;
  l_after_insert  number;
  l_upd_oracle    number;
begin
  l_time := sysdate;
  select count(*) into l_before_cnt from target_tab;  -- assume no other DML on target_tab
 
  select b.value into l_before_change from v$statname a, v$mystat b where a.statistic# = b.statistic# and name = 'HSC Heap Segment Block Changes';
  select b.value into l_before_insert from v$statname a, v$mystat b where a.statistic# = b.statistic# and name =  'Heap Segment Array Inserts';
 
  merge /*+ index(a target_tab#p) */ into target_tab a
        --using source_tab b
        using (select /*+ index(t source_tab#p) */ * from source_tab t where x > 4000)  b
    on (a.x = b.x)
    when matched then
      update set a.y       = b.y
    when not matched then
      insert (x, y) values (b.x, b.y);
     
  l_merge_cnt := sql%rowcount;
 
  select b.value into l_after_change from v$statname a, v$mystat b where a.statistic# = b.statistic# and name = 'HSC Heap Segment Block Changes';
  select b.value into l_after_insert from v$statname a, v$mystat b where a.statistic# = b.statistic# and name =  'Heap Segment Array Inserts';
 
  --select count(*) into l_before_cnt from target_tab as of timestamp l_time;  -- sys.smon_scn_time.TIME_DP is DATE type
  select count(*) into l_after_cnt from target_tab;
 
  dbms_output.put_line('Merge sql%rowcount = '||l_merge_cnt);
  dbms_output.put_line('Table Rows before  = '||l_before_cnt);
  dbms_output.put_line('Table Rows after   = '||l_after_cnt);
 
  dbms_output.put_line('============= inserts and updates by Programmer =============');
  dbms_output.put_line('Insert Rows = '|| (l_after_cnt - l_before_cnt));
  dbms_output.put_line('Update Rows = '|| (l_merge_cnt - (l_after_cnt - l_before_cnt)));
 
  dbms_output.put_line('============= inserts and updates by Oracle =============');
  l_upd_oracle := (l_after_change  - l_before_change) - (l_after_insert - l_before_insert);
  dbms_output.put_line('Insert Rows = '|| (l_merge_cnt - l_upd_oracle));
  dbms_output.put_line('Update Rows = '|| l_upd_oracle);
 
  rollback;
end;
/

3. Test Output

Merge sql%rowcount = 4000
Table Rows before  = 7000
Table Rows after   = 8000
 
============= inserts and updates by Programmer =============
Insert Rows = 1000
Update Rows = 3000
 
============= inserts and updates by Oracle =============
Insert Rows = 1000
Update Rows = 3000