JL Computer Consultancy

Block cleanout - fast or delayed.

January 2001


I have written this note purely for entertainment value. It highlights the differences that appear when a block is subject to 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 [Ed June 2009: in fact if the block has been written to disc, flushed from memory, then reloaded by another session it can still be marked on commit]. 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 process that is reading it. 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 first data block.

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

c) Thrash the rollback segments to recycle and lose the history of my update, then dump another block far down the table.

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

Back to Main Index of Topics