Oracle In Memory Undo

作者：

來源：

IMU was first introduced in 10g, but I can not observe it in my 10.2.0.3 server. Below test was performed in 11g.       In traditional undo update, once record be updated, an undo block will be allocated in the buffer cache, 1 new entry will be inserted into the undo block immediately. If several records be updated in the same transaction, several entries generated in the undo buffer as soon as the record updated. At the mean time, each undo entry will also generate redo log entry. After introduced the IMU, new pools named IMU pools will be allocated from shared pool. Once a record be updated, an undo buffer block still be allocted from buffer cache, but without inserting a new entry into the block immediately. It will generate an undo map in the IMU pool, and one IMU node for the record change. If several records be updated, several IMU nodes will be generated in the IMU pool, and the UNDO map be updated correspondly. All of the changes occur in the IMU pool, not modify the undo buffer block. Once commit or the IMU pool be flushed, it will map the IMU nodes as undo entries into the undo block and write to disk. This process is a batch process, just 1 redo entry generated for these changes.       The undocumented parameter "_in_memory_undo" control to enable/disable this feature. It could be modified in system/session level. Another undocumented parameter "_IMU_pools" control the IMU pool number. SQL程式碼 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     Let's have a testing to see how does IMU reduce redo size.     First observe the traditional mode. SQL程式碼 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       Each record updating generate 1 redo entry.       Now, enable IMU and do it again.   SQL程式碼 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       The redo entries did not increase with the records be updated, it just increase at the momemt commmit. Unlike traditional commit redo entry, it not only contain the commit vector, but also the undo changes, be wroten in batch.     IMU commit also works in such case that several records updated in 1 dml.   SQL程式碼 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       You may noted although the final redo size of IMU is less than the redo size of non-IMU, it increased obviously when commit. Dump the redo entry, we can find it contains the changes before commit, including the recursive operations, such as cleanout.   SQL程式碼 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.       In the redo trace file, there are 3 changes in this entry.   SQL程式碼 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    ...       Once commit, the IMU nodes will be mapped to undo buffer block, after that, the undo buffer block is same as the non-IMU undo buffer block.   IMU Flush     The IMU pool is also managed by LRU algorithm. Once not enough buffer could be allocated for the new transactions, it will flush the buffers from the LRU end. Other events will also cause the IMU flush, such as switch logfile and tansaction rollback. However, even though the IMU pools were mentioned as allocated from shared pool, manually flushing shared pool will not cause the IMU flush. Once IMU nodes be flushed, the undo entries will be mapped into the correspond undo buffer.   SQL程式碼 HELLODBA.COM>conn demo/demo@ora11    Connected.    HELLODBA.COM>alter session set "_in_memory_undo"=true;       Session altered.       HELLODBA.COM>update tt set x=1;       1 row updated.       HELLODBA.COM>update tt set x=2;       1 row updated.       HELLODBA.COM>update tt set x=3;       1 row updated.       HELLODBA.COM>select b.name, a.value from v$sysstat a, v$statname b where a.statistic#=b.statistic# and b.name like '%IMU%';       NAME                                                                  VALUE    ---------------------------------------------------------------- ----------    IMU commits                                                             320    IMU Flushes                                                             159    IMU contention                                                           19    ...       13 rows selected.       HELLODBA.COM>alter system switch logfile;       System altered.       HELLODBA.COM>select b.name, a.value from v$sysstat a, v$statname b where a.statistic#=b.statistic# and b.name like '%IMU%';       NAME                                                                  VALUE    ---------------------------------------------------------------- ----------    IMU commits                                                             320    IMU Flushes                                                             160    IMU contention                                                           20    ...       13 rows selected.       Once flushed, all of the changes after last flush will be merged into 1 redo log entry.   IMU CR     In traditional mode, the consistent get transaction will read undo block to apply the undo records. However, in IMU, the undo buffer block was not be modified before IMU commit/flush, the CR transaction need read the undo info from IMU pool.   SQL程式碼 --Session 1:    HELLODBA.COM>conn demo/demo@ora11    Connected.    HELLODBA.COM>alter session set "_in_memory_undo"=true;       Session altered.       HELLODBA.COM>update tt set x=1;       1 row updated.       HELLODBA.COM>update tt set x=2;       1 row updated.       HELLODBA.COM>update tt set x=3;       1 row updated.       --Session 2:    HELLODBA.COM>conn demo/demo@ora11    Connected.    HELLODBA.COM>alter system flush buffer_cache;       System altered.       HELLODBA.COM>alter session set tracefile_identifier=IMU_CR;       Session altered.       HELLODBA.COM>alter session set events '10046 trace name context forever, level 8';       Session altered.       HELLODBA.COM>select * from tt;                X    ----------             3       HELLODBA.COM>alter session set events '10046 trace name context off';       Session altered.       HELLODBA.COM>select b.name, a.value from v$mystat a, v$statname b where a.statistic#=b.statistic# and b.name like '%IMU%';       NAME                                                                  VALUE    ---------------------------------------------------------------- ----------    ...    IMU CR rollbacks                                                          3    ...        From the trace file, even though the undo buffer has been flushed out from buffer cached, it did not read the undo files for CR rollback, read from IMU pool instead.     --- Fuyuncat Mark ---