对比DML操作产生的REDO及UNDO大小

结论：相同数据量的数据操作：

INSERT产生最少REDO；UPDATA首先的REDO居中；DELETE产生最多REDO。

DELETE产生UNDO最多，UPDATE次之，INSERT产生UNDO最少。

DML操作数据未COMMIT时，无查询到REDO信息变化-----这是10G后Oracle的新特性：IMU。
Redo先放在共享池中，Commit时，再传入Log Buffer。只有当日志传入Log Buffer时，Oracle的Redo统计资料才会增加。
这样做的目的，是为了减少向Log Buffer传数据的次数，减少Log BUffer相关Latch的竞争。

实验如下：

1.INSERT产生的REDO UNDO

SQL> select name,value from (select b.name,a.value from v$mystat a,v$statname b where a.STATISTIC#=b.statistic#) where  name='redo size' or name like 'undo change%';
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                                 0
undo change vector size                                                   0
SQL> select * from test;
A
----------
 
SQL> insert into test select rownum from dual connect by rownum<=10;
10 rows inserted
SQL> commit;
Commit complete
SQL> select name,value from (select b.name,a.value from v$mystat a,v$statname b where a.STATISTIC#=b.statistic#) where  name='redo size' or name like 'undo change%';
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                               576
undo change vector size                                                 156
SQL> select 576-0 as redo_size_insert_change,156-0 as undo_size_insert_change from dual;
REDO_SIZE_INSERT_CHANGE UNDO_SIZE_INSERT_CHANGE
----------------------- -----------------------
                    576                     156

2.UPDATE产生的UNDO REDO

SQL> select name,value as bytes from (select b.name,a.value from v$mystat a,v$statname b where a.STATISTIC#=b.statistic#) where  name='redo size' or name like 'undo change%';
 
NAME                                                                  BYTES
---------------------------------------------------------------- ----------
redo size                                                               576
undo change vector size                                                 156
SQL> select * from test;
A
----------
1
2
3
4
5
6
7
8
9
10
 
SQL> update test set a=a+100;
10 rows updated
SQL> commit;
Commit complete
SQL> select name,value as bytes from (select b.name,a.value from v$mystat a,v$statname b where a.STATISTIC#=b.statistic#) where  name='redo size' or name like 'undo change%';
NAME                                                                  BYTES
---------------------------------------------------------------- ----------
redo size                                                              3124
undo change vector size                                                1104
SQL> select * from test;
A
----------
101
102
103
104
105
106
107
108
109
110
SQL> select 3124-576 as redo_size_update_change,1104-156 as undo_size_update_change from dual;
 
REDO_SIZE_UPDATE_CHANGE UNDO_SIZE_UPDATE_CHANGE
----------------------- -----------------------
                   2548                     948

3.DELETE产生REDO UNDO

SQL> select name,value as bytes from (select b.name,a.value from v$mystat a,v$statname b where a.STATISTIC#=b.statistic#) where  name='redo size' or name like 'undo change%';
NAME                                                                  BYTES
---------------------------------------------------------------- ----------
redo size                                                              3124
undo change vector size                                                1104
SQL> select * from test;
A
----------
101
102
103
104
105
106
107
108
109
110
SQL> delete test;
10 rows deleted
SQL> commit;
Commit complete
SQL> select * from test;
A
----------
 
SQL> select name,value as bytes from (select b.name,a.value from v$mystat a,v$statname b where a.STATISTIC#=b.statistic#) where  name='redo size' or name like 'undo change%';
NAME                                                                  BYTES
---------------------------------------------------------------- ----------
redo size                                                              5628
undo change vector size                                                2212
SQL> select 5628-3124 as redo_size_delete_change,2212-1104 as undo_size_delete_change from dual;
 
REDO_SIZE_DELETE_CHANGE UNDO_SIZE_DELETE_CHANGE
----------------------- -----------------------
                   2504                    1108