Redo and undo volumes
Submitted by John Watson on Sun, 2014-08-31 10:17
Results:
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:
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
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
»
- John Watson's blog
- Log in to post comments