Oracle In Memory Undo(轉)

paulyibinyi發表於2010-08-23
Oracle

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程式碼
  1. HELLODBA.COM>create table ttt (a number, b varchar2(20));   
  2.   
  3. Table created.   
  4.   
  5. HELLODBA.COM>begin  
  6.   2    for i in 1..2000 loop   
  7.   3      insert into ttt values (i, ''||i);   
  8.   4    end loop;   
  9.   5    commit;   
  10.   6  end;   
  11.   7  /   
  12.   
  13. PL/SQL procedure successfully completed.   
  14.   
  15. HELLODBA.COM>select a   
  16.   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)   
  17.   3  where block_id != pre_block_id;   
  18.   
  19.          A   
  20. ----------   
  21.       1124   
  22.       1643   
  23.          1  

IMU Commit

    Let's have a testing to see how does IMU reduce redo size.

    First observe the traditional mode.

SQL程式碼
  1. HELLODBA.COM>conn demo/demo@ora11   
  2. Connected.   
  3. HELLODBA.COM>alter session set "_in_memory_undo"=false;   
  4.   
  5. Session altered.   
  6.   
  7. HELLODBA.COM>update ttt set b='X' where a=1124;   
  8.   
  9. 1 row updated.   
  10.   
  11. 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');   
  12.   
  13. NAME                                                                  VALUE   
  14. ---------------------------------------------------------------- ----------   
  15. redo entries                                                              4   
  16. redo size                                                              1600   
  17. IMU commits                                                               0   
  18.   
  19. HELLODBA.COM>update ttt set b='Y' where a=1643;   
  20.   
  21. 1 row updated.   
  22.   
  23. 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');   
  24.   
  25. NAME                                                                  VALUE   
  26. ---------------------------------------------------------------- ----------   
  27. redo entries                                                              5   
  28. redo size                                                              1960   
  29. IMU commits                                                               0   
  30.   
  31. HELLODBA.COM>update ttt set b='Z' where a=1;   
  32.   
  33. 1 row updated.   
  34.   
  35. 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');   
  36.   
  37. NAME                                                                  VALUE   
  38. ---------------------------------------------------------------- ----------   
  39. redo entries                                                              6   
  40. redo size                                                              2320   
  41. IMU commits                                                               0   
  42.   
  43. HELLODBA.COM>commit;   
  44.   
  45. Commit complete.   
  46.   
  47. 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');   
  48.   
  49. NAME                                                                  VALUE   
  50. ---------------------------------------------------------------- ----------   
  51. redo entries                                                              7   
  52. redo size                                                              2416   
  53. IMU commits                                                               0  

    Each record updating generate 1 redo entry.
 

    Now, enable IMU and do it again.
 

SQL程式碼
  1. HELLODBA.COM>conn demo/demo@ora11   
  2. Connected.   
  3. HELLODBA.COM>alter session set "_in_memory_undo"=true;   
  4.   
  5. Session altered.   
  6.   
  7. HELLODBA.COM>update ttt set b='X' where a=1124;   
  8.   
  9. 1 row updated.   
  10.   
  11. HELLODBA.COM>select b.name, a.value from v$mystat a, v$statname b where a.statistic#=b.statistic# an   
  12. d b.name in ('redo entries', 'redo size', 'IMU commits');   
  13.   
  14. NAME                                                                  VALUE   
  15. ---------------------------------------------------------------- ----------   
  16. redo entries                                                              3   
  17. redo size                                                              1084   
  18. IMU commits                                                               0   
  19.   
  20. HELLODBA.COM>update ttt set b='Y' where a=1643;   
  21.   
  22. 1 row updated.   
  23.   
  24. HELLODBA.COM>select b.name, a.value from v$mystat a, v$statname b where a.statistic#=b.statistic# an   
  25. d b.name in ('redo entries', 'redo size', 'IMU commits');   
  26.   
  27. NAME                                                                  VALUE   
  28. ---------------------------------------------------------------- ----------   
  29. redo entries                                                              3   
  30. redo size                                                              1084   
  31. IMU commits                                                               0   
  32.   
  33. HELLODBA.COM>update ttt set b='Z' where a=1;   
  34.   
  35. 1 row updated.   
  36.   
  37. HELLODBA.COM>select b.name, a.value from v$mystat a, v$statname b where a.statistic#=b.statistic# an   
  38. d b.name in ('redo entries', 'redo size', 'IMU commits');   
  39.   
  40. NAME                                                                  VALUE   
  41. ---------------------------------------------------------------- ----------   
  42. redo entries                                                              3   
  43. redo size                                                              1084   
  44. IMU commits                                                               0   
  45.   
  46. HELLODBA.COM>commit;   
  47.   
  48. Commit complete.   
  49.   
  50. HELLODBA.COM>select b.name, a.value from v$mystat a, v$statname b where a.statistic#=b.statistic# an   
  51. d b.name in ('redo entries', 'redo size', 'IMU commits');   
  52.   
  53. NAME                                                                  VALUE   
  54. ---------------------------------------------------------------- ----------   
  55. redo entries                                                              4   
  56. redo size                                                              2176   
  57. 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程式碼
  1. HELLODBA.COM>conn demo/demo@ora11   
  2. Connected.   
  3. HELLODBA.COM>alter session set "_in_memory_undo"=true;   
  4.   
  5. Session altered.   
  6.   
  7. HELLODBA.COM>update ttt set b='X' where a in (1643, 1124, 1);   
  8.   
  9. 3 rows updated.   
  10.   
  11. HELLODBA.COM>select b.name, a.value from v$mystat a, v$statname b where a.statistic#=b.statistic# an   
  12. d b.name in ('redo entries', 'redo size', 'IMU commits');   
  13.   
  14. NAME                                                                  VALUE   
  15. ---------------------------------------------------------------- ----------   
  16. redo entries                                                              3   
  17. redo size                                                              1084   
  18. IMU commits                                                               0   
  19.   
  20. HELLODBA.COM>commit;   
  21.   
  22. Commit complete.   
  23.   
  24. HELLODBA.COM>select b.name, a.value from v$mystat a, v$statname b where a.statistic#=b.statistic# an   
  25. d b.name in ('redo entries', 'redo size', 'IMU commits');   
  26.   
  27. NAME                                                                  VALUE   
  28. ---------------------------------------------------------------- ----------   
  29. redo entries                                                              4   
  30. redo size                                                              2344   
  31. 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程式碼
  1. HELLODBA.COM>conn demo/demo@ora11   
  2. Connected.   
  3. HELLODBA.COM>set serveroutput on  
  4. HELLODBA.COM>var v_bt number;   
  5. HELLODBA.COM>var v_et number;   
  6. HELLODBA.COM>alter session set "_in_memory_undo"=false;   
  7.   
  8. Session altered.   
  9.   
  10. HELLODBA.COM>update tt set x=1 where rownum <= 1;   
  11.   
  12. 1 row updated.   
  13.   
  14. HELLODBA.COM>update tt set x=2 where rownum <= 1;   
  15.   
  16. 1 row updated.   
  17.   
  18. HELLODBA.COM>update tt set x=3 where rownum <= 1;   
  19.   
  20. 1 row updated.   
  21.   
  22. HELLODBA.COM>begin  
  23.   2   select current_scn into :v_bt from v$database;   
  24.   3   dbms_output.put_line(''||:v_bt);   
  25.   4  end;   
  26.   5  /   
  27. 6328064   
  28.   
  29. PL/SQL procedure successfully completed.   
  30.   
  31. HELLODBA.COM>commit;   
  32.   
  33. Commit complete.   
  34.   
  35. HELLODBA.COM>begin  
  36.   2   select current_scn into :v_et from v$database;   
  37.   3   dbms_output.put_line(''||:v_et);   
  38.   4  end;   
  39.   5  /   
  40. 6328067   
  41.   
  42. PL/SQL procedure successfully completed.   
  43.   
  44. HELLODBA.COM>declare  
  45.   2   v_log varchar2(2000);   
  46.   3   v_sql varchar2(4000);   
  47.   4  begin  
  48.   5   select a.member into v_log from v$logfile a, v$log b where a.group#=b.group# and b.status='CUR  
  49. RENT' and rownum <= 1;   
  50.   6   execute immediate 'alter system switch logfile';   
  51.   7   v_sql := 'alter system dump logfile '''||v_log||''' SCN MIN '||:v_bt||' SCN MAX '||:v_et;   
  52.   8   execute immediate v_sql;   
  53.   9  end;   
  54.  10  /   
  55.   
  56. PL/SQL procedure successfully completed.  

    In the redo trace file, there are 3 changes in this entry.
 

SQL程式碼
  1. REDO RECORD - Thread:1 RBA: 0x0000c8.00000f39.0010 LEN: 0x046c VLD: 0x0d   
  2. SCN: 0x0000.00608ed4 SUBSCN:  1 11/16/2009 14:59:10   
  3. CHANGE #1 TYP:2 CLS: 1 AFN:4 DBA:0x010016cf OBJ:74952 SCN:0x0000.00602dc7 SEQ:  4 OP:11.19   
  4. KTB Redo    
  5. ...   
  6. CHANGE #2 TYP:0 CLS:17 AFN:3 DBA:0x00c00009 OBJ:4294967295 SCN:0x0000.00608e9b SEQ:  2 OP:5.2   
  7. ...   
  8. CHANGE #8 TYP:0 CLS:18 AFN:3 DBA:0x00c006f7 OBJ:4294967295 SCN:0x0000.00608ed4 SEQ:  2 OP:5.1   
  9. ...  

    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程式碼
  1. HELLODBA.COM>conn demo/demo@ora11   
  2. Connected.   
  3. HELLODBA.COM>alter session set "_in_memory_undo"=true;   
  4.   
  5. Session altered.   
  6.   
  7. HELLODBA.COM>update tt set x=1;   
  8.   
  9. 1 row updated.   
  10.   
  11. HELLODBA.COM>update tt set x=2;   
  12.   
  13. 1 row updated.   
  14.   
  15. HELLODBA.COM>update tt set x=3;   
  16.   
  17. 1 row updated.   
  18.   
  19. HELLODBA.COM>select b.name, a.value from v$sysstat a, v$statname b where a.statistic#=b.statistic# and b.name like '%IMU%';   
  20.   
  21. NAME                                                                  VALUE   
  22. ---------------------------------------------------------------- ----------   
  23. IMU commits                                                             320   
  24. IMU Flushes                                                             159   
  25. IMU contention                                                           19   
  26. ...   
  27.   
  28. 13 rows selected.   
  29.   
  30. HELLODBA.COM>alter system switch logfile;   
  31.   
  32. System altered.   
  33.   
  34. HELLODBA.COM>select b.name, a.value from v$sysstat a, v$statname b where a.statistic#=b.statistic# and b.name like '%IMU%';   
  35.   
  36. NAME                                                                  VALUE   
  37. ---------------------------------------------------------------- ----------   
  38. IMU commits                                                             320   
  39. IMU Flushes                                                             160   
  40. IMU contention                                                           20   
  41. ...   
  42.   
  43. 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程式碼
  1. --Session 1:   
  2. HELLODBA.COM>conn demo/demo@ora11   
  3. Connected.   
  4. HELLODBA.COM>alter session set "_in_memory_undo"=true;   
  5.   
  6. Session altered.   
  7.   
  8. HELLODBA.COM>update tt set x=1;   
  9.   
  10. 1 row updated.   
  11.   
  12. HELLODBA.COM>update tt set x=2;   
  13.   
  14. 1 row updated.   
  15.   
  16. HELLODBA.COM>update tt set x=3;   
  17.   
  18. 1 row updated.   
  19.   
  20. --Session 2:   
  21. HELLODBA.COM>conn demo/demo@ora11   
  22. Connected.   
  23. HELLODBA.COM>alter system flush buffer_cache;   
  24.   
  25. System altered.   
  26.   
  27. HELLODBA.COM>alter session set tracefile_identifier=IMU_CR;   
  28.   
  29. Session altered.   
  30.   
  31. HELLODBA.COM>alter session set events '10046 trace name context forever, level 8';   
  32.   
  33. Session altered.   
  34.   
  35. HELLODBA.COM>select * from tt;   
  36.   
  37.          X   
  38. ----------   
  39.          3   
  40.   
  41. HELLODBA.COM>alter session set events '10046 trace name context off';   
  42.   
  43. Session altered.   
  44.   
  45. HELLODBA.COM>select b.name, a.value from v$mystat a, v$statname b where a.statistic#=b.statistic# and b.name like '%IMU%';   
  46.   
  47. NAME                                                                  VALUE   
  48. ---------------------------------------------------------------- ----------   
  49. ...   
  50. IMU CR rollbacks                                                          3   
  51. ...  

     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 ---

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7199859/viewspace-671679/，如需轉載，請註明出處，否則將追究法律責任。

相關文章