當我們以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

select會訪問buffer cache裡哪種型別的data block，XCUR還是CR？

相關文章