happydba

January 2001

I have written this note purely for entertainment value. It highlights the differences that appear when a block is subject ot delayed block cleanout rather than fast commit cleanout. The experiments in this note were run against 8.1.5 only.

When an Oracle process changes a data block it keeps track of the block and, at commit time, will revisit the block and mark the change as committed – but only if the block has not been flushed from the db_block_buffer in the interim. There is also a restriction that if the process has changed too many blocks then only the first few will be revisited (I believe the limit is currently 10% of the db_block_buffers).

If a block has not been fixed up by the fast commit then the next time it is read, it will be cleaned out by the reader process. There are two variations on this delayed block cleanout: the reader may be able to determine the exact SCN that was in effect at commit time because the state of the rollback segments is still sufficiently fresh, in this case the block will be marked with the correct SCN; alternatively, if too much time has passed and the exact SCN can no longer be retrieved, the block will be marked with a ‘best guess’ SCN or upper bound commit number.

To find out if there were any differences between blocks that fall into these three states, I did the following:

Create a table with one row per block – which can be achieved by using the rpad() function to make one column as long as needed, and setting pctfree very high to make it impossible for Oracle to put more than one row in each block. For the purposes of the test I used a small db_block_buffer and made the table a little larger than the buffer. For convenience, each row has a built-in row number so that it is easy to identify

I then did three tests:

a) Update the first row in the table, commit, and dump the block.

b) Update the rest of the rows, commit, and dump the second block

c) Thrash the rollback segment to recycle and lose the history of my update, then dump another block.

In the dumps below, the significant changes are highlighted.

SQL to generate the table

create table junk1

nologging

pctfree 99

pctused 1

as

        select rownum n1, rpad(rownum,200) v1

        from all_objects

        where rownum <= 1000

;

Fast cleanout – the first block after a single row update and commit.

The block SCN matches the transaction SCN (and the transaction SCN entry is still claiming to be a Free Space Count entry anyway), and the block flag is 2. The transaction flag is –U-, but we are still apparently indicating that this transaction is locking one row. The row has its lock byte set to 1 to identify the relevant item in the interested transaction list (ITL).

Start dump data blocks tsn: 1 file#: 2 minblk 27225 maxblk 27225

buffer tsn: 1 rdba: 0x00806a59 (2/27225)

scn: 0x0000.0067694d seq: 0x01 flg: 0x02 tail: 0x694d0601

frmt: 0x02 chkval: 0x0000 type: 0x06=trans data

Block header dump: 0x00806a59

Object id on Block? Y

seg/obj: 0x8693 csc: 0x00.676937 itc: 1 flg: - typ: 1 - DATA

fsl: 0 fnx: 0x0 ver: 0x01

Itl       Xid                 Uba                     Flag  Lck   Scn/Fsc

0x01 xid: 0x0002.008.00001902 uba: 0x00c071b9.041b.0e --U-    1   fsc 0x0000.0067694d

data_block_dump

===============

tsiz: 0x7b8

hsiz: 0x14

pbl: 0x0670ba44

bdba: 0x00806a59

flag=-----------

ntab=1

nrow=1

frre=-1

fsbo=0x14

fseo=0x618

avsp=0x6d3

tosp=0x6d3

0xe:pti[0]     nrow=1  offs=0

0x12:pri[0]    offs=0x618

block_row_dump:

tab 0, row 0, @0x618

tl: 209 fb: --H-FL-- lb: 0x1 cc: 2

col 0: [ 4] c3 02 01 02

col 1: [200]

31 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20

   ...

20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20

end_of_block_dump

End dump data blocks tsn: 1 file#: 2 minblk 27225 maxblk 27225

Delayed block cleanout when the rollback segment still holds recent history:

The block SCN is higher than the transaction SCN (the difference is only one, as no transactions occurred between the original transaction and the read that cleaned the block), and the block flag is 0. The transaction flag is C—-, and we are no longer showing any locked rows. The transaction SCN is showing the correct value. The block is perfectly clean.

Start dump data blocks tsn: 1 file#: 2 minblk 27226 maxblk 27226

buffer tsn: 1 rdba: 0x00806a5a (2/27226)

scn: 0x0000.0067695e seq: 0x01 flg: 0x00 tail: 0x695e0601

frmt: 0x02 chkval: 0x0000 type: 0x06=trans data

Block header dump: 0x00806a5a

Object id on Block? Y

seg/obj: 0x8693 csc: 0x00.67695e itc: 1 flg: - typ: 1 - DATA

fsl: 0 fnx: 0x0 ver: 0x01

Itl       Xid                 Uba                     Flag  Lck   Scn/Fsc

0x01 xid: 0x0002.005.000018f3 uba: 0x00c071b9.041b.0f C---    0   scn 0x0000.0067695d

data_block_dump

===============

tsiz: 0x7b8

hsiz: 0x14

pbl: 0x0670ba44

bdba: 0x00806a5a

flag=-----------

ntab=1

nrow=1

frre=-1

fsbo=0x14

fseo=0x618

avsp=0x6d3

tosp=0x6d3

0xe:pti[0]     nrow=1  offs=0

0x12:pri[0]    offs=0x618

block_row_dump:

tab 0, row 0, @0x618

tl: 209 fb: --H-FL-- lb: 0x0 cc: 2

col 0: [ 4] c3 02 01 03

col 1: [200]

32 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20

   ...

20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20

end_of_block_dump

End dump data blocks tsn: 1 file#: 2 minblk 27226 maxblk 27226

Delayed block cleanout when the rollback segment has lost all relevant history:

The block SCN is much higher than the transaction SCN (and the transaction SCN ought to be the same as the one in the example above as this block was changed in the same large-scale update), and the block flag is 0. The transaction flag is C-U-, and again we are no longer showing any locked rows. The transaction SCN is much higher than the SCN at which the transaction actually happened, as we destroyed the full history by thrashing the rollback segment, and the SCN recorded here is the lowest SCN that could be regenerated by the rollback segment (The U is for “upper bound commit”).

Start dump data blocks tsn: 1 file#: 2 minblk 27234 maxblk 27234

buffer tsn: 1 rdba: 0x00806a62 (2/27234)

scn: 0x0000.006770eb seq: 0x01 flg: 0x00 tail: 0x70eb0601

frmt: 0x02 chkval: 0x0000 type: 0x06=trans data

Block header dump: 0x00806a62

Object id on Block? Y

seg/obj: 0x8693 csc: 0x00.6770eb itc: 1 flg: - typ: 1 - DATA

fsl: 0 fnx: 0x0 ver: 0x01

Itl       Xid                 Uba                     Flag  Lck   Scn/Fsc

0x01 xid: 0x0002.005.000018f3 uba: 0x00c071ba.041b.03 C-U-    0   scn 0x0000.006770c0

data_block_dump

===============

tsiz: 0x7b8

hsiz: 0x14

pbl: 0x069bba44

bdba: 0x00806a62

flag=-----------

ntab=1

nrow=1

frre=-1

fsbo=0x14

fseo=0x618

avsp=0x6d3

tosp=0x6d3

0xe:pti[0]     nrow=1  offs=0

0x12:pri[0]    offs=0x618

block_row_dump:

tab 0, row 0, @0x618

tl: 209 fb: --H-FL-- lb: 0x0 cc: 2

col 0: [ 4] c3 02 01 0b

col 1: [200]

31 30 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20

   ...

20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20

end_of_block_dump

End dump data blocks tsn: 1 file#: 2 minblk 27234 maxblk 27234

Block 的cache header部分，记录着一个block scn,它是当前块最后一次变更的时间戳（确切说，这个更新并不是指itl上的scn的最新更新，在接下来delayed block cleanout下的slot重用情况下，可以看到block scn并不等于itl上的最后一次更新的scn）。可以通过dump获得block scn/last itl scn 和发布ora_rowscn语句获得last itl scn。

1. SQL> select distinct ora_rowscn,dbms_rowid.rowid_block_number(rowid) from test_ind where dbms_rowid.rowid_block_number(rowid)=31501;
ORA_ROWSCN DBMS_ROWID.ROWID_BLOCK_NUMBER(
———- ——————————
617405 31501

2. SQL> alter system dump datafile 4 block 31501;
Start dump data blocks tsn: 4 file#: 4 minblk 31501 maxblk 31501
buffer tsn: 4 rdba: 0×01007b0d (4/31501)
scn: 0×0000.00096bbd seq: 0×01 flg: 0×02 tail: 0×6bbd0601
frmt: 0×02 chkval: 0×0000 type: 0×06=trans data
――――――――――――――――――――――――――――――――――――――
Itl Xid Uba Flag Lck Scn/Fsc
0×01 0×000a.001.00000089 0×008033be.0046.14 C— 0 scn 0×0000.00065b9e
0×02 0×0008.02b.000000ac 0×00802fea.004a.28 –U- 1 fsc 0×0000.00096bbd

发布transaction后，未提交之前，block scn是不会改变的，对应的itl中也并不做scn记录。Block scn的改变，确切的说不是在发布commit之时（因为有delayed block cleanout的情况存在），而是在transaction对应的itl获得commit scn之时。
当发生fast commit cleanout，系统将transaction提交时刻的scn作为commit scn，更新block上 itl和undo segment header的Transaction table的slot上的 scn，并修改block scn，三者是一致的。发生delayed block cleanout的时候，之前的transaction commit更新的只是Transaction table，而并未做block上的处理，等待下次使用此blobk的时候，更新block scn和itl状态。block scn和itl的更新又分2种情况：
（1）当不产生slot重用的时候， delayed block cleanout时，根据Transaction table里面的信息，更新block scn和itl上的Scn/Fsc为transaction曾经提交时候的scn。
（2） 当产生slot重用的时候，更新对应itl上scn为control scn，而block scn 为delayed block cleanout发生时刻的scn。

简单看一下这个测试过程。
（一）fast commit cleanout
1.建表/插入数据

SQL> create table test_scn(see char(500)) pctfree 90 tablespace test_a ;
Table created
SQL> insert into test_scn values(’see1′);
1 row inserted
SQL> insert into test_scn values(’see2′);
1 row inserted
SQL> commit;
Commit complete
SQL> select dbms_rowid.rowid_block_number(rowid),ora_rowscn from test_scn;
DBMS_ROWID.ROWID_BLOCK_NUMBER( ORA_ROWSCN
—————————— ———-
25617 622604
25618 622604

2.发布事务更新

SQL> update test_scn set see=’kao’ where see=’see1′;
SQL> select dbms_rowid.rowid_block_number(rowid),ora_rowscn from test_scn;

DBMS_ROWID.ROWID_BLOCK_NUMBER( ORA_ROWSCN
—————————— ———-
25617 622604
25618 622604 ――发现block scn并未改变
SQL>commit;

SQL> select dbms_rowid.rowid_block_number(rowid),ora_rowscn from test_scn;

DBMS_ROWID.ROWID_BLOCK_NUMBER( ORA_ROWSCN
—————————— ———-
25617 622604
25618 622683 ――已经更新

（二） delayed block cleanout：

1.创建一个小undo表空间.

SQL> create undo tablespace undo datafile ‘E:\ORACLE\PRODUCT\10.2.0\ORADATA\DEMO\undo.dbf’ size 2M;
Tablespace created
SQL> alter system set undo_tablespace=’undo’;
System altered

2 .发布更新后，清空buffer_cache，创造延时块清除条件，然后提交

SQL> select dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid), ora_rowscn,t.* from test_scn t;
DBMS_ROWID.ROWID_BLOCK_NUMBER( DBMS_ROWID.ROWID_RELATIVE_FNO( ORA_ROWSCN SEE
—————————— —————————— ———- ——————–
25617 6 625000 kao1
25618 6 693298 see2
SQL> update test_scn set see=’kao2′ where see=’see2′;
1 row updated
SQL> select XIDUSN，XIDSLOT，XIDSQN from v$transaction;
XIDUSN XIDSLOT XIDSQN
———- ————- —————- —————- —————-
20 33 21 ――得到XIDUSN，XIDSLOT的使用情况，后续发布重用脚本
SQL> alter system flush buffer_cache;
System altered
SQL> commit;
Commit complete
SQL> select timestamp_to_scn(systimestamp) from dual;
TIMESTAMP_TO_SCN(SYSTIMESTAMP)
——————————
693323 —–大概的commit scn

3．发布脚本，使XIDUSN 20 XIDLOT 33 重用。

SQL>exec proc_go_break_reuse(20，33，21);

代码如下：

create or replace procedure proc_go_break_reuse
(v_XIDUSN number,
v_XIDSLOT number,
v_XIDSQN number)
/* ————————————————–
Create_user :Mecoyoo
Time:2008-5-08
Description:It’s used to make transaction slot reused
—————————————————*/
AS
nsid number;
type transaction_record_type is record(
XIDUSN number,
XIDSLOT number,
XIDSQN number);
transaction_record transaction_record_type;
begin
select sys_context(’userenv’, ’sid’) into nsid from dual;
loop
insert into goon
select * from dba_objects where rownum<100;
select XIDUSN, XIDSLOT, XIDSQN
into transaction_record
from v$transaction a, v$session b
where a.ADDR = b.TADDR
and b.SID = nsid;
if (transaction_record.XIDUSN = v_XIDUSN and
transaction_record.XIDSLOT = v_XIDSLOT and
transaction_record.XIDSQN > v_XIDSQN) then
goto resue_end;
end if;
commit;
delete from goon;
select XIDUSN, XIDSLOT, XIDSQN
into transaction_record
from v$transaction a, v$session b
where a.ADDR = b.TADDR
and b.SID = nsid;
if (transaction_record.XIDUSN = v_XIDUSN and
transaction_record.XIDSLOT = v_XIDSLOT and
transaction_record.XIDSQN > v_XIDSQN) then
goto resue_end;
end if;
commit;
end loop;
<< resue_end >>
commit;
end;

4.产生延时块清除，记录相应scn号

SQL> select * from test_scn;
SEE
——————————————————————————–
kao1
kao2
SQL> select timestamp_to_scn(systimestamp) from dual;

TIMESTAMP_TO_SCN(SYSTIMESTAMP)
——————————
703860 ―――发生延时块清除时候的大概scn

SQL> select dbms_rowid.rowid_block_number(rowid), ora_rowscn from test_scn t where see=’kao2′;

DBMS_ROWID.ROWID_BLOCK_NUMBER( ORA_ROWSCN
—————————— ———-
25618 701061 —最后一次itl 上的 commit scn

5.dump undo header和block 25618

SQL> alter system dump undo header ‘_SYSSMU15$’;

System altered
TRN CTL:: seq: 0×0340 chd: 0×002b ctl: 0×0021 inc: 0×00000000 nfb: 0×0001
mgc: 0×8201 xts: 0×0068 flg: 0×0001 opt: 2147483646 (0×7ffffffe)
uba: 0×0200009b.0336.0a scn: 0×0000.000ab285 －这是control scn
―――――――――――――――――――――――――――――――――――――
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
————————————————————————————————
0×00 9 0×00 0×0018 0×0001 0×0000.000ab2f0 0×0200009b 0×0000.000.00000000 0×00000001 0×00000000 1210262128
―――――――――――――――――――――――――――――――――――――――――――――
0×21 9 0×00 0×0018 0xffff 0×0000.000abd20 0×02000084 0×0000.000.00000000 0×00000052 0×00000000 1210262179 －－该slot已经被重用，重用的提交scn为000abd20
―――――――――――――――――――――――――――――――――――――――――――――
0×2f 9 0×00 0×0017 0×002e 0×0000.000ab387 0×0200009b 0×0000.000.00000000
0×000000

SQL> alter system dump datafile 6 block 25618;

Start dump data blocks tsn: 6 file#: 6 minblk 25618 maxblk 25618
buffer tsn: 6 rdba: 0×01806412 (6/25618)
scn: 0×0000.000abd72 seq: 0×01 flg: 0×00 tail: 0xbd720601 ―――这个scn 就是block scn，scn号为703858，可见应该是延时块清除的时候的scn
frmt: 0×02 chkval: 0×0000 type: 0×06=trans data

Hex dump of block: st=0, typ_found=1
Dump of memory from 0×074C8400 to 0×074CA400
……………………………………………………………………..
Block header dump: 0×01806412
Object id on Block? Y
seg/obj: 0×2966 csc: 0×00.abd72 itc: 2 flg: E typ: 1 – DATA
brn: 0 bdba: 0×180640e ver: 0×01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0×01 0×0014.021.00000015 0×0200009f.02c5.0e C-U- 0 scn 0×0000.000ab285 －－这个scn就是control scn，在TRN CTL有记录，对应为701061
0×02 0×0010.016.00000014 0×02000074.0294.0d C— 0 scn 0×0000.000a9432

ref: http://www.easyora.net/blog/scn_block_scn.html