Very simple example:
Test case
[sourcecode language=”sql”]
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;
[/sourcecode]
[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