【IT168 技术文档】IMU是10g引入的一项新技术，并且是Oracle的专利技术。但是，在10g中似乎没有完全激活，以下的测试在10.2.0.3中无法通过，在11g中可以进行。
在传统的事务更新过程中，如果一条数据记录被更新，就会从buffer cache中读取/分配一块UNDO数据块，并且立即会写入一条UNDO条目。如果同一个事务中有多条记录被更新，则undo buffer数据块中就会写入多条undo条目。引入IMU后，会从shared pool中分配出一个新的内存池——IMU pool。当一条数据记录被更新，仍然会从buffer cache中读取/分配一块undo数据块，但是，这块undo块并不会立即被更新，而是会在IMU pool中产生一个IMU node，IMU节点通过IMU map与数据记录更新对应。如果事务中有多条记录被修改，则IMU pool中就生产多个IMU nodes，而buffer中的undo block不会发生任何变化。当发生IMU commit或IMU flush时，才会通过IMU map将这些IMU node记录的undo信息写入undo buffer block中。并且，所有这些redo信息会和commit vector一起作为一个Redo条目写入Redo log中。整个过程中UNDO所产生的redo信息则大大减少。
隐含参数_in_memory_undo用于控制IMU特性的开关，可以在会话/系统级立即生效，默认为true。另外一个隐含参数_IMU_pools则控制IMU pool的数量，默认为3。此外，目前IMU的使用还存在一些限制，如undo管理方式(undo_management)必须为auto，在RAC中无效，
--建立测试表、数据
HELLODBA.COM>create table ttt (a number, b varchar2(20));
Table created.
HELLODBA.COM>begin
2 for i in 1..2000 loop
3 insert into ttt values (i, ''||i);
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
HELLODBA.COM>select a
2 from (select a, dbms_rowid.rowid_block_number(ROWID) block_id, lag(dbms_rowid.rowid_block_number(ROWID)) over (order by rowid) as pre_block_id from ttt)
3 where block_id != pre_block_id;
A
----------
1124
1643
1
HELLODBA.COM>create table ttt (a number, b varchar2(20));
Table created.
HELLODBA.COM>begin
2 for i in 1..2000 loop
3 insert into ttt values (i, ''||i);
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
HELLODBA.COM>select a
2 from (select a, dbms_rowid.rowid_block_number(ROWID) block_id, lag(dbms_rowid.rowid_block_number(ROWID)) over (order by rowid) as pre_block_id from ttt)
3 where block_id != pre_block_id;
A
----------
1124
1643
1
IMU Commit
让我们看下IMU commit与传统事务commit时产生的redo size的变化。首先看传统模式下，
HELLODBA.COM>conn demo/demo@ora11
Connected.
HELLODBA.COM>alter session set "_in_memory_undo"=false;
Session altered.
HELLODBA.COM>update ttt set b='X' where a=1124;
1 row updated.
HELLODBA.COM>select b.name, a.value from v$mystat a, v$statname b where a.statistic#=b.statistic# and b.name in ('redo entries', 'redo size', 'IMU commits');
NAME VALUE
---------------------------------------------------------------- ----------
redo entries 4
redo size 1600
IMU commits 0
HELLODBA.COM>update ttt set b='Y' where a=1643;
1 row updated.
HELLODBA.COM>select b.name, a.value from v$mystat a, v$statname b where a.statistic#=b.statistic# and b.name in ('redo entries', 'redo size', 'IMU commits');
NAME VALUE
---------------------------------------------------------------- ----------
redo entries 5
redo size 1960
IMU commits 0
HELLODBA.COM>update ttt set b='Z' where a=1;
1 row updated.
HELLODBA.COM>select b.name, a.value from v$mystat a, v$statname b where a.statistic#=b.statistic# and b.name in ('redo entries', 'redo size', 'IMU commits');
NAME VALUE
---------------------------------------------------------------- ----------
redo entries 6
redo size 2320
IMU commits 0
HELLODBA.COM>commit;
Commit complete.
HELLODBA.COM>select b.name, a.value from v$mystat a, v$statname b where a.statistic#=b.statistic# and b.name in ('redo entries', 'redo size', 'IMU commits');
NAME VALUE
---------------------------------------------------------------- ----------
redo entries 7
redo size 2416
IMU commits 0
Connected.
HELLODBA.COM>alter session set "_in_memory_undo"=false;
Session altered.
HELLODBA.COM>update ttt set b='X' where a=1124;
1 row updated.
HELLODBA.COM>select b.name, a.value from v$mystat a, v$statname b where a.statistic#=b.statistic# and b.name in ('redo entries', 'redo size', 'IMU commits');
NAME VALUE
---------------------------------------------------------------- ----------
redo entries 4
redo size 1600
IMU commits 0
HELLODBA.COM>update ttt set b='Y' where a=1643;
1 row updated.
HELLODBA.COM>select b.name, a.value from v$mystat a, v$statname b where a.statistic#=b.statistic# and b.name in ('redo entries', 'redo size', 'IMU commits');
NAME VALUE
---------------------------------------------------------------- ----------
redo entries 5
redo size 1960
IMU commits 0
HELLODBA.COM>update ttt set b='Z' where a=1;
1 row updated.
HELLODBA.COM>select b.name, a.value from v$mystat a, v$statname b where a.statistic#=b.statistic# and b.name in ('redo entries', 'redo size', 'IMU commits');
NAME VALUE
---------------------------------------------------------------- ----------
redo entries 6
redo size 2320
IMU commits 0
HELLODBA.COM>commit;
Commit complete.
HELLODBA.COM>select b.name, a.value from v$mystat a, v$statname b where a.statistic#=b.statistic# and b.name in ('redo entries', 'redo size', 'IMU commits');
NAME VALUE
---------------------------------------------------------------- ----------
redo entries 7
redo size 2416
IMU commits 0
可以看到，每一条数据被update都产生一条redo 条目。
然后，我们激活IMU，再重复上述事务过程，
HELLODBA.COM>conn demo/demo@ora11
Connected.
HELLODBA.COM>alter session set "_in_memory_undo"=true;
Session altered.
HELLODBA.COM>update ttt set b='X' where a=1124;
1 row updated.
HELLODBA.COM>select b.name, a.value from v$mystat a, v$statname b where a.statistic#=b.statistic# an
d b.name in ('redo entries', 'redo size', 'IMU commits');
NAME VALUE
---------------------------------------------------------------- ----------
redo entries 3
redo size 1084
IMU commits 0
HELLODBA.COM>update ttt set b='Y' where a=1643;
1 row updated.
HELLODBA.COM>select b.name, a.value from v$mystat a, v$statname b where a.statistic#=b.statistic# an
d b.name in ('redo entries', 'redo size', 'IMU commits');
NAME VALUE
---------------------------------------------------------------- ----------
redo entries 3
redo size 1084
IMU commits 0
HELLODBA.COM>update ttt set b='Z' where a=1;
1 row updated.
HELLODBA.COM>select b.name, a.value from v$mystat a, v$statname b where a.statistic#=b.statistic# an
d b.name in ('redo entries', 'redo size', 'IMU commits');
NAME VALUE
---------------------------------------------------------------- ----------
redo entries 3
redo size 1084
IMU commits 0
HELLODBA.COM>commit;
Commit complete.
HELLODBA.COM>select b.name, a.value from v$mystat a, v$statname b where a.statistic#=b.statistic# an
d b.name in ('redo entries', 'redo size', 'IMU commits');
NAME VALUE
---------------------------------------------------------------- ----------
redo entries 4
redo size 2176
IMU commits 1
Connected.
HELLODBA.COM>alter session set "_in_memory_undo"=true;
Session altered.
HELLODBA.COM>update ttt set b='X' where a=1124;
1 row updated.
HELLODBA.COM>select b.name, a.value from v$mystat a, v$statname b where a.statistic#=b.statistic# an
d b.name in ('redo entries', 'redo size', 'IMU commits');
NAME VALUE
---------------------------------------------------------------- ----------
redo entries 3
redo size 1084
IMU commits 0
HELLODBA.COM>update ttt set b='Y' where a=1643;
1 row updated.
HELLODBA.COM>select b.name, a.value from v$mystat a, v$statname b where a.statistic#=b.statistic# an
d b.name in ('redo entries', 'redo size', 'IMU commits');
NAME VALUE
---------------------------------------------------------------- ----------
redo entries 3
redo size 1084
IMU commits 0
HELLODBA.COM>update ttt set b='Z' where a=1;
1 row updated.
HELLODBA.COM>select b.name, a.value from v$mystat a, v$statname b where a.statistic#=b.statistic# an
d b.name in ('redo entries', 'redo size', 'IMU commits');
NAME VALUE
---------------------------------------------------------------- ----------
redo entries 3
redo size 1084
IMU commits 0
HELLODBA.COM>commit;
Commit complete.
HELLODBA.COM>select b.name, a.value from v$mystat a, v$statname b where a.statistic#=b.statistic# an
d b.name in ('redo entries', 'redo size', 'IMU commits');
NAME VALUE
---------------------------------------------------------------- ----------
redo entries 4
redo size 2176
IMU commits 1
可见redo数量并没有随着数据的更新而增加，而是在IMU commit时增加。而当1条DML语句更新多条记录时，也可以使用到IMU：
HELLODBA.COM>conn demo/demo@ora11
Connected.
HELLODBA.COM>alter session set "_in_memory_undo"=true;
Session altered.
HELLODBA.COM>update ttt set b='X' where a in (1643, 1124, 1);
3 rows updated.
HELLODBA.COM>select b.name, a.value from v$mystat a, v$statname b where a.statistic#=b.statistic# an
d b.name in ('redo entries', 'redo size', 'IMU commits');
NAME VALUE
---------------------------------------------------------------- ----------
redo entries 3
redo size 1084
IMU commits 0
HELLODBA.COM>commit;
Commit complete.
HELLODBA.COM>select b.name, a.value from v$mystat a, v$statname b where a.statistic#=b.statistic# an
d b.name in ('redo entries', 'redo size', 'IMU commits');
NAME VALUE
---------------------------------------------------------------- ----------
redo entries 4
redo size 2344
IMU commits 1
Connected.
HELLODBA.COM>alter session set "_in_memory_undo"=true;
Session altered.
HELLODBA.COM>update ttt set b='X' where a in (1643, 1124, 1);
3 rows updated.
HELLODBA.COM>select b.name, a.value from v$mystat a, v$statname b where a.statistic#=b.statistic# an
d b.name in ('redo entries', 'redo size', 'IMU commits');
NAME VALUE
---------------------------------------------------------------- ----------
redo entries 3
redo size 1084
IMU commits 0
HELLODBA.COM>commit;
Commit complete.
HELLODBA.COM>select b.name, a.value from v$mystat a, v$statname b where a.statistic#=b.statistic# an
d b.name in ('redo entries', 'redo size', 'IMU commits');
NAME VALUE
---------------------------------------------------------------- ----------
redo entries 4
redo size 2344
IMU commits 1
从上面的例子中你也许注意到了，尽管UPDATE过程中redo size没有变化，但是，在IMU commit时，redo size的变化却很大，比传统模式下的commit产生的redo大许多。这是因为在IMU commit中，不仅仅包含了commit vector，还包含了commit之前数据变化，并且这些redo数据的写入是一次批量写入。我们可以将这个redo条目dump出来观察其内容：
HELLODBA.COM>conn demo/demo@ora11
Connected.
HELLODBA.COM>set serveroutput on
HELLODBA.COM>var v_bt number;
HELLODBA.COM>var v_et number;
HELLODBA.COM>alter session set "_in_memory_undo"=false;
Session altered.
HELLODBA.COM>update tt set x=1 where rownum <= 1;
1 row updated.
HELLODBA.COM>update tt set x=2 where rownum <= 1;
1 row updated.
HELLODBA.COM>update tt set x=3 where rownum <= 1;
1 row updated.
HELLODBA.COM>begin
2 select current_scn into :v_bt from v$database;
3 dbms_output.put_line(''||:v_bt);
4 end;
5 /
6328064
PL/SQL procedure successfully completed.
HELLODBA.COM>commit;
Commit complete.
HELLODBA.COM>begin
2 select current_scn into :v_et from v$database;
3 dbms_output.put_line(''||:v_et);
4 end;
5 /
6328067
PL/SQL procedure successfully completed.
HELLODBA.COM>declare
2 v_log varchar2(2000);
3 v_sql varchar2(4000);
4 begin
5 select a.member into v_log from v$logfile a, v$log b where a.group#=b.group# and b.status='CUR
RENT' and rownum <= 1;
6 execute immediate 'alter system switch logfile';
7 v_sql := 'alter system dump logfile '''||v_log||''' SCN MIN '||:v_bt||' SCN MAX '||:v_et;
8 execute immediate v_sql;
9 end;
10 /
PL/SQL procedure successfully completed.
Connected.
HELLODBA.COM>set serveroutput on
HELLODBA.COM>var v_bt number;
HELLODBA.COM>var v_et number;
HELLODBA.COM>alter session set "_in_memory_undo"=false;
Session altered.
HELLODBA.COM>update tt set x=1 where rownum <= 1;
1 row updated.
HELLODBA.COM>update tt set x=2 where rownum <= 1;
1 row updated.
HELLODBA.COM>update tt set x=3 where rownum <= 1;
1 row updated.
HELLODBA.COM>begin
2 select current_scn into :v_bt from v$database;
3 dbms_output.put_line(''||:v_bt);
4 end;
5 /
6328064
PL/SQL procedure successfully completed.
HELLODBA.COM>commit;
Commit complete.
HELLODBA.COM>begin
2 select current_scn into :v_et from v$database;
3 dbms_output.put_line(''||:v_et);
4 end;
5 /
6328067
PL/SQL procedure successfully completed.
HELLODBA.COM>declare
2 v_log varchar2(2000);
3 v_sql varchar2(4000);
4 begin
5 select a.member into v_log from v$logfile a, v$log b where a.group#=b.group# and b.status='CUR
RENT' and rownum <= 1;
6 execute immediate 'alter system switch logfile';
7 v_sql := 'alter system dump logfile '''||v_log||''' SCN MIN '||:v_bt||' SCN MAX '||:v_et;
8 execute immediate v_sql;
9 end;
10 /
PL/SQL procedure successfully completed.
可以看到，在trace日志中，这一redo条目包含了多个change：
REDO RECORD - Thread:1 RBA: 0x0000c8.00000f39.0010 LEN: 0x046c VLD: 0x0d
SCN: 0x0000.00608ed4 SUBSCN: 1 11/16/2009 14:59:10
CHANGE #1 TYP:2 CLS: 1 AFN:4 DBA:0x010016cf OBJ:74952 SCN:0x0000.00602dc7 SEQ: 4 OP:11.19
KTB Redo
...
CHANGE #2 TYP:0 CLS:17 AFN:3 DBA:0x00c00009 OBJ:4294967295 SCN:0x0000.00608e9b SEQ: 2 OP:5.2
...
CHANGE #8 TYP:0 CLS:18 AFN:3 DBA:0x00c006f7 OBJ:4294967295 SCN:0x0000.00608ed4 SEQ: 2 OP:5.1
...
SCN: 0x0000.00608ed4 SUBSCN: 1 11/16/2009 14:59:10
CHANGE #1 TYP:2 CLS: 1 AFN:4 DBA:0x010016cf OBJ:74952 SCN:0x0000.00602dc7 SEQ: 4 OP:11.19
KTB Redo
...
CHANGE #2 TYP:0 CLS:17 AFN:3 DBA:0x00c00009 OBJ:4294967295 SCN:0x0000.00608e9b SEQ: 2 OP:5.2
...
CHANGE #8 TYP:0 CLS:18 AFN:3 DBA:0x00c006f7 OBJ:4294967295 SCN:0x0000.00608ed4 SEQ: 2 OP:5.1
...
