Redo and undo volumes - take two
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
- John Watson's blog
- Log in to post comments