实验目的：oracle 11gR2 里的delayed block cleanout特性研究
基础知识：
Fast commit cleanout
DML语句后，在被修改的block从buffer cache写回到磁盘之前执行commit。这种情况下，更新block scn，block itl事务槽上的scn，以及undo segment header事务表相应槽位上的scn，这三个scn是一致的，并且更新Itl槽位上的Flag为--U-的形式。Fast commit cleanout发生后不会清理Itl Lck和row data里的lb字段信息。
Delayed block cleanout
DML语句后，在被修改的block从buffer cache写回到磁盘之后执行commit。更新的只是undo segment header 事务表相应槽位上的scn和Itl lck标志，而并未做Itl里其它字段的更新，等待下次DML(多个itl slot)或者select语句(单个itl slot)访问此block的时候再对Itl Flag、Itl lck、Itl scn/fsc字段作更新。实测下来11g中delayed block cleanout的行为在以下两种场景中略有不同：
(1) 使用到data block中前两个预分配的Itl slot的transaction按照delayed block cleanout的条件(DML->flush buffer_cache->commit)去模拟，其cleanout的行为仅由访问该block的DML语句触发，select语句并不会触发，这一个特点与fast commit cleanout有些相似：更新DML语句所使用的slot上的Itl Flag、Itl lck、Itl scn(与block scn的值相同)，同时对另一个曾经被已提交事务占用的slot进行cleanout操作(加上C---标志)，Lewis把此现象称作：delayed logging block cleanout，以下是他对此现象的描述：
let’s go back to a block that was subject to commit cleanout. If you query this block you can see the effects of the committed transaction, but since the commit cleanout wrote the commit SCN into its ITL slot you know when the transaction committed and generally don’t need to do anything to tidy the block up as you read it. However if you want to update the block you become responsible for finishing the cleanout of the block – in fact you may even want to re-use that ITL entry and update some of the rows which are still showing a lock byte. So, at this point, you complete the block cleanout, and generate redo that describes not only the changed you are now making but also the earlier commit cleanout. This process is known as ‘delayed logging’ block cleanout – because the redo log for the commit cleanout has been delayed until this moment
This process is known as ‘delayed logging’ block cleanout – because the redo log for the commit cleanout has been delayed until this moment.This process is known as ‘delayed logging’ block cleanout – because the redo log for the commit cleanout has been delayed until this moment.
(2) 使用到自动分配的Itl slot的transaction是严格按照delayed block cleanout的本义执行的：commit后不更新Itl中的Flag 、Scn/Fsc这两个字段值，等到下一次DML或select访问该block之后并且进行了flush buffer_cache操作后才进行更新，同时更新的还有block scn、csc等字段
实验步骤：
####实验使用scott.emp表，先对表的block做一个dump，得到初始状态
alter system dump datafile 4 block 151;
select * from v$diag_info;
/oracle/app/oracle/diag/rdbms/zwcsdb/zwcsdb/trace/zwcsdb_ora_30605382.trc --初始dump
scn: 0x0b78.2772cef5 csc: 0xb78.2772cef3
Itl Xid Uba Flag Lck Scn/Fsc
[C1] 0x0001.014.00020128 0x00c00179.4506.17 --U- 1 fsc 0x0000.2772cef5
0x02 0x000b.01b.00020e1b 0x00c00620.29ed.22 C--- 0 scn 0x0b78.2772ce94
####fast commit cleanout 1st time，占用了0x02，因为该slot已经被cleanout了
update scott.emp set ename='SMITH' where ename='SMITHHHHHH';
commit;
alter system flush buffer_cache;
alter system dump datafile 4 block 151;
select * from v$diag_info;
zwcsdb_ora_15597762.trc -- fast commit cleanout /scn: 0x0b78.2776cebb csc: 0xb78.2772cef3
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0001.014.00020128 0x00c00179.4506.17 --U- 1 fsc 0x0000.2772cef5
0x02 0x000b.01d.000210c5 0x00c012f9.2ae6.04 --U- 1 fsc 0x0005.2776cebb
####fast commit cleanout 2nd time，占用了0x01，其实此时0x02也可以被占用，因为0x02的transaction也已经提交了，可见oracle会根据scn/fsc选择更早释放出的slot，同时对另一个已经释放出来的slot进行cleanout，csc已经较上面推进了
update scott.emp set ename='ALLEN' where ename='ALLENNNN';
commit;
alter system flush buffer_cache;
alter system dump datafile 4 block 151;
select * from v$diag_info;
zwcsdb_ora_22151348.trc -- fast commit cleanout /scn: 0x0b78.2776cf18 csc[C2] : 0xb78.2776cf15
Itl Xid Uba Flag Lck Scn/Fsc
[C3] 0x000b.01c.000210d5 0x00c012f9.2ae6.05 --U- 1 fsc 0x0003.2776cf18
0x02 0x000b.01d.000210c5 0x00c012f9.2ae6.04 C--- 0 scn 0x0b78.2776cebb
####delayed block cleanout 1st time，更新了itl scn和 block scn(两者值相等)，但csc未更新，没有发生block cleanout，重用了slot 0x02，Flag从C---改成了--U-,
update scott.emp set ename='JONESSSS' where ename='JONES';
alter system flush buffer_cache;
commit;
alter system flush buffer_cache;
alter system dump datafile 4 block 151;
select * from v$diag_info;
zwcsdb_ora_32506056.trc --delayed block cleanout after flush buffer_cache
scn: 0x0b78.2776d0a2 csc: 0xb78.2776cf15
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000b.01c.000210d5 0x00c012f9.2ae6.05 --U- 1 fsc 0x0003.2776cf18
[C4] 0x000b.01b.000210de 0x00c012fa.2ae6.16 --U- 1 fsc 0x0000.2776d0a2
####delayed block cleanout 2nd time，使用slot 0x01，cleanout slot 0x02，csc值比dml commit的scn稍小一些，可推断cleanout是dml commit所引发的
update scott.emp set ename='MARTINNNNN' where ename='MARTIN';
alter system flush buffer_cache;
commit;
alter system flush buffer_cache;
alter system dump datafile 4 block 151;
select * from v$diag_info;
zwcsdb_ora_15007812.trc --delay block cleanout 2nd time plus flush buffer_cache /scn: 0x0b78.2776d2a0 csc: 0xb78.2776d29b
Itl Xid Uba Flag Lck Scn/Fsc
[C5] 0x000b.01f.00020f19 0x00c0074f.2ae7.0a --U- 1 fsc 0x0000.2776d2a0
0x02 0x000b.01b.000210de 0x00c012fa.2ae6.16 C--- 0 scn 0x0b78.2776d0a2
阶段结论：在使用block里系统预分配的两个slot的情况下，delayed block cleanout与fast commit cleanout两种方式基本无区别，它们的行为都是：占用Itl的两个slot里更早释放出来的那个slot->更新Flag、Lck、Scn/Fsc->cleanout剩余的已经commit的slot(仅当两个slot的Flag初始状态都为--U-的情况下才会做此操作，若两个slot的初始状态一个是--U-、一个是C---的情况下不会做此操作)->推进block scn和csc
####模拟将预分配的两个slot都占满的情况
session 1：
update scott.emp set ename='BLAKEEEE' where ename='BLAKE';
session 2:
update scott.emp set ename='CLARKKKK' where ename='CLARK';
alter system flush buffer_cache;
alter system dump datafile 4 block 151;
select * from v$diag_info;
zwcsdb_ora_22151216.trc --- scn: 0x0b78.2776d748 csc: 0xb78.2776d747
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.000.00040f70 0x00c0060d.558b.1b ---- 1 fsc 0x0000.00000000
0x02 0x000b.019.00020f8d 0x00c00751.2ae7.1d ---- 1 fsc 0x0000.00000000
####开启第3个session进行fast commit cleanout，新开出了第3个slot，csc比上一轮推进了，可以推断发生了cleanout但是没有看到Itl Flag字段的C标志
session 3:
update scott.emp set ename='SCOTTTTT' where ename='SCOTT';
commit;
alter system flush buffer_cache;
alter system dump datafile 4 block 151;
select * from v$diag_info;
zwcsdb_ora_25559182.trc --- scn: 0x0b78.2776d7f1 csc: 0xb78.2776d7ec
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.000.00040f70 0x00c0060d.558b.1b ---- 1 fsc 0x0000.00000000
0x02 0x000b.019.00020f8d 0x00c00751.2ae7.1d ---- 1 fsc 0x0000.00000000
0x03 0x0006.01d.0001b2f5 0x00c000d3.41fa.1e --U- 1 fsc 0x0000.2776d7f1
####开启第4个session进行fast commit cleanout，重用第3个slot，又发生了cleanout，因为csc又推进了
session 4:
update scott.emp set ename='KINGGGG' where ename='KING';
commit;
alter system flush buffer_cache;
alter system dump datafile 4 block 151;
select * from v$diag_info;
zwcsdb_ora_24772802.trc --- scn: 0x0b78.2776d86a csc: 0xb78.2776d864
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.000.00040f70 0x00c0060d.558b.1b ---- 1 fsc 0x0000.00000000
0x02 0x000b.019.00020f8d 0x00c00751.2ae7.1d ---- 1 fsc 0x0000.00000000
0x03 0x0009.01f.0002e43a 0x00c0253c.3571.1a --U- 1 fsc 0x0000.2776d86a
####开启第5个session模拟delayed block cleanout，发现竟然出现了所谓的delayed block cleanout现象，对于下面的0x03槽位，commit后Flag、scn/fsc标志位没有更改
session 5:
update scott.emp set ename='TURNERRRR' where ename='TURNER';
alter system flush buffer_cache;
commit;
alter system flush buffer_cache;
alter system dump datafile 4 block 151;
select * from v$diag_info;
zwcsdb_ora_11534458.trc --- scn: 0x0b78.2776d9cf csc: 0xb78.2776d9ca
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.000.00040f70 0x00c0060d.558b.1b ---- 1 fsc 0x0000.00000000
0x02 0x000b.019.00020f8d 0x00c00751.2ae7.1d ---- 1 fsc 0x0000.00000000
0x03 0x0009.001.0002d621 0x00c0160f.3574.04 ---- 1 fsc 0x0000.00000000
####另开一个session对scott.emp做select发现出现了redosize
SQL> set autotrace on stat;
SQL> select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- ------------ ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30
7521 WARDDD SALESMAN 7698 22-FEB-81 1250 500
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- ------------ ---------- ----------
DEPTNO
----------
7566 JONESSSS MANAGER 7839 02-APR-81 2975
20
7654 MARTINNNNN SALESMAN 7698 28-SEP-81 1250 1400
30
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- ------------ ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 09-JUN-81 2450
10
7788 SCOTTTTT ANALYST 7566 19-APR-87 3000
20
7839 KINGGGG PRESIDENT 17-NOV-81 5000
10
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- ------------ ---------- ----------
DEPTNO
----------
7844 TURNERRRR SALESMAN 7698 08-SEP-81 1500 0
30
7876 ADAMS CLERK 7788 23-MAY-87 1100
20
7900 JAMES CLERK 7698 03-DEC-81 950
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- ------------ ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 03-DEC-81 3000
20
7934 MILLER CLERK 7782 23-JAN-82 1300
10
14 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
17 consistent gets
12 physical reads
116 redo size
1648 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
####紧接着对block再次做dump，dump内容和上一次一样
####第二次select * from scott.emp上还是有redo size发生，此时再次dump，dump内容还是没有变化, 接着再n次的select * from scott.emp，每次都伴随有Redo size
####第n次[C6] dump block发现delayed block cleanout终于如期而至,且此时scn和csc一致，Flag标志变为C---
zwcsdb_ora_8978604.trc(第三次dump的内容) --scn: 0x0b78.2776db60 csc: 0xb78.2776db60
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.000.00040f70 0x00c0060d.558b.1b ---- 1 fsc 0x0000.00000000
0x02 0x000b.019.00020f8d 0x00c00751.2ae7.1d ---- 1 fsc 0x0000.00000000
0x03 0x0009.001.0002d621 0x00c0160f.3574.04 C--- 0 scn 0x0b78.2776d9d2
####再模拟一次delayed block cleanout
update scott.emp set ename='JAMESSSS' where ename='JAMES';
alter system flush buffer_cache;
commit;
alter system flush buffer_cache;
alter system dump datafile 4 block 151;
select * from v$diag_info;
zwcsdb_ora_27263036.trc -- scn: 0x0b78.2776de44 csc: 0xb78.2776de41
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.000.00040f70 0x00c0060d.558b.1b ---- 1 fsc 0x0000.00000000
0x02 0x000b.019.00020f8d 0x00c00751.2ae7.1d ---- 1 fsc 0x0000.00000000
0x03 0x0009.012.0002ddd5 0x00c01616.3574.18 ---- 1 fsc 0x0000.00000000
####select + flush buffer_cache触发delayed block cleanout，
select * from scott.emp;
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
17 consistent gets
15 physical reads
116 redo size
1654 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
Alter system flush buffer_cache
scn: 0x0b78.2776de63 csc: 0xb78.2776de63
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.000.00040f70 0x00c0060d.558b.1b ---- 1 fsc 0x0000.00000000
0x02 0x000b.019.00020f8d 0x00c00751.2ae7.1d ---- 1 fsc 0x0000.00000000
0x03 0x0009.012.0002ddd5 0x00c01616.3574.18 C--- 0 scn 0x0b78.2776de47
####最后再进行一次fast commit cleanout，csc比上一次有所推进，明显发生了delayed block cleanout，但csc与block scn值不等
update scott.emp set ename='FORDDDD' where ename='FORD';
commit;
alter system flush buffer_cache;
alter system dump datafile 4 block 151;
select * from v$diag_info;
zwcsdb_ora_13828174.trc -- scn: 0x0b78.2776dedc csc: 0xb78.2776ded7
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.000.00040f70 0x00c0060d.558b.1b ---- 1 fsc 0x0000.00000000
0x02 0x000b.019.00020f8d 0x00c00751.2ae7.1d ---- 1 fsc 0x0000.00000000
0x03 0x0008.01a.0002ab95 0x00c01593.3ea2.14 --U- 1 fsc 0x0000.2776dedc
阶段结论：在只有一个slot的情况下（当然前提是一个slot够用），每次dml后都会触发cleanout ，对于fast commit cleanout是只能通过dml语句触发才能看到cleanout现象，对于delayed block cleanout则可以通过select操作+flush buffer_cache观察到cleanout现象（当然也可以通过DML语句）。,在fast commit cleanout场景下，block scn大于csc；在delayed block cleanout场景下，delayed block cleanout发生时block scn和csc值相同，且这个值要大于itl scn值。
[C1]---这个slot是2天前update scott.emp set ename='WARDDD' where empno=7521语句使用的，还没有cleanout
[C2]比上面的csc值推进了，可见发生了cleanout
[C3]---update scott.emp set ename='ALLEN' where ename='ALLENNNN' 使用的slot
[C4]---update scott.emp set ename='JONESSSS' where ename='JONES'使用的slot
[C5]---update scott.emp set ename='MARTINNNNN' where ename='MARTIN'使用的slot
[C6]后面的步骤可以证明select 后还需要flush buffer_cache才能更快的触发cleanout
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/53956/viewspace-1319487/ ，如需转载，请注明出处，否则将追究法律责任。
