转载

select会访问buffer cache里哪种类型的data block，XCUR还是CR？

当我们以select一张表的时候，如果data block没有在buffer cache里，这个block就会被server process从磁盘读取到buffer cache；如果能在buffer cache里找到那么直接访问即可；v$bh.status表示了buffer cache里block的类型，常见的有以下几种
cr
free
pi <---RAC环境下才有
read
scur <---RAC环境下才有
write
xcur

在我的印象中Select操作总是与consistent这个关键字联系在一起，而DML操作总与current有关，因此select总是会访问buffer cache里cr类型的block。
其实不然：
当data block上尚有未完成的transaction时，如果新开一个session去select这个data block就会使用cr block，如果在原本的transaction里去select就会使用xcur block
当data block上没有未完成的transaction时，select这个data block就会使用xcur block

<--------x$bh里的关键字段含义-------->
x$bh.tch：block被访问的次数
x$bh.flag：block是否为脏块，同时还表示了其它一些信息
x$bh.state：block的类型

###创建测试表connect scott/773946
create table t0427_1 as select * from all_users;

select distinct dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid) from t0427_1;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)
------------------------------------ ------------------------------------
22065 4

###数据库所使用的undo tablespace及对应的undo datafile
SQL> show parameter undo_tablespace

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string undotbs3

col name format a50
set linesize 130
select name,file# from v$datafile_header where tablespace_name='UNDOTBS3'

NAME FILE#
-------------------------------------------------- ----------
/oradata06/testaaaaa/undotbs3.dbf 10

###这里我们开启三个session，session 1用于执行DML和select、session 2执行select、session 3用来监控
---session 3: 起初buffer cache里没有block 4/22065
SQL> select file#, dbablk, decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi', 9,'memory',10,'mwrite',11,'donated', 12,'protected', 13,'securefile', 14,'siop',15,'recckpt', 16, 'flashfree', 17, 'flashcur', 18, 'flashna') status,tch,decode(bitand(flag,1), 0, 'N', 'Y') dirty from x$bh where file#=4 and dbablk=22065;

no rows selected

---session 1：执行update，记录Transaction使用到的undo信息
update t0427_1 set username='XXX' where username='XDB';

select UBAFIL,UBABLK from v$transaction;
UBAFIL UBABLK
---------- ----------
10 59589

---session 3：update后观察buffer cache里的data block和undo block
select file#, dbablk, decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi', 9,'memory',10,'mwrite',11,'donated', 12,'protected', 13,'securefile', 14,'siop',15,'recckpt', 16, 'flashfree', 17, 'flashcur', 18, 'flashna') status,tch,decode(bitand(flag,1), 0, 'N', 'Y') dirty from x$bh where file#=4 and dbablk=22065;
FILE# DBABLK STATUS TCH D
---------- ---------- ---------- ---------- -
4 22065 xcur 2 Y
4 22065 cr 1 N

在update真正执行之前先把要修改的block复制出一份cr的副本
select file#, dbablk, decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi', 9,'memory',10,'mwrite',11,'donated', 12,'protected', 13,'securefile', 14,'siop',15,'recckpt', 16, 'flashfree', 17, 'flashcur', 18, 'flashna') status,tch,decode(bitand(flag,1), 0, 'N', 'Y') dirty from x$bh where file#=10 and dbablk=59589;
FILE# DBABLK STATUS TCH D
---------- ---------- ---------- ---------- -
10 59589 xcur 2 Y

---session 2：执行第一轮select
select * from t0427_1 where username='XDB';
USERNAME USER_ID CREATED
------------------------------ ---------- -----------------
XDB 59 20150629 16:00:05

---session 3: 第一轮后观察data block和undo block
select file#, dbablk, decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi', 9,'memory',10,'mwrite',11,'donated', 12,'protected', 13,'securefile', 14,'siop',15,'recckpt', 16, 'flashfree', 17, 'flashcur', 18, 'flashna') status,tch,decode(bitand(flag,1), 0, 'N', 'Y') dirty from x$bh where file#=4 and dbablk=22065;
FILE# DBABLK STATUS TCH D
---------- ---------- ---------- ---------- -
4 22065 cr 1 N
4 22065 xcur 2 Y
4 22065 cr 1 N

select file#, dbablk, decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi', 9,'memory',10,'mwrite',11,'donated', 12,'protected', 13,'securefile', 14,'siop',15,'recckpt', 16, 'flashfree', 17, 'flashcur', 18, 'flashna') status,tch,decode(bitand(flag,1), 0, 'N', 'Y') dirty from x$bh where file#=10 and dbablk=59589;
FILE# DBABLK STATUS TCH D
---------- ---------- ---------- ---------- -
10 59589 xcur 3 Y

可以看到对于data block：4/22065 新增了一个cr block，对于undo block：10/59589 tch从2增加为3，表示其被访问过1次了；这里大概的作一下解释：session 2访问4/22065时发现buffer cache里已经有这个block的两个copy，一个xcur，一个cr，在我们看来最简单的方法莫过于把cr block直接拿过来使用，但是oracle并不这么认为，隐含参数_db_block_max_cr_dba默认值为6，决定了同一个data block最多能有6个类型为cr的副本，所以oracle会新生成一个block 4/22065的cr copy，我们还注意到undo block 10/59589也被访问了一次，因此生成cr copy的过程：oracle将data block 4/22065的xcur copy再复制一份出来，然后通过这份新复制出来的xcur副本头部的Transaction信息(Uba、SCN等)找到包含before-image的undo block 10/59589，进行rollback，最终形成一份崭新的cr copy

---session 2：执行第二轮select ：
select * from t0427_1 where username='XDB';
USERNAME USER_ID CREATED
------------------------------ ---------- -----------------
XDB 59 20150629 16:00:05

---session 3: 第二轮后观察data block和undo block
select file#, dbablk, decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi', 9,'memory',10,'mwrite',11,'donated', 12,'protected', 13,'securefile', 14,'siop',15,'recckpt', 16, 'flashfree', 17, 'flashcur', 18, 'flashna') status,tch,decode(bitand(flag,1), 0, 'N', 'Y') dirty from x$bh where file#=4 and dbablk=22065;
FILE# DBABLK STATUS TCH D
---------- ---------- ---------- ---------- -
4 22065 cr 1 N
4 22065 cr 1 N
4 22065 xcur 2 Y
4 22065 cr 1 N

select file#, dbablk, decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi', 9,'memory',10,'mwrite',11,'donated', 12,'protected', 13,'securefile', 14,'siop',15,'recckpt', 16, 'flashfree', 17, 'flashcur', 18, 'flashna') status,tch,decode(bitand(flag,1), 0, 'N', 'Y') dirty from x$bh where file#=10 and dbablk=59589;
FILE# DBABLK STATUS TCH D
---------- ---------- ---------- ---------- -
10 59589 xcur 4 Y

第二轮执行完后，data block：4/22065 的cr copy数量从2增加为3，undo block 10/59589 tch从3增加为4

---session 2：执行第三轮select：
select * from t0427_1 where username='XDB';
USERNAME USER_ID CREATED
------------------------------ ---------- -----------------
XDB 59 20150629 16:00:05

---session 3 ：第三轮后观察data block和undo block
select file#, dbablk, decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi', 9,'memory',10,'mwrite',11,'donated', 12,'protected', 13,'securefile', 14,'siop',15,'recckpt', 16, 'flashfree', 17, 'flashcur', 18, 'flashna') status,tch,decode(bitand(flag,1), 0, 'N', 'Y') dirty from x$bh where file#=4 and dbablk=22065;
FILE# DBABLK STATUS TCH D
---------- ---------- ---------- ---------- -
4 22065 cr 1 N
4 22065 cr 1 N
4 22065 cr 1 N
4 22065 xcur 2 Y
4 22065 cr 1 N

select file#, dbablk, decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi', 9,'memory',10,'mwrite',11,'donated', 12,'protected', 13,'securefile', 14,'siop',15,'recckpt', 16, 'flashfree', 17, 'flashcur', 18, 'flashna') status,tch,decode(bitand(flag,1), 0, 'N', 'Y') dirty from x$bh where file#=10 and dbablk=59589;
FILE# DBABLK STATUS TCH D
---------- ---------- ---------- ---------- -
10 59589 xcur 5 Y

第三轮执行完后，data block：4/22065 的cr copy数量从3增加为4，undo block 10/59589 tch从4增加为5
可见，在没有达到_db_block_max_cr_dba指定值的情况下cr copy数会持续增加

---session 1：执行select操作查询自己尚未commit的修改结果
select * from t0427_1 where username='XXX';
USERNAME USER_ID CREATED
------------------------------ ---------- -----------------
XXX 59 20150629 16:00:05

---session 3 ：观察data block
select file#, dbablk, decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi', 9,'memory',10,'mwrite',11,'donated', 12,'protected', 13,'securefile', 14,'siop',15,'recckpt', 16, 'flashfree', 17, 'flashcur', 18, 'flashna') status,tch,decode(bitand(flag,1), 0, 'N', 'Y') dirty from x$bh where file#=4 and dbablk=22065;
FILE# DBABLK STATUS TCH D
---------- ---------- ---------- ---------- -
4 22065 cr 1 N
4 22065 cr 1 N
4 22065 cr 1 N
4 22065 xcur 3 Y <---增加了1
4 22065 cr 1 N

至此，我们发现session 1执行update时会访问xcur类型的block，在select自己没有commit的修改结果是访问的也是xcur类型的block；而当session 1没有commit的情况下，session 2自始至终select的都是CR类型的block。

现在我们让session 1 commit
---session 1：commit之前的修改
commit;

---session 2：继续select表t0427_1
select * from t0427_1 where username='XDB';

no rows selected

---session 3：由于session 1 commit了，只观察data block
select file#, dbablk, decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi', 9,'memory',10,'mwrite',11,'donated', 12,'protected', 13,'securefile', 14,'siop',15,'recckpt', 16, 'flashfree', 17, 'flashcur', 18, 'flashna') status,tch,decode(bitand(flag,1), 0, 'N', 'Y') dirty from x$bh where file#=4 and dbablk=22065;
FILE# DBABLK STATUS TCH D
---------- ---------- ---------- ---------- -
4 22065 cr 1 N
4 22065 cr 1 N
4 22065 cr 1 N
4 22065 xcur 4 Y
4 22065 cr 1 N

因为session 1 commit了，所以4/22065这个block内容的一致性得到了保证，session 2最后的这次select访问的就是xcur类型的block；

过不久发生了incremental checkpoint

SQL> select file#, dbablk, decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi', 9,'memory',10,'mwrite',11,'donated', 12,'protected', 13,'securefile', 14,'siop',15,'recckpt', 16, 'flashfree', 17, 'flashcur', 18, 'flashna') status,tch,decode(bitand(flag,1), 0, 'N', 'Y') dirty from x$bh where file#=4 and dbablk=22065

FILE# DBABLK STATUS TCH D
---------- ---------- ---------- ---------- -
4 22065 cr 1 N
4 22065 cr 1 N
4 22065 cr 1 N
4 22065 xcur 4 N <---Dirty从Y变为N
4 22065 cr 1 N

---session 2：再一次select表t0427_1
select * from t0427_1 where username='XDB';

no rows selected

---session 3：这次访问的还是xcur类型对应的那个block
SQL> select file#, dbablk, decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi', 9,'memory',10,'mwrite',11,'donated', 12,'protected', 13,'securefile', 14,'siop',15,'recckpt', 16, 'flashfree', 17, 'flashcur', 18, 'flashna') status,tch,decode(bitand(flag,1), 0, 'N', 'Y') dirty from x$bh where file#=4 and dbablk=22065;

FILE# DBABLK STATUS TCH D
---------- ---------- ---------- ---------- -
4 22065 cr 1 N
4 22065 cr 1 N
4 22065 cr 1 N
4 22065 xcur 5 N
4 22065 cr 1 N

---session 3： flush buffer_cache
alter system flush buffer_cache;

SQL> select file#, dbablk, decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi', 9,'memory',10,'mwrite',11,'donated', 12,'protected', 13,'securefile', 14,'siop',15,'recckpt', 16, 'flashfree', 17, 'flashcur', 18, 'flashna') status,tch,decode(bitand(flag,1), 0, 'N', 'Y') dirty from x$bh where file#=4 and dbablk=22065;

FILE# DBABLK STATUS TCH D
---------- ---------- ---------- ---------- -
4 22065 free 0 N
4 22065 free 0 N
4 22065 free 0 N
4 22065 free 0 N
4 22065 free 0 N
4 22065 free 0 N
4 22065 free 0 N

---session 2：select t0427_1表
select * from t0427_1 where username='XXX';

USERNAME USER_ID CREATED
------------------------------ ---------- -----------------
XXX 59 20150629 16:00:05

SQL> select file#, dbablk, decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi', 9,'memory',10,'mwrite',11,'donated', 12,'protected', 13,'securefile', 14,'siop',15,'recckpt', 16, 'flashfree', 17, 'flashcur', 18, 'flashna') status,tch,decode(bitand(flag,1), 0, 'N', 'Y') dirty from x$bh where file#=4 and dbablk=22065;

FILE# DBABLK STATUS TCH D
---------- ---------- ---------- ---------- -
4 22065 xcur 1 N <---仍然是xcur
4 22065 free 0 N
4 22065 free 0 N
4 22065 free 0 N
4 22065 free 0 N
4 22065 free 0 N
4 22065 free 0 N
4 22065 free 0 N

正文到此结束