當使用者發出提交(commit)之後，oracle是需要寫出redo來保證故障時資料可以被恢復，oracle並不需要在提交時就寫出變更的資料塊。由於在事務需要修改資料時，必須分配ITL事務槽，必須鎖定行，必須分配回滾段事務槽和回滾表空間來記錄要修改的資料的前映象。當事務提交時，oracle需要將回滾段上的事務表資訊標記為非活動，以便空間可以被重用那麼還有ITL事務資訊和鎖定資訊需要清除,以記錄提交。

由於oracle在資料塊上儲存了ITL和鎖定等事務資訊，所以oracle必須在事務提交之後清除這些事務資料，這就是塊清除。塊清除主要清除的資料有行級鎖、ITL資訊(包括提交標誌、scn等)。

如果提交時修改過的資料塊仍然在buffer cache中，那麼oracle可以清除ITL資訊，這種清除叫做快速塊清除(fast block cleanout)，快速塊清除還有一個限制，當修改的塊數量超過buffer cache的10%，則對超出的部分不再進行快速塊清除。

如果提交事務時，修改過的資料塊已經被寫回到資料檔案上(或大量修改超出10%的部分)，再次讀出該資料塊進行修改，顯然成本過於高昂，對於這種情況，oracle選擇延遲塊清除(delayed block cleanout)，等到下一次訪問該block時再來清除ITL鎖定資訊，這就是延遲塊清除。oracle透過延遲塊清除來提高資料庫的效能，加快提交操作。

快速提交是最普遍的情況。來看一下延遲塊清除的處理：

SYS@ prod>update scott.emp set sal=1999 where empno=7369;

1 row updated.

SYS@ prod>update scott.emp set sal=1998 where empno=7499;

1 row updated.

SYS@ prod>update scott.emp set sal=1997 where empno=7521;

1 row updated.
更新完成之後,強制重新整理buffer cache,將buffer cache中的資料都寫出到資料檔案:
SYS@ prod>alter system flush buffer_cache;

System altered.

查詢事務及回滾段使用者與轉儲
SYS@ prod>select xidusn,xidslot,ubafil,ubablk from v$transaction;

XIDUSN XIDSLOT UBAFIL UBABLK
---------- ---------- ---------- ----------
6 30 3 8028

SYS@ prod>select * from v$rollname where usn=6;

USN NAME
---------- ------------------------------
6 _SYSSMU6_2897970769$

此時再提交事務;
SYS@ prod> commit;

Commit complete.

由於此時更新過的資料已經寫出到資料檔案,oracle將執行延遲塊清除,將此時的資料塊和回滾段轉儲出來:
SYS@ prod>select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from scott.emp;

DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
4 149

SYS@ prod> alter system dump datafile 4 block 149;

System altered.

SYS@ prod> alter system dump undo header '_SYSSMU1_592353410$';

System altered.

SYS@ prod> alter system dump datafile 3 block 8028;

System altered.

SYS@ prod>oradebug setmypid
Statement processed.
SYS@ prod>oradebug tracefile_name
/u01/diag/rdbms/prod/prod/trace/prod_ora_3775.trc

檢視跟蹤檔案資訊,看資料塊上的資訊,ITL事務資訊仍然存在:
其中scn表示提交commit scn,fsc表示快速提交scn
Block dump from disk:
buffer tsn: 4 rdba: 0x01000095 (4/149)
scn: 0x0000.0013bc2d seq: 0x01 flg: 0x04 tail: 0xbc2d0601
frmt: 0x02 chkval: 0x931a type: 0x06=trans data

Block header dump: 0x01000095
Object id on Block? Y
seg/obj: 0x123d7 csc: 0x00.13bbc9 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1000090 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0007.00a.0000030f 0x00c000e3.00d3.2c C--- 0 scn 0x0000.0013baa2
0x02 0x0006.01e.00000398 0x00c01f5c.0173.07 ---- 3 fsc 0x0000.00000000

xid=0x0006.01e.00000398的事務lck=3

資料塊的鎖定資訊仍然存在:
block_row_dump:
tab 0, row 0, @0x1c88
tl: 39 fb: --H-FL-- lb: 0x2 cc: 8
col 0: [ 3] c2 4a 46
col 1: [ 5] 53 4d 49 54 48
col 2: [ 5] 43 4c 45 52 4b
col 3: [ 3] c2 50 03
col 4: [ 7] 77 b4 0c 11 01 01 01
col 5: [ 3] c2 14 64
col 6: *NULL*
col 7: [ 2] c1 15
tab 0, row 1, @0x1caf
tl: 44 fb: --H-FL-- lb: 0x2 cc: 8
col 0: [ 3] c2 4b 64
col 1: [ 5] 41 4c 4c 45 4e
col 2: [ 8] 53 41 4c 45 53 4d 41 4e
col 3: [ 3] c2 4d 63
col 4: [ 7] 77 b5 02 14 01 01 01
col 5: [ 3] c2 14 63
col 6: [ 2] c2 04
col 7: [ 2] c1 1f
tab 0, row 2, @0x1e00
tl: 43 fb: --H-FL-- lb: 0x2 cc: 8
col 0: [ 3] c2 4c 16
col 1: [ 4] 57 41 52 44
col 2: [ 8] 53 41 4c 45 53 4d 41 4e
col 3: [ 3] c2 4d 63
col 4: [ 7] 77 b5 02 16 01 01 01
col 5: [ 3] c2 14 62
col 6: [ 2] c2 06
col 7: [ 2] c1 1f

再來看回滾段的資訊:
********************************************************************************
Undo Segment: _SYSSMU6_2897970769$ (6)
********************************************************************************

Version: 0x01
FREE BLOCK POOL::
uba: 0x00c01f5c.0173.07 ext: 0xd spc: 0x1cfc
uba: 0x00000000.0173.02 ext: 0xd spc: 0x1f06
uba: 0x00c01f59.0173.23 ext: 0xd spc: 0xc58
uba: 0x00000000.0095.01 ext: 0x10 spc: 0x1ed0
uba: 0x00000000.0095.01 ext: 0x10 spc: 0x1ed0
TRN TBL::

index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x1e 9 0x00 0x0398 0xffff 0x0000.0013bc55 0x00c01f5c 0x0000.000.00000000 0x00000001 0x00000000 1559115495

undo塊
********************************************************************************
UNDO BLK:
xid: 0x0006.014.00000399 seq: 0x173 cnt: 0x1e irb: 0x1e icl: 0x0 flg: 0x0000

Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset
---------------------------------------------------------------------------
0x01 0x1f8c 0x02 0x1f18 0x03 0x1ebc 0x04 0x1e68 0x05 0x1dcc
0x06 0x1d74 0x07 0x1d1c 0x08 0x1c94 0x09 0x1c30 0x0a 0x1bdc
0x0b 0x1b70 0x0c 0x1aec 0x0d 0x1a7c 0x0e 0x19fc 0x0f 0x1974
0x10 0x18e8 0x11 0x1880 0x12 0x1798 0x13 0x1730 0x14 0x16d8
0x15 0x1650 0x16 0x15d4 0x17 0x156c 0x18 0x14e4 0x19 0x144c
0x1a 0x13e4 0x1b 0x1308 0x1c 0x1220 0x1d 0x11b8 0x1e 0x1160

事務提交,事務表已經釋放。如果此時查詢scott.emp表,資料庫將產生延遲塊清除:

SYS@ prod>set autotrace on
SYS@ prod>select * from scott.emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 1999 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1998 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1997 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10

12 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 468 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 12 | 468 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9 consistent gets
6 physical reads
116 redo size
1382 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
12 rows processed

SYS@ prod>select * from scott.emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 1999 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1998 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1997 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10

12 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 468 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 12 | 468 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
1382 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
12 rows processed

注意,在此查詢是產生了物理讀取和redo,這個redo就是因為延遲塊清除導致的,再次查詢則不會
產生redo了。

再次轉儲一下該資料塊和回滾段:

SSYS@ prod> alter system dump datafile 4 block 149;

System altered.

SYS@ prod> alter system dump undo header '_SYSSMU1_592353410$';

System altered.

SYS@ prod> alter system dump datafile 3 block 8028;

System altered.

SYS@ prod>oradebug setmypid
Statement processed.
SYS@ prod>oradebug tracefile_name
/u01/diag/rdbms/prod/prod/trace/prod_ora_3775.trc

檢視跟蹤檔案,看到此時ITL事務資訊已經清除,但是注意,這裡的xid和uba資訊仍然存在:
Block header dump: 0x01000095
Object id on Block? Y
seg/obj: 0x123d7 csc: 0x00.13bfab itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1000090 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0007.00a.0000030f 0x00c000e3.00d3.2c C--- 0 scn 0x0000.0013baa2
0x02 0x0006.01e.00000398 0x00c01f5c.0173.07 C--- 0 scn 0x0000.0013bc55

資料塊塊的鎖定位也已經清除了:
block_row_dump:
tab 0, row 0, @0x1c88
tl: 39 fb: --H-FL-- lb: 0x0 cc: 8
col 0: [ 3] c2 4a 46
col 1: [ 5] 53 4d 49 54 48
col 2: [ 5] 43 4c 45 52 4b
col 3: [ 3] c2 50 03
col 4: [ 7] 77 b4 0c 11 01 01 01
col 5: [ 3] c2 14 64
col 6: *NULL*
col 7: [ 2] c1 15
tab 0, row 1, @0x1caf
tl: 44 fb: --H-FL-- lb: 0x0 cc: 8
col 0: [ 3] c2 4b 64
col 1: [ 5] 41 4c 4c 45 4e
col 2: [ 8] 53 41 4c 45 53 4d 41 4e
col 3: [ 3] c2 4d 63
col 4: [ 7] 77 b5 02 14 01 01 01
col 5: [ 3] c2 14 63
col 6: [ 2] c2 04
col 7: [ 2] c1 1f
tab 0, row 2, @0x1e00
tl: 43 fb: --H-FL-- lb: 0x0 cc: 8
col 0: [ 3] c2 4c 16
col 1: [ 4] 57 41 52 44
col 2: [ 8] 53 41 4c 45 53 4d 41 4e
col 3: [ 3] c2 4d 63
col 4: [ 7] 77 b5 02 16 01 01 01
col 5: [ 3] c2 14 62
col 6: [ 2] c2 06
col 7: [ 2] c1 1f

提交之後的undo資訊
當提交事務之後,回滾段事務表標記該事務為非活動,繼續再來看一下回滾段資料塊的資訊,
看到這裡的irb指向了0x3d，此前的事務已經不可回滾

********************************************************************************
UNDO BLK:
xid: 0x0006.004.00000399 seq: 0x173 cnt: 0x3d irb: 0x3d icl: 0x0 flg: 0x0000

看一下偏移量列表也已經新增了一條資訊0x3d 0x01c8

Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset
---------------------------------------------------------------------------
0x01 0x1f8c 0x02 0x1f18 0x03 0x1ebc 0x04 0x1e68 0x05 0x1dcc
0x06 0x1d74 0x07 0x1d1c 0x08 0x1c94 0x09 0x1c30 0x0a 0x1bdc
0x0b 0x1b70 0x0c 0x1aec 0x0d 0x1a7c 0x0e 0x19fc 0x0f 0x1974
0x10 0x18e8 0x11 0x1880 0x12 0x1798 0x13 0x1730 0x14 0x16d8
0x15 0x1650 0x16 0x15d4 0x17 0x156c 0x18 0x14e4 0x19 0x144c
0x1a 0x13e4 0x1b 0x1308 0x1c 0x1220 0x1d 0x11b8 0x1e 0x1160
0x1f 0x10b0 0x20 0x1000 0x21 0x0f94 0x22 0x0f24 0x23 0x0eb8
0x24 0x0e4c 0x25 0x0d9c 0x26 0x0cec 0x27 0x0c80 0x28 0x0c14
0x29 0x0ba8 0x2a 0x0b3c 0x2b 0x0a8c 0x2c 0x0a20 0x2d 0x09b4
0x2e 0x0904 0x2f 0x087c 0x30 0x0820 0x31 0x0770 0x32 0x06c0
0x33 0x0610 0x34 0x05a4 0x35 0x0538 0x36 0x04cc 0x37 0x0460
0x38 0x03f4 0x39 0x0388 0x3a 0x031c 0x3b 0x02b0 0x3c 0x0234
0x3d 0x01c8

再看前映象的資訊,仍然存在。

雖然這個事務已經提交了,不可以回滾了,但是在覆蓋之前,這個前映象資訊仍然存在,透過某些手段,還是可以獲得這個資訊的.比如回閃查詢

Oracle Block Cleanouts 塊清除 - 趙春義 - 部落格園 (cnblogs.com)

Oracle Block Cleanouts 塊清除

相關文章