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

来源：这里教程网时间：2026-03-03 16:17:07 作者：

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