[20201207]關於ORACLE IMU的一些疑問.txt

[20201207]關於ORACLE IMU的一些疑問.txt

--//關於oracle IMU，我自己本人有太多的疑問，我自己很久以前在測試栽過跟頭。
--// http://blog.itpub.net/267265/viewspace-2137720/ => [20170421]警惕開啟IMU對測試的影響.txt

--//前幾天看別人的測試： =>關於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 ;