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

当前位置: 数据库>oracle

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

来源: 互联网发布时间：2017-06-22

本文导语: 一句DML操作对数据表的修改看似很简单：把老的记录值替换成新的记录值，插入或者删除一条记录，这个看似简单的操作但在buffer cache里却有着不一般的经历，为实现读一致性，一个block在buffer cache里可能有多个版本，在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 created

select data_object_id from dba_objects where object_name='T1118_1';
DATA_OBJECT_ID
----------------
41231

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

发现又多出了一行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;

###回到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;

回滚后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

发现访问的是以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); 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 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 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;

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;

select * from scott.t1118_1;

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 FLAG LRU_FLAG CLASS STATE DBARFIL DBABLK BA TCH
---------- ---------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ----------
41231 64259 0000000110997C58 07000001B6B0C908 524288 0 1 3 5 60683 0700000157E9C000 1
41231 64260 0000000110997B10 07000001B6B0C908 524288 0 1 3 5 60683 0700000157EC0000 1
41231 64262 0000000110997880 07000001B6B0C908 524288 2 1 3 5 60683 070000015E1D8000 1

select * from scott.t1118_1;

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 FLAG LRU_FLAG CLASS STATE DBARFIL DBABLK BA TCH
---------- ---------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ----------
41231 64265 0000000110997C58 07000001B6B0C908 524288 0 1 3 5 60683 070000015D028000 1
41231 64266 0000000110997B10 07000001B6B0C908 524288 0 1 3 5 60683 0700000157E9C000 1
41231 64267 00000001109979C8 07000001B6B0C908 524288 0 1 3 5 60683 0700000157EC0000 1
41231 64269 0000000110997738 07000001B6B0C908 524288 2 1 3 5 60683 070000015E1D8000 1

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;

当5/60683的before-image个数达到5个了就不再增长了，buffer cache里一个data block的before-image个数与隐含参数"_db_block_max_cr_dba"有关，表示Maximum Allowed Number of CR buffers per dba，一个数据块在buffer cache里最多能有多少个一致性读的buffer，默认值为6，因为current version已经占据了1个，所以留给before-image的只有5个buffer了，用满之后，再对scott.t1118_1表进行一致性读访问的次数统计就要从保存before-image的undo block buffer里获取了，

SYS@tstdb1-SQL> select obj,indx,addr,hladdr,flag,lru_flag,class,state,dbarfil,dbablk,ba,tch from x$bh where dbarfil=10 and DBABLK=40169; select obj,indx,addr,hladdr,flag,lru_flag,class,state,dbarfil,dbablk,ba,tch from x$bh where dbarfil=10 and DBABLK=40169;

您可能感兴趣的文章:

本站(WWW.)旨在分享和传播互联网科技相关的资讯和技术，将尽最大努力为读者提供更好的信息聚合和浏览方式。
本站(WWW.)站内文章除注明原创外，均为转载、整理或搜集自网络。欢迎任何形式的转载，转载请注明出处。