Redo and undo volumes - take two

articles: 

Following a question on OTN https://community.oracle.com/message/12801175 I did another test on redo and undo, just to prove that frequent COMMIT can be bad for performance. The results surprised me. I expected that row-by-row commit would be worse then a single commit at the end of a multi-row transaction, but I hadn't expected it to be this bad. As well as being much slower, both undo and redo volumes are vastly greater.

This is my test script, that performs a million updates: first by commiting each update, then by commiting them all in one transaction.

conn scott/tiger
set timing on
select value from v$mystat natural join v$statname where name='redo size';
select value from v$mystat natural join v$statname where name='undo change vector size';
begin
for i in 1..1000000 loop
update dept set dname=dname where deptno=10;
commit;
end loop;
end;
/
select value from v$mystat natural join v$statname where name='redo size';
select value from v$mystat natural join v$statname where name='undo change vector size';

conn scott/tiger
set timing on
select value from v$mystat natural join v$statname where name='redo size';
select value from v$mystat natural join v$statname where name='undo change vector size';
begin
for i in 1..1000000 loop
update dept set dname=dname where deptno=10;
end loop;
commit;
end;
/
select value from v$mystat natural join v$statname where name='redo size';
select value from v$mystat natural join v$statname where name='undo change vector size';

My figures for the row-by-row transactions are,
redo, 561MB
undo, 160MB
time, 52 seconds

And for the single transaction,
redo, 273MB
undo, 92MB
time, 19 seconds

We all know that row-by-row equals slow-by-slow, but I had not realized that it was this bad.

Tests done on 12.1.0.2, Windows
--
John Watson
Oracle Certified Master DBA
http://skillbuilders.com