Post date: Nov 4, 2014 2:19:17 PM
Updating values in a table will cause redo to be generated even if the new value is exactly the same as the old value. Oracles does not perform any smart optimisation to remove this fact.
Only update fields where the values have been changed! If possible.
The "get_stat_val" function is from the Expert Oracle Database Architecture book so I will not list it here. It is however really easy to implement, just create a function that return the current value of the givet stat name from v$mystat. You will need to join v$mystat to v$statname to get the name of the stat.
set serveroutput on set feedback off clear screen drop table peter; create table peter(a number primary key, b timestamp, c varchar2(20),d char(1) check (d in ('t','f')) ); insert into peter values (1, '2014-01-01', 'hejsan peter', 'f'); insert into peter values (2, '2014-02-01', 'hej peter', 't'); insert into peter values (3, '2014-03-01', 'hallå peter', 't'); select * from peter; variable redo number -- set only the value that changed exec :redo := get_stat_val('redo size'); update peter set d = 'f' where a = 3; exec dbms_output.put_line( (get_stat_val('redo size')-:redo) || ' bytes of redo generated on changing one field ...'); -- change only d (logicaly change only d but set all other values to same value as it was exec :redo := get_stat_val('redo size'); update peter set a = 2, b = '2014-02-01', c = 'hej peter', d = 'f' where a = 2; exec dbms_output.put_line( (get_stat_val('redo size')-:redo) || ' bytes of redo generated on setting all fields ...'); -- Try to be clever and hope that Oracle does not change values that do not need changing. exec :redo := get_stat_val('redo size'); update peter set a = case when a = 2 then a else 2 end, b = case when b = to_timestamp('2014-02-01') then b else to_timestamp('2014-02-01') end, c = case when c = 'hej peter' then c else 'hej peter' end, d = case when d = 'f' then d else 'f' end where a = 2; exec dbms_output.put_line( (get_stat_val('redo size')-:redo) || ' bytes of redo generated while "clever" update ...'); commit;
Results:
A B C D ---------- ----------------------------- -------------------- - 1 2014-01-01 00:00:00,000000000 hejsan peter f 2 2014-02-01 00:00:00,000000000 hej peter t 3 2014-03-01 00:00:00,000000000 hallå peter t 264 bytes of redo generated on changing one field ... 304 bytes of redo generated on setting all fields ... 304 bytes of redo generated while clever update ...