透过Buffer cache了解data block在DML操作下的状态演变

一句DML操作对数据表的修改看似很简单：把老的记录值替换成新的记录值，插入或者删除一条记录，这个看似简单的操作但在buffer cache里却有着不一般的经历，为实现读一致性，一个block在buffer cache里可能有多个版本，在DML的过程中，buffer cache里的block经历了一系列的变化。本文以update、insert、delete操作为例揭示了buffer cache里data block状态是如何变化的，有助于我们进一步了解事务的本质及buffer cache的运作机制###创建测试表
create table scott.t1118_1 tablespace ts1116 as select * from all_users;Table createdselect data_object_id from dba_objects where object_name="T1118_1";
DATA_OBJECT_ID
----------------
41231col segment_name format a15
col owner format a15
set linesize 100
select owner,segment_name,header_file,header_block from dba_segments where segment_name="T1118_1";
OWNER SEGMENT_NAME HEADER_FILE HEADER_BLOCK
--------------- --------------- ----------- ------------
SCOTT T1118_1 5 60682###create table后，x$bh里有了三条记录
SYS@tstdb1-SQL> select obj,addr,hladdr,flag,lru_flag,class,state,dbarfil,dbablk,ba,tch from x$bh where obj=41231;
OBJ ADDR HLADDR FLAG LRU_FLAG CLASS STATE DBARFIL DBABLK BA TCH
---------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ----------
41231 00000001109968B8 07000001B6B503D8 33554433 0 4 1 5 60682 070000014A920000 1
41231 00000001109968B8 07000001B6C811F8 33554433 0 9 1 5 60681 07000000F4010000 1
41231 00000001109968B8 07000001B6DB20E0 33554433 0 8 1 5 60680 0700000102904000 1
这三条记录分别对应3个block
block 5/60680：1st level bitmap block
FLAG：33554433 换算成16进制后：0x2000001=0x2000000（Redo Generated since block read）+0x00001（buffer dirty）
CLASS: 8, 表示1st level bmb
STATE：1，表示Execlusive current
TCH: 1，表示Touch count

block 5/60681：2st level bitmap block
FLAG：33554433 换算成16进制后：0x2000001=0x2000000（Redo Generated since block read）+0x00001（buffer dirty）
CLASS: 9, 表示2nd level bmb
STATE：1，表示Execlusive current
TCH: 1，表示Touch count

block 5/60682：segment header
FLAG：33554433 换算成16进制后：0x2000001=0x2000000（Redo Generated since block read）+0x00001（buffer dirty）
CLASS: 4, 表示segment header
STATE：1，表示Execlusive current
TCH: 1，表示Touch count
###过一会儿再去查这三条记录的状态，发现和上一次结果相比FLAG值发生了变化
select obj,addr,hladdr,flag,lru_flag,class,state,dbarfil,dbablk,ba,tch from x$bh where obj=41231;
OBJ ADDR HLADDR FLAG LRU_FLAG CLASS STATE DBARFIL DBABLK BA TCH
---------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ----------
41231 0000000110A4AA48 07000001B6B503D8 35651584 0 4 1 5 60682 070000014A920000 1
41231 0000000110A4AA48 07000001B6C811F8 35651584 0 9 1 5 60681 07000000F4010000 1
41231 0000000110A4AA48 07000001B6DB20E0 35651584 0 8 1 5 60680 0700000102904000 1
FLAG：35651584换算成16进制为0x2200000=0x2000000（Redo Generated since block read）+ 0x200000（Buffer has been written once），表示这三个block已经写到了磁盘上，不再是dirty block了
###对表T1118_1做一次查询，看看x$bh视图内容变化情况
SYS@tstdb1-SQL> select * from scott.t1118_1 where username="SYS";
USERNAME USER_ID CREATED
------------------------------ ---------- -----------------
SYS 0 20141110 21:16:12
select obj,addr,hladdr,flag,lru_flag,class,state,dbarfil,dbablk,ba,tch from x$bh where obj=41231
OBJ ADDR HLADDR FLAG LRU_FLAG CLASS STATE DBARFIL DBABLK BA TCH
---------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ----------
41231 0000000110A4AA48 07000001B6B503D8 35651584 0 4 1 5 60682 070000014A920000 2
41231 0000000110A4AA48 07000001B6C811F8 35651584 0 9 1 5 60681 07000000F4010000 1
41231 0000000110A4AA48 07000001B6D3F4F0 524288 0 1 1 5 60683 0700000162014000 1
41231 0000000110A4AA48 07000001B6DB20E0 35651584 0 8 1 5 60680 0700000102904000 1
多出了一行对应block 5/60683，这行对应的是data block
FLAG：524288 换算成16进制后：0x80000（sequential scan only flag）
CLASS: 1, 表示data block
STATE：1，表示Execlusive current
TCH: 1，表示Touch count

同时注意到block 5/60682这行的TCH增加到了2，因为扫描表的时候必然先要访问segment header
###对表T1118_1做update，先不提交
---session 1:
SYS@tstdb1-SQL> update scott.t1118_1 set user_id=100 where username="SYS";

1 row updated.
SYS@tstdb1-SQL> select obj,indx,addr,hladdr,flag,lru_flag,class,state,dbarfil,dbablk,ba,tch from x$bh where obj=41231;

OBJ INDX ADDR HLADDR FLAG LRU_FLAG CLASS STATE DBARFIL DBABLK BA TCH
---------- ---------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ----------
41231 31275 000000011099C328 07000001B6B503D8 35651584 0 4 1 5 60682 070000014A920000 3
41231 147533 000000011099C328 07000001B6C811F8 35651584 0 9 1 5 60681 07000000F4010000 1
41231 219283 000000011099C328 07000001B6D3F4F0 33554433 0 1 1 5 60683 07000001084A2000 1
41231 219284 000000011099C1E0 07000001B6D3F4F0 524288 2 1 3 5 60683 0700000162014000 1
41231 280262 000000011099C328 07000001B6DB20E0 35651584 0 8 1 5 60680 0700000102904000 1
（因为BA表示block在内存中的物理地址，所以后续以BA来标示某一行）
新增一行BA=07000001084A2000，这行里
FLAG：33554433，换算成16进制：0x2000001=0x200000（Buffer has been written once）+0x00001（buffer dirty）
CLASS：1, 表示data block
STATE：1，表示Exclusive current
TCH: 1，表示Touch count
同时注意到BA=0700000162014000，这行里
LRU_FLAG：2,表示moved to tail of lru，放到了LRU的最末端，因为截止目前block 5/60683已经被扫描了两次（一次是在select时,一次是在update时）
STATE：3，表示buffer consistant read，数据库块被修改前会先复制一份consistent read的版本
###这时我们另开一个会话，select 5/60683 这个块
---session 2：select之前我们通过x$bh发现BA=07000001084A2000这行的FLAG变成了35651584，表示这个block的内容虽然还未提交但是已经flush到磁盘了
SYS@tstdb1-SQL> select obj,indx,addr,hladdr,flag,lru_flag,class,state,dbarfil,dbablk,ba,tch from x$bh where obj=41231;

OBJ INDX ADDR HLADDR FLAG LRU_FLAG CLASS STATE DBARFIL DBABLK BA TCH
---------- ---------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ----------
41231 31284 000000011099BA50 07000001B6B503D8 35651584 0 4 1 5 60682 070000014A920000 3
41231 147564 000000011099BA50 07000001B6C811F8 35651584 0 9 1 5 60681 07000000F4010000 1
41231 219327 000000011099BA50 07000001B6D3F4F0 35651584 0 1 1 5 60683 07000001084A2000 1
41231 219328 000000011099B908 07000001B6D3F4F0 524288 2 1 3 5 60683 0700000162014000 1
41231 280277 000000011099BA50 07000001B6DB20E0 35651584 0 8 1 5 60680 0700000102904000 1
***发起select操作
SYS@tstdb1-SQL> select * from scott.t1118_1 where username="SYS";
USERNAME USER_ID CREATED
------------------------------ ---------- -----------------
SYS 0 20141110 21:16:12
SYS@tstdb1-SQL> select obj,indx,addr,hladdr,flag,lru_flag,class,state,dbarfil,dbablk,ba,tch from x$bh where obj=41231;
OBJ INDX ADDR HLADDR FLAG LRU_FLAG CLASS STATE DBARFIL DBABLK BA TCH
---------- ---------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ----------
41231 31287 000000011099BA50 07000001B6B503D8 35651584 0 4 1 5 60682 070000014A920000 4
41231 147573 000000011099BA50 07000001B6C811F8 35651584 0 9 1 5 60681 07000000F4010000 1
41231 219340 000000011099BA50 07000001B6D3F4F0 524288 0 1 3 5 60683 0700000105276000 1
41231 219341 000000011099B908 07000001B6D3F4F0 35651585 0 1 1 5 60683 07000001084A2000 1
41231 219342 000000011099B7C0 07000001B6D3F4F0 524288 2 1 3 5 60683 0700000162014000 1
41231 280283 000000011099BA50 07000001B6DB20E0 35651584 0 8 1 5 60680 0700000102904000 1
发现又多出了一行BA=0700000105276000，这行里
FLAG：524288 换算成16进制后：0x80000（sequential scan only flag）
STATE：3，表示buffer consistent read，表示后续对于block 5/60683的一致性读将会用到这一行，一个data block在buffer cache里可能会拥有多个consistent read 版本，后面也会谈及
TCH：1，表示touch count为1
同时发现BA=07000001084A2000，这行里
FLAG：35651585，转换成16进制0x2200001=0x2000000（Redo Generated since block read）+0x200000（Buffer has been written once）+0x0001（buffer dirty）
***之后再次查询发现BA=07000001084A2000，这行的FLAG又变回了35651584，转换成16进制0x2200001=0x2000000（Redo Generated since block read）+0x200000（Buffer has been written once）
SYS@tstdb1-SQL> select obj,indx,addr,hladdr,flag,lru_flag,class,state,dbarfil,dbablk,ba,tch from x$bh where obj=41231;
OBJ INDX ADDR HLADDR FLAG LRU_FLAG CLASS STATE DBARFIL DBABLK BA TCH
---------- ---------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ----------
41231 31287 000000011099BA50 07000001B6B503D8 35651584 0 4 1 5 60682 070000014A920000 4
41231 147573 000000011099BA50 07000001B6C811F8 35651584 0 9 1 5 60681 07000000F4010000 1
41231 219340 000000011099BA50 07000001B6D3F4F0 524288 0 1 3 5 60683 0700000105276000 1
41231 219341 000000011099B908 07000001B6D3F4F0 35651584 0 1 1 5 60683 07000001084A2000 1
41231 219342 000000011099B7C0 07000001B6D3F4F0 524288 2 1 3 5 60683 0700000162014000 1
41231 280283 000000011099BA50 07000001B6DB20E0 35651584 0 8 1 5 60680 0700000102904000 1 ###回到session 1查询
session 1:
SYS@tstdb1-SQL> select * from scott.t1118_1 where username="SYS";

USERNAME USER_ID CREATED
------------------------------ ---------- -----------------
SYS 100 20141110 21:16:12
SYS@tstdb1-SQL> select obj,indx,addr,hladdr,flag,lru_flag,class,state,dbarfil,dbablk,ba,tch from x$bh where obj=41231;

OBJ INDX ADDR HLADDR FLAG LRU_FLAG CLASS STATE DBARFIL DBABLK BA TCH
---------- ---------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ----------
41231 31290 000000011099BA50 07000001B6B503D8 35651584 0 4 1 5 60682 070000014A920000 5
41231 147591 000000011099BA50 07000001B6C811F8 35651584 0 9 1 5 60681 07000000F4010000 1
41231 219366 000000011099BA50 07000001B6D3F4F0 524288 0 1 3 5 60683 0700000105276000 1
41231 219367 000000011099B908 07000001B6D3F4F0 35651584 0 1 1 5 60683 07000001084A2000 2
41231 219368 000000011099B7C0 07000001B6D3F4F0 524288 2 1 3 5 60683 0700000162014000 1
41231 280295 000000011099BA50 07000001B6DB20E0 35651584 0 8 1 5 60680 0700000102904000 1
BA=07000001084A2000,这行里
TCH：2，比原来增加了1
STATE：1，表示Exclusive current，因为查询的是自己修改后的尚未提交的内容
###将session 1回滚
---session 1:
SYS@tstdb1-SQL> rollback;
Rollback complete.
SYS@tstdb1-SQL> select obj,indx,addr,hladdr,flag,lru_flag,class,state,dbarfil,dbablk,ba,tch from x$bh where obj=41231;
OBJ INDX ADDR HLADDR FLAG LRU_FLAG CLASS STATE DBARFIL DBABLK BA TCH
---------- ---------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ----------
41231 31374 000000011099DB50 07000001B6B503D8 35651584 0 4 1 5 60682 070000014A920000 5
41231 148007 000000011099DB50 07000001B6C811F8 35651584 0 9 1 5 60681 07000000F4010000 1
41231 219991 000000011099DB50 07000001B6D3F4F0 524288 0 1 3 5 60683 0700000105276000 1
41231 219992 000000011099DA08 07000001B6D3F4F0 35651584 0 1 1 5 60683 07000001084A2000 3
41231 219993 000000011099D8C0 07000001B6D3F4F0 524288 2 1 3 5 60683 0700000162014000 1
41231 280351 000000011099DB50 07000001B6DB20E0 35651584 0 8 1 5 60680 0700000102904000 1
回滚后BA=07000001084A2000所在行，
TCH：3，比原来增加了1
###再次发起scott.t1118_1表的查询
SYS@tstdb1-SQL> select * from scott.t1118_1 where username="SYS";
USERNAME USER_ID CREATED
------------------------------ ---------- -----------------
SYS 0 20141110 21:16:12
SYS@tstdb1-SQL> select obj,indx,addr,hladdr,flag,lru_flag,class,state,dbarfil,dbablk,ba,tch from x$bh where obj=41231
OBJ INDX ADDR HLADDR FLAG LRU_FLAG CLASS STATE DBARFIL DBABLK BA TCH
---------- ---------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ----------
41231 31384 000000011099DB50 07000001B6B503D8 35651584 0 4 1 5 60682 070000014A920000 6
41231 148032 000000011099DB50 07000001B6C811F8 35651584 0 9 1 5 60681 07000000F4010000 1
41231 220026 000000011099DB50 07000001B6D3F4F0 524288 0 1 3 5 60683 0700000105276000 1
41231 220027 000000011099DA08 07000001B6D3F4F0 35651584 0 1 1 5 60683 07000001084A2000 4
41231 220028 000000011099D8C0 07000001B6D3F4F0 524288 2 1 3 5 60683 0700000162014000 1
41231 280364 000000011099DB50 07000001B6DB20E0 35651584 0 8 1 5 60680 0700000102904000 1
发现访问的是以exclusive current（state=1）状态存在的BA=07000001084A2000地址里的内容，因为我们看到了TCH=4，比上一次增加了1，且当前没有活动事务在修改这个block，所以不需要去访问consistent read（state）模式的block
回顾之前
BA=07000001084A2000地址里的数据在session 1前一次update之后commit之前被查询过一次当时查到的内容是
USERNAME USER_ID CREATED
------------------------------ ---------- -----------------
SYS 100 20141110 21:16:12
rollback的时候又被touch过一次（TCH从2增加到3）
现在再次查到BA=07000001084A2000里的内容为
USERNAME USER_ID CREATED
------------------------------ ---------- -----------------
SYS 0 20141110 21:16:12
猜测在rollback的时候BA=07000001084A2000内容已被恢复到了修改前的状态
###insert一条记录到scott.t1118_1表
insert into scott.t1118_1 values（"NEWUSER",1000,sysdate）; <---先不提交
SYS@tstdb1-SQL> select obj,indx,addr,hladdr,flag,lru_flag,class,state,dbarfil,dbablk,ba,tch from x$bh where obj=41231;
OBJ INDX ADDR HLADDR FLAG LRU_FLAG CLASS STATE DBARFIL DBABLK BA TCH
---------- ---------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ----------
41231 31410 000000011099E2F8 07000001B6B503D8 35651585 0 4 1 5 60682 070000014A920000 7
41231 58880 000000011099E2F8 07000001BCD31B80 33554433 0 1 1 5 60687 07000000FF9F0000 1
41231 103516 000000011099E2F8 07000001BCDA4770 33554433 0 1 1 5 60684 07000000E2816000 1
41231 148171 000000011099E2F8 07000001B6C811F8 35651584 0 9 1 5 60681 07000000F4010000 2
41231 175696 000000011099E2F8 07000001BCE62A68 33554433 0 1 1 5 60686 07000000E754E000 1
41231 220242 000000011099E2F8 07000001B6D3F4F0 524288 0 1 3 5 60683 0700000105276000 1
41231 220243 000000011099E1B0 07000001B6D3F4F0 35651584 0 1 1 5 60683 07000001084A2000 4
41231 220244 000000011099E068 07000001B6D3F4F0 524288 2 1 3 5 60683 0700000162014000 1
41231 280378 000000011099E2F8 07000001B6DB20E0 35651585 0 8 1 5 60680 0700000102904000 2
41231 325203 000000011099E2F8 07000001BCF93888 33554433 0 1 1 5 60685 07000000E74CC000 1
insert操作之后发现obj=41231这个对象在buffer cache里多出了4条记录，这4条记录分属于60684~60687四个block，且原来的60680（1st level bitmap block）、60681（2nd level bitmap block）、60682（segment header）三个block的TCH都增加了1；这是因为insert的时候申请了新的空间来存放新的数据，要知道是否有空闲空间可申请，必须先要访问1st and 2nd level bitmap block，对segment header的访问是必然的不多解释了
select dbms_rowid.rowid_block_number（rowid）,dbms_rowid.rowid_relative_fno（rowid） from scott.t1118_1;
DBMS_ROWID.ROWID_BLOCK_NUMBER（ROWID） DBMS_ROWID.ROWID_RELATIVE_FNO（ROWID）
------------------------------------ ------------------------------------
60683 5
60683 5
60683 5
60683 5
60683 5
60683 5
60683 5
60683 5
60683 5
60683 5
60683 5
60683 5
60683 5
60683 5
60683 5
60683 5
60683 5
60683 5
60683 5
60683 5
60683 5
60685 5 <----新插入的行放在了60685这个新申请的block里
虽然只使用了60685，从下面的输出可以推断出60684、60686、60687也被格式化了：
***dba_extents
SYS@tstdb1-SQL> col owner format a10
SYS@tstdb1-SQL> col segment_name format a20
SYS@tstdb1-SQL> set linesize 100
SYS@tstdb1-SQL> select owner,segment_name,relative_fno,block_id,blocks from dba_extents where segment_name="T1118_1"
OWNER SEGMENT_NAME RELATIVE_FNO BLOCK_ID BLOCKS
---------- -------------------- ------------ ---------- ----------
SCOTT T1118_1 5 60680 8
***dbms_space.space_usage过程的执行结果显示有5个Data block被formatted了
SYS@tstdb1-SQL> declare
2 unformatted_blocks NUMBER;
3 unformatted_bytes NUMBER;
4 fs1_blocks NUMBER;
5 fs1_bytes NUMBER;
6 fs2_blocks NUMBER;
7 fs2_bytes NUMBER;
8 fs3_blocks NUMBER;
9 fs3_bytes NUMBER;
10 fs4_blocks NUMBER;
11 fs4_bytes NUMBER;
12 full_blocks NUMBER;
13 full_bytes NUMBER;
14 begin
15 dbms_space.space_usage（"SCOTT","T1118_1","TABLE",unformatted_blocks,unformatted_bytes,fs1_blocks,fs1_bytes,fs2_blocks,fs2_bytes,fs3_blocks,fs3_bytes,fs4_blocks,fs4_bytes,full_blocks,full_bytes）;
16 dbms_output.put_line（"unformatted_blocks:"||unformatted_blocks）;
17 dbms_output.put_line（"fs1_blocks:"||fs1_blocks）;
18 dbms_output.put_line（"fs2_blocks:"||fs2_blocks）;
19 dbms_output.put_line（"fs3_blocks:"||fs3_blocks）;
20 dbms_output.put_line（"fs4_blocks:"||fs4_blocks）;
21 dbms_output.put_line（"full_blocks:"||full_blocks）;
22 end;
23 /
unformatted_blocks:0
fs1_blocks:0
fs2_blocks:0
fs3_blocks:0
fs4_blocks:4 <---从60683开始有5个block被格式化过了
full_blocks:1
***发起对scott.t1118_1的查询
SYS@tstdb1-SQL> select * from scott.t1118_1 where username="SYS";
USERNAME USER_ID CREATED
------------------------------ ---------- -----------------
SYS 0 20141110 21:16:12

SYS@tstdb1-SQL> select obj,indx,addr,hladdr,flag,lru_flag,class,state,dbarfil,dbablk,ba,tch from x$bh where obj=41231
OBJ INDX ADDR HLADDR FLAG LRU_FLAG CLASS STATE DBARFIL DBABLK BA TCH
---------- ---------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ----------
41231 31787 000000011099E2F8 07000001B6B503D8 35651584 0 4 1 5 60682 070000014A920000 8
41231 59572 000000011099E2F8 07000001BCD31B80 35651584 0 1 1 5 60687 07000000FF9F0000 2
41231 104826 000000011099E2F8 07000001BCDA4770 35651584 0 1 1 5 60684 07000000E2816000 2
41231 150065 000000011099E2F8 07000001B6C811F8 35651584 0 9 1 5 60681 07000000F4010000 2
41231 177953 000000011099E2F8 07000001BCE62A68 35651584 0 1 1 5 60686 07000000E754E000 2
41231 223088 000000011099E2F8 07000001B6D3F4F0 524288 0 1 3 5 60683 0700000105276000 1
41231 223089 000000011099E1B0 07000001B6D3F4F0 35651584 0 1 1 5 60683 07000001084A2000 5
41231 223090 000000011099E068 07000001B6D3F4F0 524288 2 1 3 5 60683 0700000162014000 1
41231 280093 000000011099E2F8 07000001B6DB20E0 35651584 0 8 1 5 60680 0700000102904000 2
41231 324995 000000011099E2F8 07000001BCF93888 524288 0 1 3 5 60685 0700000138622000 1
41231 324996 000000011099E1B0 07000001BCF93888 524288 0 1 3 5 60685 07000000EC7B0000 1
41231 324997 000000011099E068 07000001BCF93888 35651584 0 1 1 5 60685 07000000E74CC000 1
commit;

再次查询后682~687每个block的TCH都加了1，虽然60684、60686、60687三个block里未存放数据但因为在HWM以下所以也被scan到
###从scott.t1118_1表里delete一条记录（因为数据库经历了重启所以无法接着上面的测试了，只能重新开始）
---session 1：delete一条记录，不提交
SYS@tstdb1-SQL> delete from scott.t1118_1 where username="ANONYMOUS";
1 row deleted.
---session 2：排除掉state=0（状态为free）的block
SYS@tstdb1-SQL> select obj,indx,addr,hladdr,flag,lru_flag,class,state,dbarfil,dbablk,ba,tch from x$bh where obj=41231 and state！=0;
OBJ INDX ADDR HLADDR FLAG LRU_FLAG CLASS STATE DBARFIL DBABLK BA TCH
---------- ---------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ----------
41231 8670 0000000110997C58 07000001B6AFD160 0 0 4 1 5 60682 070000015E0E2000 1
41231 16171 0000000110997C58 07000001BCC9B928 524288 0 1 1 5 60687 070000015F4AC000 1
41231 28310 0000000110997C58 07000001BCC9F230 524288 0 1 1 5 60684 070000015E2E8000 1
41231 47972 0000000110997C58 07000001BCCA5180 524288 0 1 1 5 60686 070000015F392000 1
41231 59894 0000000110997C58 07000001B6B0C908 33554433 0 1 1 5 60683 070000015E1D6000 2
41231 59895 0000000110997B10 07000001B6B0C908 524288 2 1 3 5 60683 070000015E1D8000 1
41231 79479 0000000110997C58 07000001BCCAE9D8 524288 0 1 1 5 60685 070000015F268000 1
block 5/60683对应了两行，BA=070000015E1D8000这一行是state=3，代表了before-image，用于一致性读；BA=070000015E1D6000这一行是state=1用于current read，touch次数为两次，推断其中一次是修改前被访问而复制出before-image；
---session 1：访问自己修改后但未提交的结果
select * from scott.t1118_1;
---session 2：
select obj,indx,addr,hladdr,flag,lru_flag,class,state,dbarfil,dbablk,ba,tch from x$bh where obj=41231 and state！=0
OBJ INDX ADDR HLADDR FLAG LRU_FLAG CLASS STATE DBARFIL DBABLK BA TCH
---------- ---------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ----------
41231 9300 0000000110997C58 07000001B6AFD160 0 0 4 1 5 60682 070000015E0E2000 2
41231 17325 0000000110997C58 07000001BCC9B928 524288 0 1 1 5 60687 070000015F4AC000 2
41231 30333 0000000110997C58 07000001BCC9F230 524288 0 1 1 5 60684 070000015E2E8000 2
41231 51415 0000000110997C58 07000001BCCA5180 524288 0 1 1 5 60686 070000015F392000 2
41231 64226 0000000110997C58 07000001B6B0C908 35651584 0 1 1 5 60683 070000015E1D6000 3
41231 64227 0000000110997B10 07000001B6B0C908 524288 2 1 3 5 60683 070000015E1D8000 1
41231 85239 0000000110997C58 07000001BCCAE9D8 524288 0 1 1 5 60685 070000015F268000 2
BA=070000015E1D6000对饮的block 5/60683所在的行tch增加了1，因为session 1访问的是所有block的current version
---session 3:
select * from scott.t1118_1;
select obj,indx,addr,hladdr,flag,lru_flag,class,state,dbarfil,dbablk,ba,tch from x$bh where obj=41231 and state！=0;
OBJ INDX ADDR HLADDR FLAG LRU_FLAG CLASS STATE DBARFIL DBABLK BA TCH
---------- ---------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ----------
41231 9300 0000000110997C58 07000001B6AFD160 0 0 4 1 5 60682 070000015E0E2000 3
41231 17329 0000000110997C58 07000001BCC9B928 524288 0 1 1 5 60687 070000015F4AC000 3
41231 30337 0000000110997C58 07000001BCC9F230 524288 0 1 1 5 60684 070000015E2E8000 3
41231 51426 0000000110997C58 07000001BCCA5180 524288 0 1 1 5 60686 070000015F392000 3
41231 64237 0000000110997C58 07000001B6B0C908 524288 0 1 3 5 60683 0700000157EC0000 1
41231 64238 0000000110997B10 07000001B6B0C908 35651585 0 1 1 5 60683 070000015E1D6000 3
41231 64239 00000001109979C8 07000001B6B0C908 524288 2 1 3 5 60683 070000015E1D8000 1
41231 85256 0000000110997C58 07000001BCCAE9D8 524288 0 1 1 5 60685 070000015F268000 3
因为session 3执行的是consistent read，可以看到新增的BA=0700000157EC0000这行是5/60683的before-image，并没有复用同样是before-image的BA=070000015E1D8000这行
---session 3: 继续执行select
***查询5/60683的before-image个数
SYS@tstdb1-SQL> select obj,indx,addr,hladdr,flag,lru_flag,class,state,dbarfil,dbablk,ba,tch from x$bh where obj=41231 and dbablk=60683 and state=3;
OBJ INDX ADDR HLADDR