[20201207]关于ORACLE IMU的一些疑问.txt

--//关于oracle IMU，我自己本人有太多的疑问，我自己很久以前在测试栽过跟头。
--// http://blog.itpub.net/267265/viewspace-2137720/ => [20170421]警惕打开IMU对测试的影响.txt

--//前几天看别人的测试：https://www.modb.pro/db/23012 =>关于ORACLE In Memory Undo的一些疑问
--//对方的测试结论：
测试结论：
1、当使用IMU时，flush buffer 不会触发IMU Flushes，还没有IMU Flushes的脏块是不会落盘的
2、checkpoint时会IMU Flushes。

--//但是我的测试checkpoint时不一定做IMU Flushes。我重复测试看看：

1.环境：

SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

--//session 1:
create table test_imu(id number,c varchar2(20));
insert into test_imu values (1,'ABCDEF');
commit;

SCOTT@book> select rowid,id from test_imu;
ROWID                      ID
------------------ ----------
AAAXFvAAEAAAA1/AAA          1

SCOTT@book> @ rowid AAAXFvAAEAAAA1/AAA
    OBJECT       FILE      BLOCK        ROW ROWID_DBA            DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
     94575          4       3455          0  0x1000D7F           4,3455               alter system dump datafile 4 block 3455

2.测试：
--//执行alter system checkpoint;多次保证脏块写盘。
SYS@book> select * from v$sysstat where name like '%IMU%';
STATISTIC# NAME                                          CLASS      VALUE    STAT_ID
---------- ---------------------------------------- ---------- ---------- ----------
       374 IMU commits                                     128       9998 1914489094
       375 IMU Flushes                                     128       1015 2099506212
       376 IMU contention                                  128         16 2909373607
       377 IMU recursive-transaction flush                 128         25 2591100633
       378 IMU undo retention flush                        128          0 2087226422
       379 IMU ktichg flush                                128         45 1206609541
       380 IMU bind flushes                                128          0 2756376339
       381 IMU mbu flush                                   128          0 3723686946
       382 IMU pool not allocated                          128        129  659017805
       383 IMU CR rollbacks                                128         23 2225124543
       384 IMU undo allocation size                        128   49745632  244193920
       385 IMU Redo allocation size                        128   11180980 3945654623
       386 IMU- failed to get a private strand             128        129 2412863545
13 rows selected.

--//session 1:
SCOTT@book> update test_imu set c='abcdef';
1 row updated.

--//session 2:
SYS@book> alter system checkpoint;
System altered.

SYS@book> select * from v$sysstat where name like '%IMU%';
STATISTIC# NAME                                          CLASS      VALUE    STAT_ID
---------- ---------------------------------------- ---------- ---------- ----------
       374 IMU commits                                     128       9998 1914489094
       375 IMU Flushes                                     128       1015 2099506212
       376 IMU contention                                  128         16 2909373607
       377 IMU recursive-transaction flush                 128         25 2591100633
       378 IMU undo retention flush                        128          0 2087226422
       379 IMU ktichg flush                                128         45 1206609541
       380 IMU bind flushes                                128          0 2756376339
       381 IMU mbu flush                                   128          0 3723686946
       382 IMU pool not allocated                          128        129  659017805
       383 IMU CR rollbacks                                128         23 2225124543
       384 IMU undo allocation size                        128   49745848  244193920
       385 IMU Redo allocation size                        128   11180980 3945654623
       386 IMU- failed to get a private strand             128        129 2412863545

13 rows selected.
--//IMU Flushes 前后次数不变，还是1015.

BBED> x  /rnc dba  4,3455 *kdbr[0]
rowdata[0]                                  @8175
----------
flag@8175: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8176: 0x00
cols@8177:    2

col    0[2] @8178: 1
col    1[6] @8181: ABCDEF
--//你可以发现并没有将脏块写盘。
--//注：你可以反复多次，有时候会写盘有时候不会，不能以一次的结果下定论。

3.总结：
--//也就是alter system checkpoint ;不一定会IMU Flushes。实际上即使触发IMU Flushes，该脏块也不一定写盘，可能是别的脏块写盘。
--//至于什么情况下导致这样脏块写盘，我不是很清楚。总之你执行alter system checkpoint ;多次,肯定会出现写盘情况。
SYS@book> alter system checkpoint;
System altered.

SYS@book> select * from v$sysstat where name like '%IMU%';
STATISTIC# NAME                                          CLASS      VALUE    STAT_ID
---------- ---------------------------------------- ---------- ---------- ----------
       374 IMU commits                                     128      10001 1914489094
       375 IMU Flushes                                     128       1016 2099506212
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~       
       376 IMU contention                                  128         17 2909373607
       377 IMU recursive-transaction flush                 128         25 2591100633
       378 IMU undo retention flush                        128          0 2087226422
       379 IMU ktichg flush                                128         45 1206609541
       380 IMU bind flushes                                128          0 2756376339
       381 IMU mbu flush                                   128          0 3723686946
       382 IMU pool not allocated                          128        129  659017805
       383 IMU CR rollbacks                                128         23 2225124543
       384 IMU undo allocation size                        128   49753216  244193920
       385 IMU Redo allocation size                        128   11180980 3945654623
       386 IMU- failed to get a private strand             128        129 2412863545
13 rows selected.

BBED> x  /rnc dba  4,3455 *kdbr[0]
rowdata[0]                                  @8175
----------
flag@8175: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8176: 0x01
cols@8177:    2

col    0[2] @8178: 1
col    1[6] @8181: abcdef
--//现在已经脏块写盘。

4.附上imux.sql脚本：
$ cat imux.sql
column sid format 9999
column minutes format 9999999
column program format a43
column event format a48

select
    sid,
    round(( sysdate - to_date(ktcxbstm,'mm/dd/rr hh24:mi:ss') ) * 24 * 60)
      minutes,
    decode(bitand(ktcxbflg,2),2,1,0) bit2,
    to_number(ktifprpc,'xxxxxxxxxxxxxxxx') -
    to_number(ktifprpb,'xxxxxxxxxxxxxxxx')  redo_bytes,
    to_number(ktifpupc, 'xxxxxxxxxxxxxxxx') -
    to_number(ktifpupb, 'xxxxxxxxxxxxxxxx') undo_bytes,
    program,
    event
  from x$ktifp, x$ktcxb, v$session ses
  where ktifpxcb = ktcxbxba and ktcxbses = saddr
  order by ktcxbstm desc ;