Oracle SQL
  • LICENSE

How even empty trigger increases redo generation

Posted on September 22, 2016 by Sayan Malakshinov Posted in oracle 2 Comments

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
trigger
« 8 queens chess problem: solution in Oracle SQL
row pieces, 255 columns, intra-block row chaining in details »
photo Sayan Malakshinov

Oracle ACE Pro Oracle ACE Pro

DEVVYOracle Database Developer Choice Award winner

Oracle performance tuning expert

UK / Cambridge

LinkedIn   Twitter
sayan@orasql.org

Recent Posts

  • CBO and Partial indexing
  • Slow index access “COL=:N” where :N is NULL
  • Where does the commit or rollback happen in PL/SQL code?
  • :1 and SP2-0553: Illegal variable name “1”.
  • ORA exceptions that can’t be caught by exception handler

Recent Comments

  • Oracle SGA 값을 증가 시킬 때 발생 장애 원인 – DBA의 정석 on Example of controlling “direct path reads” decision through SQL profile hints (index_stats/table_stats)
  • Oracle SQL | Oracle diagnostic events — Cheat sheet on Where does the commit or rollback happen in PL/SQL code?
  • Functions & Subqueries | Oracle Scratchpad on Deterministic function vs scalar subquery caching. Part 3
  • Materialized views state turns into compilation_error after refresh - kranar.top - Answering users questions... on Friday prank: select from join join join
  • Exadata Catalogue | Oracle Scratchpad on When bloggers get it wrong – part 1
  • Exadata Catalogue | Oracle Scratchpad on Serial Scans failing to offload
  • lateral join – decorrelation gone wrong – svenweller on Lateral view decorrelation(VW_DCL) causes wrong results with rownum
  • 255 column catalogue | Oracle Scratchpad on Intra-block row chaining optimization in 12.2
  • 255 column catalogue | Oracle Scratchpad on row pieces, 255 columns, intra-block row chaining in details
  • opt_estimate catalogue | Oracle Scratchpad on Correct syntax for the table_stats hint

Blogroll

  • Alex Fatkulin
  • Alexander Anokhin
  • Andrey Nikolaev
  • Charles Hooper
  • Christian Antognini
  • Coskan Gundogar
  • David Fitzjarrell
  • Igor Usoltsev
  • Jonathan Lewis
  • Karl Arao
  • Mark Bobak
  • Martin Bach
  • Martin Berger
  • Neil Chandler
  • Randolf Geist
  • Richard Foote
  • Riyaj Shamsudeen
  • Tanel Poder
  • Timur Akhmadeev
  • Valentin Nikotin

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org
©Sayan Malakshinov. Oracle SQL