1.何为延迟块清除:
简单的说,在oracle的block上都有活动事务的标志的,如果一个事务commit后,由于某些block在commit之前已经写回datafile, 或者事务影响到的block数过多,则commi的时候只会清理undo segment header中的事务表信息,data block上的事务标志不会清除,否则代价过高。那么在一些读取这些block时,需要将这些事务标志进行清除,就是延迟块清除。
2.测试思路:
根据上述的说法,可以进行模拟,对一个块上的数据进行update之后,手动发出checkpoint,将其从缓存中写入数据文件,这样,当commit时,将不在数据块上对该事务做标记。此时,对该表进行select操作,此操作需要对检查数据块里的itl,修改没有标记为提交的事务信息为已提交。
3.测试过程如下:
SQL> create table t(a number) INITRANS 1 maxtrans 1;
Table created.
SQL> insert into t values(1);
1 row created.
SQL> select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from t;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
1 31826
SQL> alter system dump datafile 1 block 31826;
System altered.
-------------update之前的数据块dump---------------------------------
Object id on Block? Y
seg/obj: 0x371a csc: 0x00.55fa5a itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0001.019.00000338 0x00800443.0318.17 ---- 1 fsc 0x0000.00000000
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
-----------------------------------------------------------------------
SQL> update t set a=2;
1 row updated.
SQL> alter system dump datafile 1 block 31826;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system flush buffer_cache;
System altered.
SQL> select file#,block#,status,dirty from v$bh where file#=1 and block#=31826;
FILE# BLOCK# STATUS D
---------- ---------- ------- -
1 31826 free N
1 31826 free N
可以确认被修改过的数据块已经被写到数据文件里
---------------------------------update之后的数据块信息-------------------------------------
Block header dump: 0x00407c52
Object id on Block? Y
seg/obj: 0x371a csc: 0x00.55fa83 itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0001.019.00000338 0x00800443.0318.17 C--- 0 scn 0x0000.0055fa7d
0x02 0x0004.015.0000030f 0x00807020.036a.3b ---- 1 fsc 0x0000.00000000 ---------此事务代表执行的update操作
----------------------------------提交之后,再次查看数据块信息---------------------------------
SQL> commit;
Commit complete.
SQL> alter system dump datafile 1 block 31826;
System altered.
----------------------------------提交之后的数据块信息------------------------------------------
Block header dump: 0x00407c52
Object id on Block? Y
seg/obj: 0x371a csc: 0x00.55fa83 itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0001.019.00000338 0x00800443.0318.17 C--- 0 scn 0x0000.0055fa7d
0x02 0x0004.015.0000030f 0x00807020.036a.3b ---- 1 fsc 0x0000.00000000 ----------可见,事务仍然被标记为未提交
--------------------------------------
SQL> select * from t;
A
----------
2
SQL> alter system dump datafile 1 block 31826;
System altered.
-----------------------------------执行select操作,再次查看数据块信息--------------------------------
Block header dump: 0x00407c52
Object id on Block? Y
seg/obj: 0x371a csc: 0x00.55fb10 itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0001.019.00000338 0x00800443.0318.17 C--- 0 scn 0x0000.0055fa7d
0x02 0x0004.015.0000030f 0x00807020.036a.3b C--- 0 scn 0x0000.0055faae ---------------------------已经被标记为已提交
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10972173/viewspace-615796/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/10972173/viewspace-615796/