透過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 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;
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 FLAG LRU_FLAG CLASS STATE DBARFIL DBABLK BA TCH
---------- ---------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ----------
41231 64258 0000000110997C58 07000001B6B0C908 524288 0 1 3 5 60683 0700000157EC0000 1
41231 64260 00000001109979C8 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 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;
OBJ INDX ADDR HLADDR FLAG LRU_FLAG CLASS STATE DBARFIL DBABLK BA TCH
---------- ---------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ----------
41231 64283 0000000110997C58 07000001B6B0C908 524288 0 1 3 5 60683 070000015B5B2000 1
41231 64284 0000000110997B10 07000001B6B0C908 524288 0 1 3 5 60683 070000015A3E0000 1
41231 64285 00000001109979C8 07000001B6B0C908 524288 0 1 3 5 60683 070000015B5B4000 1
41231 64286 0000000110997880 07000001B6B0C908 524288 0 1 3 5 60683 0700000157E92000 1
41231 64287 0000000110997738 07000001B6B0C908 524288 0 1 3 5 60683 070000015D028000 1
當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; <---這裡的10、40169分別來自於v$transaction.ubafil，v$transaction.ubablk
OBJ INDX ADDR HLADDR FLAG LRU_FLAG CLASS STATE DBARFIL DBABLK BA TCH
---------- ---------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ----------
4294967295 8038 0000000110B557C0 07000001B6CA2150 35651584 0 102 1 10 40169 0700000183CF2000 14
select count(*) 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 dbarfil=10 and DBABLK=40169;
OBJ INDX ADDR HLADDR FLAG LRU_FLAG CLASS STATE DBARFIL DBABLK BA TCH
---------- ---------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ----------
4294967295 8048 0000000110B557C0 07000001B6CA2150 35651584 0 102 1 10 40169 0700000183CF2000 15 <---undo block的TCH增加了1
###建立測試表
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;
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 FLAG LRU_FLAG CLASS STATE DBARFIL DBABLK BA TCH
---------- ---------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ----------
41231 64258 0000000110997C58 07000001B6B0C908 524288 0 1 3 5 60683 0700000157EC0000 1
41231 64260 00000001109979C8 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 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;
OBJ INDX ADDR HLADDR FLAG LRU_FLAG CLASS STATE DBARFIL DBABLK BA TCH
---------- ---------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ----------
41231 64283 0000000110997C58 07000001B6B0C908 524288 0 1 3 5 60683 070000015B5B2000 1
41231 64284 0000000110997B10 07000001B6B0C908 524288 0 1 3 5 60683 070000015A3E0000 1
41231 64285 00000001109979C8 07000001B6B0C908 524288 0 1 3 5 60683 070000015B5B4000 1
41231 64286 0000000110997880 07000001B6B0C908 524288 0 1 3 5 60683 0700000157E92000 1
41231 64287 0000000110997738 07000001B6B0C908 524288 0 1 3 5 60683 070000015D028000 1
當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; <---這裡的10、40169分別來自於v$transaction.ubafil，v$transaction.ubablk
OBJ INDX ADDR HLADDR FLAG LRU_FLAG CLASS STATE DBARFIL DBABLK BA TCH
---------- ---------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ----------
4294967295 8038 0000000110B557C0 07000001B6CA2150 35651584 0 102 1 10 40169 0700000183CF2000 14
select count(*) 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 dbarfil=10 and DBABLK=40169;
OBJ INDX ADDR HLADDR FLAG LRU_FLAG CLASS STATE DBARFIL DBABLK BA TCH
---------- ---------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ----------
4294967295 8048 0000000110B557C0 07000001B6CA2150 35651584 0 102 1 10 40169 0700000183CF2000 15 <---undo block的TCH增加了1
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/53956/viewspace-1844599/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 從底向上瞭解DML操作
- 關於tablespace在read only狀態下的DML ,DDL操作--Read-Only Tablespaces
- data buffer cache的一點總結。
- data buffer cache的一點總結 -- 轉
- select會訪問buffer cache裡哪種型別的data block，XCUR還是CR？型別BloC
- 如何透過程式碼接入手機在網狀態 APIAPI
- Oracle buffer狀態深入剖析Oracle
- How To Identify a Hot Block Within The Database Buffer Cache. [ID 163424.1]IDEBloCDatabase
- 【原創】cache buffer chains的一次解決過程AI
- Oracle10g data buffer cache的記憶體結構Oracle記憶體
- [譯] SpaceAce 瞭解一下，一個新的前端狀態管理庫前端
- page cache與buffer cache的關係
- Buffer Cache 原理
- cache buffer chainAI
- 透過crontab更改DataGuard物理庫managed recover狀態
- 透過bat檔案批次判斷url地址的狀態BAT
- 透過單選按鈕控制編輯框的狀態
- vue狀態管理演進Vue
- MySQL-DML（Data Manipulation Language）詳解MySql
- Spark SQL 教程： 透過示例瞭解 Spark SQLSparkSQL
- Export data from a data blockExportBloC
- DML對QUERY CACHE 處理過程之原始碼分析原始碼
- IO之核心buffer----"buffer cache"
- 在DDMS中檢視/data/data/*下檔案的解決方法
- 在Buffer Cache中自動大表快取快取
- buffer與cache的區別
- Buffer cache的執行原理
- Buffer和Cache的區別
- Buffer 與 Cache 的區別
- Cache 和 Buffer的區別
- Oracle Data BufferOracle
- Buffer Cache Hit Ratio
- Oracle Buffer Cache原理Oracle
- Oracle database buffer cacheOracleDatabase
- CACHE BUFFER CHAINSAI
- Database Buffer Cache (79)Database
- 不可見索引在表DML操作過程中依然被維護索引
- 動態路由,透過id改變,改頁面路由