Redo and undo volumes

articles: 

How much undo and redo does Oracle generate for different operations? More than you might think.

I see questions on the forums not infrequently regarding how much redo and/or undo different DMLs might generate, such as this on AskTom,
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5663330700346736253
or this on OTN,
https://community.oracle.com/thread/3602460
so I thought I would run a test. This script inserts, updates, and deletes 10MB of data: a million rows, 10bytes each.

drop table t1;
create table t1(c1 varchar2(10));
select 'redo: '||value from v$mystat natural join v$statname where name = 'redo size';
select 'undo: '||value from v$mystat natural join v$statname where name = 'undo change vector size';
insert into t1 select '1234567890' from dual connect by level <= 1000000;
select 'redo: '||value from v$mystat natural join v$statname where name = 'redo size';
select 'undo: '||value from v$mystat natural join v$statname where name = 'undo change vector size';
update t1 set c1='abcdefghij';
select 'redo: '||value from v$mystat natural join v$statname where name = 'redo size';
select 'undo: '||value from v$mystat natural join v$statname where name = 'undo change vector size';
delete from t1;
select 'redo: '||value from v$mystat natural join v$statname where name = 'redo size';
select 'undo: '||value from v$mystat natural join v$statname where name = 'undo change vector size';

Results:
	insert	update	delete
redo,	22.3MB	133.6MB 261.5MB
undo,	2.4MB	59.8MB	112.0MB

For delete, undo generation is more than ten times the volume of data affected; redo generation is more than twenty times. This test is distorted by the fact that the rows are so small so I repeated the test for rows of 100bytes, representing 100MB of user data:
	insert	update	delete
redo,	120.9MB	307.1MB	353.2MB
undo,	3.8MB	147.8MB	200.0MB

Now the figures are a more reasonable ratio of the size of the data, but even so, I had not expected the figures to be so high. This is the price we pay for the mechanism of undo and redo that Oracle introduced in release 6. It may be the best implementation of the ACID test yet developed on the planet, but it is not without cost.
(Tests done on release 12.1.0.1, on Windows)
--
John Watson
Oracle Certified Master DBA
http://skillbuilders.com