In Memory Undo

早上读老熊的博客Hint的常见错误使用方式, 评论中有朋友提到, 表没有nologging属性, 插入语句之后, 会话的统计信息redo size为0. 是不是统计信息出错? 我猜想是因为In Memory Undo(IMU)的缘故, 下午做了一下测试, 顺便整理IMU的知识.

Oracle采用write-ahead logging的策略, 优先写redo, 保证事物的持久性. write-ahead有两层含义
1. Log 优先于data: 对data(表和索引)和undo更改之前, Oracle产生相应的change vecter, 合并成一条redo record, copy到log buffer里, 然后才根据change vector更改data和undo.
2. lgwr优先于dbwr: data和undo从buffer cahce写到磁盘之前, 需要lgwr把它们对应的redo record写到online redo file.

我们这里关注第一点, 10g之前只有一个log buffer, 每一次DML产生的redo record都会马上被copy到log buffer, 并发的小事务容易形成对log buffer的争用. Oracle 10g引入IMU减少小事务对log buffer的使用次数. IMU是在shared pool里两组一一对应的结构: Private undo buffer和 private redo buffer(称为private redo thread或者redo strand). Private undo buffer存放undo的change vector, private redo buffer存放data的change vector. 当private undo buffer或者private redo buffer用完的时候, 或者当用户提交的时候, Oracle会把两组change vector合并成一条redo record, 一起copy到一个公共 log buffer. 还有其他原因会导致change vector被刷到一个公共log buffer, 比如checkpoint的时候. 使用IMU, change Vector产生的数量大小并没有改变, 只是把change vector copy到公共 redo buffer这个动作往后推迟了, 批量处理, 减少对公共的log buffer争用.

对于一个IMU事务, 会话的统计信息, 比如”redo size”, “redo entries”, “IMU Flush”只有在change vector被copy到公共的log buffer时才会增加. 一个insert语句之后, 如果change vector还在IMU里, 相应的统计信息就不会增加. 下面是我在10.2.0.5 Linux 32bit的测试.

1. 确定IMU已经启用. _in_memory_undo这个隐含参数用于打开或者关闭IMU. x$ktifp保存private undo buffer的信息, 其中ktifprpb指向对应private redo buffer的起始位置. x$kcrfstrand保存了所有redo buffer的信息, 开始两个log buffer是公共的. Shared Pool分配了有24组buffer, 每个buffer大小64k左右. 其中6组是活跃, Oracle会根据负载动态调整活跃buffer的数目. x$ktiff记录IMU的统计信息.

sys@SIDGD> @pd2 _in_memory_undo

NAME            VALUE DESCRIPTION
--------------- ----- ------------------------------------------------
_in_memory_undo TRUE  Make in memory undo for top level transactions

select
  indx,
  to_number(ktifpupe,'XXXXXXXXXXXXXXXXXXXXXXX') -
  to_number(ktifpupb,'XXXXXXXXXXXXXXXXXXXXXXX')           undo_size,
  to_number(ktifpupc,'XXXXXXXXXXXXXXXXXXXXXXX') -
          to_number(ktifpupb,'XXXXXXXXXXXXXXXXXXXXXXX')   undo_usage,
  ktifprpb                                                redo_start,
  to_number(ktifprpe,'XXXXXXXXXXXXXXXXXXXXXXX') -
          to_number(ktifprpb,'XXXXXXXXXXXXXXXXXXXXXXX')   redo_size,
  to_number(ktifprpc,'XXXXXXXXXXXXXXXXXXXXXXX') -
          to_number(ktifprpb,'XXXXXXXXXXXXXXXXXXXXXXX')   redo_usage
  from
          x$ktifp
;

 --24组 private undo buffer
 INDX  UNDO_SIZE UNDO_USAGE REDO_STA  REDO_SIZE REDO_USAGE
----- ---------- ---------- -------- ---------- ----------
    0      64000          0 00                0          0     
    1      64000          0 00                0          0
    2      64000          0 00                0          0

....

   21      64000          0 00                0          0
   22      64000          0 00                0          0
   23      64000          0 00                0          0

24 rows selected.

select
        indx,
        PNEXT_BUF_KCRFA_CLN,
        PTR_KCRF_PVT_STRAND,
        FIRST_BUF_KCRFA,
        LAST_BUF_KCRFA,
        STRAND_SIZE_KCRFA       strand_size,
        SPACE_KCRF_PVT_STRAND   strand_space
from
        x$kcrfstrand
;

 INDX PNEXT_BU PTR_KCRF FIRST_BU LAST_BUF STRAND_SIZE STRAND_SPACE
----- -------- -------- -------- -------- ----------- ------------
    0 20148400 00       20138000 20296600     1435648            0    <-- 两组公共的log buffer
    1 203F5000 00       20296800 203F4E00     1435648            0
    2 00       43CA50EC 43CA5098 00             66560        62976    <-- 六组活跃的private redo buffer
    3 00       43CB54EC 43CB5498 00             66560        62976
    4 00       43CC58EC 43CC5898 00             66560        62976
    5 00       43CD5CEC 43CD5C98 00             66560        62976
    6 00       43CE60EC 43CE6098 00             66560        62976
    7 00       43CF64EC 43CF6498 00             66560        62976
    8 00       00       43D06898 00             66560            0    <--不活跃的private redo buffer
    9 00       00       43D16C98 00             66560            0
   10 00       00       43D27098 00             66560            0

....

26 rows selected.


select ktiffcat, ktiffflc from x$ktiff order by 2;

KTIFFCAT                              KTIFFFLC
----------------------------------- ----------
....
Recursive txn flushes                        1
Max. chgs flushes                            3
Redo pool overflow flushes                   7
Contention flushes                          11
Undo pool overflow flushes                  15
Bitmap state change flushes                 29
Stack cv flushes                            46
Redo only CR flushes                       208
Rollback flushes                           323
Commit flushes                          531663

18 rows selected.

2. 准备一张空表T

sys@SIDGD> drop table t purge;

Table dropped.

sys@SIDGD> create table t (n1 number, v1 varchar2(2000));

Table created.

3. 新开一个会话. 先切换日志文件, 等会观察第一个redo record的大小,包含多少change vector. 每次往表T插入6条记录, 可以看到第一个private undo buffer被使用, 对应第一个private redo buffer, 地址是43CA50EC. 因为是插入语句, data产生的change vector比undo的多. data的change vector大小是21208, undo的change vector是2560. redo entries和redo size都为0, change vector还没有被刷到log buffer.

sys@SIDGD> alter system switch logfile;

System altered.

===================
session statistics
===================

sys@SIDGD> select
  2  	 ses.sid,
  3  	 sn.name,
  4  	 ses.value
  5  from
  6  	 v$sesstat ses,
  7  	 v$statname sn
  8  where
  9  	 sn.statistic# = ses.statistic#
 10  and ses.sid in (select sid from v$mystat where rownum=1)
 11  and sn.name in ('redo entries', 'redo size', 'IMU Flushes', 'IMU commits')
 12  /

       SID NAME                                VALUE
---------- ------------------------------ ----------
       199 redo entries                            0
       199 redo size                               0
       199 IMU commits                             0
       199 IMU Flushes                             0

sys@SIDGD> 
sys@SIDGD> insert into t
  2  select
  3  	     level,
  4  	     lpad(level, 2000, '0')
  5  from
  6  	     dual
  7  connect by level <= 6;

6 rows created.

===================
session statistics
===================

       SID NAME                                VALUE
---------- ------------------------------ ----------
       199 redo entries                            0
       199 redo size                               0
       199 IMU commits                             0
       199 IMU Flushes                             0

====================
private undo buffers
====================
 INDX  UNDO_SIZE UNDO_USAGE REDO_STA  REDO_SIZE REDO_USAGE
----- ---------- ---------- -------- ---------- ----------
    0      64000       2560 43CA50EC      62976      21208
    1      64000          0 00                0          0
    2      64000          0 00                0          0
    3      64000          0 00                0          0
....

==========================
all the redo buffers
==========================
 INDX PNEXT_BU PTR_KCRF FIRST_BU LAST_BUF STRAND_SIZE STRAND_SPACE
----- -------- -------- -------- -------- ----------- ------------
    0 20296800 00       20138000 20296600     1435648            0
    1 203F5000 00       20296800 203F4E00     1435648            0
    2 00       43CA50EC 43CA5098 00             66560        62976
    3 00       43CB54EC 43CB5498 00             66560        62976
....

4. 继续插入六条记录. Private redo buffer增到到42876, undo是5068. redo size和redo entry没有改变, x$kcrfstrand.PNEXT_BUF_KCRFA_CLN表示log buffer中空闲空间的开始, 两个公共log buffer这个指针都没有改变(0x20296800 和 0x203F5000), 这段时间没有redo record进入公共log buffer.

sys@SIDGD> insert into t
  2  select
  3  	     level,
  4  	     lpad(level, 2000, '0')
  5  from
  6  	     dual
  7  connect by level <= 6;

6 rows created.

===================
session statistics
===================

       SID NAME                                VALUE
---------- ------------------------------ ----------
       199 redo entries                            0
       199 redo size                               0
       199 IMU commits                             0
       199 IMU Flushes                             0

====================
private undo buffers
====================

 INDX  UNDO_SIZE UNDO_USAGE REDO_STA  REDO_SIZE REDO_USAGE
----- ---------- ---------- -------- ---------- ----------
    0      64000       5068 43CA50EC      62976      42876

==========================
all the redo buffers
==========================

 INDX PNEXT_BU PTR_KCRF FIRST_BU LAST_BUF STRAND_SIZE STRAND_SPACE
----- -------- -------- -------- -------- ----------- ------------
    0 20296800 00       20138000 20296600     1435648            0
    1 203F5000 00       20296800 203F4E00     1435648            0
    2 00       43CA50EC 43CA5098 00             66560        62976

5. 继续插入六条记录. 可以看到private redo buffer用满后(62972/62976), 触发一次IMU Flushes, IMU中的change vector被刷到公共的log buffer, 第一个log buffer的PNEXT_BUF_KCRFA_CLN往前推进了(0x20296800 -> 0x2014212C). 有趣的是redo size只有39944, 远远低于69712(6740+62972), 说明IMU的对change vector管理需要额外的空间, 多余的部分在进入公共的log buffer时被去掉. redo entries为2, 除了从IMU刷出来的一个redo record, 还有另外一条redo record. 因为IMU只有使用一次机会, private redo buffer用满之后切换回传统的方式, 超出的一条redo record会马上被copy到log buffer.

sys@SIDGD> insert into t
  2  select
  3  	     level,
  4  	     lpad(level, 2000, '0')
  5  from
  6  	     dual
  7  connect by level <= 6;

6 rows created.

===================
session statistics
===================

       SID NAME                                VALUE
---------- ------------------------------ ----------
       199 redo entries                            2
       199 redo size                           39944
       199 IMU commits                             0
       199 IMU Flushes                             1


====================
private undo buffers
====================

 INDX  UNDO_SIZE UNDO_USAGE REDO_STA  REDO_SIZE REDO_USAGE
----- ---------- ---------- -------- ---------- ----------
    0      64000       6740 43CA50EC      62976      62972

==========================
all the redo buffers
==========================

 INDX PNEXT_BU PTR_KCRF FIRST_BU LAST_BUF STRAND_SIZE STRAND_SPACE
----- -------- -------- -------- -------- ----------- ------------
    0 2014212C 00       20138000 20296600     1435648            0
    1 203F5000 00       20296800 203F4E00     1435648            0
    2 00       43CA50EC 43CA5098 00             66560        62976

6. 继续插入六条记录. redo record的产生方式已经切换回传统方式. redo entries增加到29, redo size增加到55720, 第一个log buffer的PNEXT_BUF_KCRFA_CLN继续往前推进(0x2014212C -> 0x201460CC).

sys@SIDGD> insert into t
  2  select
  3  	     level,
  4  	     lpad(level, 2000, '0')
  5  from
  6  	     dual
  7  connect by level <= 6;

6 rows created.

===================
session statistics
===================

       SID NAME                                VALUE
---------- ------------------------------ ----------
       199 redo entries                           29
       199 redo size                           55720
       199 IMU commits                             0
       199 IMU Flushes                             1


====================
private undo buffers
====================

 INDX  UNDO_SIZE UNDO_USAGE REDO_STA  REDO_SIZE REDO_USAGE
----- ---------- ---------- -------- ---------- ----------
    0      64000       6740 43CA50EC      62976      62972

==========================
all the redo buffers
==========================

 INDX PNEXT_BU PTR_KCRF FIRST_BU LAST_BUF STRAND_SIZE STRAND_SPACE
----- -------- -------- -------- -------- ----------- ------------
    0 201460CC 00       20138000 20296600     1435648            0
    1 203F5000 00       20296800 203F4E00     1435648            0
    2 00       43CA50EC 43CA5098 00             66560        62976

7. 提交. 这时增加一条redo record, 对这个事物对应transaction table slot进行清除. 同时IMU的使用信息也在这时被清除.

sys@SIDGD> commit;

Commit complete.

===================
session statistics
===================

       SID NAME                                VALUE
---------- ------------------------------ ----------
       199 redo entries                           30
       199 redo size                           55816
       199 IMU commits                             0
       199 IMU Flushes                             1

====================
private undo buffers
====================

 INDX  UNDO_SIZE UNDO_USAGE REDO_STA  REDO_SIZE REDO_USAGE
----- ---------- ---------- -------- ---------- ----------
    0      64000          0 00                0          0

==========================
all the redo buffers
==========================

 INDX PNEXT_BU PTR_KCRF FIRST_BU LAST_BUF STRAND_SIZE STRAND_SPACE
----- -------- -------- -------- -------- ----------- ------------
    0 20146200 00       20138000 20296600     1435648            0
    1 203F5000 00       20296800 203F4E00     1435648            0
    2 00       43CA50EC 43CA5098 00             66560        62976

8. 转储日志文件, 第一条redo record包含53个chagne vector, 大小是35692(=0x8b6c). 之后的redo record, 大都只包含两个change vector.

alter system dump logfile '/home/u02/app/oracle/product/11.1.0/oradata/SIDGD/SIDGD_redo_02a.log';

REDO RECORD - Thread:1 RBA: 0x0004be.00000002.0010 LEN: 0x8b6c VLD: 0x0d
SCN: 0x0001.cf5dade9 SUBSCN:  1 02/29/2012 19:28:03
CHANGE #1 TYP:1 CLS: 1 AFN:1 DBA:0x00407d2a OBJ:63587 SCN:0x0001.cf5dade8 SEQ:  1 OP:13.5
CHANGE #2 TYP:0 CLS: 1 AFN:1 DBA:0x00407d2a OBJ:63587 SCN:0x0001.cf5dade9 SEQ:  1 OP:13.6
CHANGE #3 TYP:0 CLS: 1 AFN:1 DBA:0x00407d2a OBJ:63587 SCN:0x0001.cf5dade9 SEQ:  2 OP:13.6
....
CHANGE #50 TYP:0 CLS:24 AFN:2 DBA:0x0080e2b4 OBJ:4294967295 SCN:0x0001.cf5dade9 SEQ:  4 OP:5.1
CHANGE #51 TYP:0 CLS:24 AFN:2 DBA:0x0080e2b4 OBJ:4294967295 SCN:0x0001.cf5dade9 SEQ:  5 OP:5.1
CHANGE #52 TYP:0 CLS:24 AFN:2 DBA:0x0080e2b4 OBJ:4294967295 SCN:0x0001.cf5dade9 SEQ:  6 OP:5.1
CHANGE #53 TYP:0 CLS:24 AFN:2 DBA:0x0080e2b4 OBJ:4294967295 SCN:0x0001.cf5dade9 SEQ:  7 OP:5.1

In Memory Undo》上有1条评论

发表评论

电子邮件地址不会被公开。 必填项已用*标注

您可以使用这些HTML标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>