Oracle In Memory Undo(转)

最新推荐文章于 2023-03-25 15:16:00 发布

cucanqi9387

最新推荐文章于 2023-03-25 15:16:00 发布

阅读量112

点赞数

Oracle In Memory Undo

作者： fuyuncat

来源： www.HelloDBA.com

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7199859/viewspace-671679/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7199859/viewspace-671679/

cucanqi9387

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle In Memory Undo(转)

Oracle In Memory Undo作者： fuyuncat 来源： www.HelloDBA.com IMU was first introduced in 10g, but ...
复制链接

扫一扫