Very simple example:
Test case
set feed on; -- simple table: create table xt_curr1 as select level a,level b from dual connect by level<=1e4; -- same table but with empty trigger: create table xt_curr2 as select level a,level b from dual connect by level<=1e4; create or replace trigger tr_xt_curr2 before update on xt_curr2 for each row begin null; end; / set autot trace stat; update xt_curr1 set b=a; set autot off; set autot trace stat; update xt_curr2 set b=a; set autot off; set feed off drop table xt_curr1 purge; drop table xt_curr2 purge;
[collapse]
Update 2017/11/19: I have posted more detailed test cases with logfile dumps here.
SQL> -- simple table:
SQL> create table xt_curr1 as select level a,level b from dual connect by level<=1e4;
Table created.
SQL> -- same table but with empty trigger:
SQL> create table xt_curr2 as select level a,level b from dual connect by level<=1e4;
Table created.
SQL> create or replace trigger tr_xt_curr2 before update on xt_curr2 for each row
2 begin
3 null;
4 end;
5 /
Trigger created.
SQL> update xt_curr1 set b=a;
10000 rows updated.
Statistics
----------------------------------------------------------
25 recursive calls
10553 db block gets
91 consistent gets
18 physical reads
3101992 redo size
560 bytes sent via SQL*Net to client
491 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
10000 rows processed
SQL> update xt_curr2 set b=a;
10000 rows updated.
Statistics
----------------------------------------------------------
11 recursive calls
20384 db block gets
59 consistent gets
18 physical reads
4411724 redo size
560 bytes sent via SQL*Net to client
491 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
10000 rows processed
