In-depth study of the data block Architecture

Environment:

SQL> select * from v$version where rownum=1;BANNER----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProdSQL> !uname -aLinux localhost.localdomain 2.6.18-308.el5xen #1 SMP Fri Jan 27 17:59:00 EST 2012 i686 i686 i386 GNU/Linux

(I) physical structure of data block

(Ii) a dump process on the block

hr@ORCL> create table think(a varchar2(10));Table created.hr@ORCL> insert into think values('a');1 row created.hr@ORCL> commit;Commit complete.hr@ORCL> execute show_space('think','auto');Total Blocks............................8Total Bytes.............................65536Unused Blocks...........................0Unused Bytes............................0Last Used Ext FileId....................4Last Used Ext BlockId...................401Last Used Block.........................8PL/SQL procedure successfully completed.sys@ORCL> alter system dump datafile 4 block 404;System altered.

TRC is as follows:

Start dump data blocks tsn: 4 file#: 4 minblk 407 maxblk 407buffer tsn: 4 rdba: 0x01000197 (4/407)scn: 0x0000.000a2707 seq: 0x03 flg: 0x02 tail: 0x27070603frmt: 0x02 chkval: 0x0000 type: 0x06=trans dataHex dump of block: st=0, typ_found=1Dump of memory from 0x0E1BA400 to 0x0E1BC400E1BA400 0000A206 01000197 000A2707 02030000  [.........'......]E1BA410 00000000 00000001 0000CD8A 000A26FE  [.............&..]E1BA420 00000000 00320002 01000191 00050001  [......2.........]E1BA430 00000100 0080000F 002300AE 00002001  [..........#.. ..]E1BA440 000A2707 00000000 00000000 00000000  [.'..............]E1BA450 00000000 00000000 00000000 00000000  [................]E1BA460 00000000 00010100 0014FFFF 1F7B1F93  [..............{.]E1BA470 00001F7B 1F930001 00000000 00000000  [{...............]E1BA480 00000000 00000000 00000000 00000000  [................]        Repeat 502 timesE1BC3F0 00000000 2C000000 61010101 27070603  [.......,...a...']Block header dump:  0x01000197 Object id on Block? Y seg/obj: 0xcd8a  csc: 0x00.a26fe  itc: 2  flg: E  typ: 1 - DATA     brn: 0  bdba: 0x1000191 ver: 0x01 opc: 0     inc: 0  exflg: 0 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc0x01   0x0001.005.00000100  0x0080000f.00ae.23  --U-    1  fsc 0x0000.000a27070x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000data_block_dump,data header at 0xe1ba464===============tsiz: 0x1f98hsiz: 0x14pbl: 0x0e1ba464bdba: 0x01000197     76543210flag=--------ntab=1nrow=1frre=-1fsbo=0x14fseo=0x1f93avsp=0x1f7btosp=0x1f7b0xe:pti[0]      nrow=1  offs=00x12:pri[0]     offs=0x1f93block_row_dump:tab 0, row 0, @0x1f93tl: 5 fb: --H-FL-- lb: 0x1  cc: 1col  0: [ 1]  61end_of_block_dumpEnd dump data blocks tsn: 4 file#: 4 minblk 407 maxblk 407

(Iii) Information about three types of headers contained in data blocks: ① cache header: data blocks are read and write operations through the data block buffer cache, therefore, it provides 20 bytes of cache header and 4 bytes of tail to cache for reading and managing.

TRC references:

  buffer tsn: 4 rdba: 0x01000197 (4/407)  scn: 0x0000.000a2707 seq: 0x03 flg: 0x02 tail: 0x27070603  frmt: 0x02 chkval: 0x0000 type: 0x06=trans data

TRC:

TRC:
Rdba: includes the relative file number of the data file where tablespace is located, file # = 4, and block_id = 407 of the data block.
SCN: occupies 6 bytes and the last changed SCN. Including 2 bytes high byte (scn wrap), and 4 bytes low byte (SCN Base)
Sequence: occupies 1 byte. Its usage is unclear. It may be a secondary SCN change.
Flag: occupies 1 byte. flag indicates a flag, such as deleting a flag.
Format: 1 byte is used to differentiate versions. Oracle 8 is 1, followed by 2
Checksum: occupies 2 bytes, which is related to the db_block_checksum parameter.

The following compares db_block_checking and db_block_checksum:

SQL> show parameter db_block_checksumNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------db_block_checksum                    string      TRUESQL> show parameter db_block_checkingNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------db_block_checking                    string      FALSE

Db_block_checking: the check and opening of the logical Bad blocks (whether the block header and block body are consistent) will consume 10% ~ 15% performance, checks the system tablespace even if it is false
Db_block_checksum: Check Physical Bad blocks
Block Type: occupies 1 byte and is often used. 1 = undo segment header block; 2 = undo data block; 5 = Data Segment header block; 6 = data block
Several other types:
0x20 = first level bitmap Block
0x21 = Second level bitmap Block
Bitmap for assm
0x23 = pagetable segment Header
Assm field Header
Record high level and low level, and record the BMB position of level 1, 2, and possible 3 level BMB position.

Tail: contains 2 bytes of the SCN base in the SCN (low-order), block type, and sequence number. When a block is read, check whether tail is consistent with the block header to ensure that the block is not damaged)

 

② Transaction header: the header information provided by the Transaction layer. It occupies a total of 48 bytes, including the control information of 24 bytes, and a series of Interested Transaction slot (ITS)

A total of 48 bytes, including 24 bytes control information and a series of Interested Transaction slot (ITS ). These its combinations are called Interested Transaction list (ITL ). Initial ITL slot
The number is determined by initrans (index branch block has only one slot ). If there is enough space available, Oracle will dynamically allocate these slots as needed until they are limited by space or reach maxtrans.

TRC references:

Block header dump:  0x01000197 Object id on Block? Y seg/obj: 0xcd8a  csc: 0x00.a26fe  itc: 2  flg: E  typ: 1 - DATA     brn: 0  bdba: 0x1000191 ver: 0x01 opc: 0     inc: 0  exflg: 0 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc0x01   0x0001.005.00000100  0x0080000f.00ae.23  --U-    1  fsc 0x0000.000a27070x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000

TRC:

Object number (SEG/obj): occupies 4 bytes, which indicates the object number (0xcd8a = 52618) of the segment recorded in OBJ $)
Cleanout SCN (CSC): occupies 6 bytes, And the last full cleanout SCN
ITL count (ITC): number of slots occupied by 1 byte and itl
Flag: occupies 2 bytes. O indicates that the block is on freelist. Otherwise, the flag is "-"
For the assm segment, flag is always e. Because assm automatically manages freelists, the availability of extent is defined in 1 level BMB.
Block Type: occupies 1 byte. 1 = data; 2 = index
ITL freelist slot (FSL): occupies 1 byte. Index to the first slot on the ITL freelist
Next freelist block (fnx): occupies 4 bytes. Rdba of the next block in segment freelist
Version: 1 byte
Each ITL entry includes the following content:
Xid: including xidusn, xidslot, and xidsqn
UBA: Includes dBA and sequence number of the rollback segment block.
Flag:
---- = Transaction is active, or committed pending cleanout
C --- = Transaction has been committed and locks cleaned out
-B -- = This undo record contains the Undo for this ITL entry
-- U-= Transaction committed (maybe long ago); SCN is an upper bound
--- T = Transaction was still active at Block cleanout SCN
Locks: Row-Level Lock
SCN or free space credit: 6bytes. If the transaction has been cleaned out, this value is SCN; otherwise, the first two bytes indicate the number of spaces in the block released by the transaction.

 

③ Data header: data area is used to store user data. Data area also includes data header, row data, and available space. However, the data header and row data structures of cluster blocks, table blocks, and index block are different. Here we mainly introduce table blocks.

Including 14bytes data header, 4 Bytes/Table dictionary, 2 bytes/row dictionary, that is, every row added, the row dictionary will be 2 more bytes used to record the row, it can be reflected through the hsiz below. Table directory is mainly used for cluster block and can be shared to multiple tables. For table block, table directory is always 1.

TRC references:

data_block_dump,data header at 0xe1ba464===============tsiz: 0x1f98hsiz: 0x14pbl: 0x0e1ba464bdba: 0x01000197     76543210flag=--------ntab=1nrow=1frre=-1fsbo=0x14fseo=0x1f93avsp=0x1f7btosp=0x1f7b0xe:pti[0]      nrow=1  offs=00x12:pri[0]     offs=0x1f93block_row_dump:tab 0, row 0, @0x1f93tl: 5 fb: --H-FL-- lb: 0x1  cc: 1col  0: [ 1]  61end_of_block_dumpEnd dump data blocks tsn: 4 file#: 4 minblk 407 maxblk 407

TRC:

Tsiz: total data area size
Hsiz: Data header size (14 + ntabs * 4 + nrows * 2)
PBL: pointer to buffer holding the block
Bdba: block DBA/rdba
Flag: N = pctfree hit (clusters), F = don't put on freelist, K = flushable cluster keys
Ntab: number of tables (> 1 so this is a cluster)
Nrow: number of rows
Fsbo: free space begin offset
Fseo: free space end offset
Avsp: available space in the block
Tosp: total available space when all transactions commit
Inference: Data Storage starts from the bottom of the block.
Tab 0, row 0, @ 0x1f93: 3 bytes row Header
TL: 5 FB: -- H-FL -- LB: 0x1 cc: 1 => LB: 0x1 represents transaction 0x1, CC represents the number of columns, each row has a 3-byte row header.
Col 0: [1] 61 => Column Length (1 byte if length <250; otherwise 3 bytes) and values
Where:
Fb => flag byte (kchdflpn): Row Type
K = cluster key (flags may change meaning if this is set to show hash cluster)
C = cluster Table Member
H = head piece of row
D = deleted row
F = first data piece
L = last data piece
P = first column continues from previous piece
N = last column continues in next piece

Lb => ITL number

 

Cc => column count

 

(Iv) Minimum row length

Oracle does have the minimun row length requirement, that is, the actual data of 5 bytes. If the row header of 3 bytes is added, and the row directory entry of 2 bytes occupied by block Hader
Column Length, which should be 11 bytes in total. Therefore, a block can contain a maximum of db_block_size/11 rows.

 

(V) For a block without any row inserted, and assuming the default initrans is used, its size should be:

Db_block_size-(Cache header + transaction header + Data header + tail + Table dictionary * ntab + row dictionary * row) = 8192-(20 + 48 + 14 + 4 + 4 + 0) = 8192-90 = 8102 bytes

 

(Vi) problem highlights

(1) What is the role of transaction slots? I think if a transaction changes or adds or deletes the row in the data block, the SID of the transaction should be directly placed in the row, because other transactions directly find the data of each row, they need to know which transaction controls the row, and the transaction slots is for the entire data block, how can we reflect the specific lock information of each row?

A: indicates the ITL of transaction on each row. If you use three different sessions to operate on the same block, and none of them are submitted:

--------------- Session 1----------------sql> insert into T values (1); 1 row created. --------------- session 2----------------sql> insert into T values (1); 1 row created. --------------- session 3----------------sql> insert into T values (3); 1 row created. start dump data blocks TSN: 8 file #: 3 minblk 842 maxblk 842 buffer TSN: 8 rdba: 0x00c0034a (3/842) SCN: 0x0000. 006d3763 seq: 0x01 flg: 0x00 tail: 0x37630 601 FRMT: 0x02 chkval: 0x0000 type: 0x06 = trans datablock header dump: 0x00c0034aobject ID on block? Yseg/obj: 0x80a9 CSC: 0x00. 6d36e9 ITC: 3 flg: O Typ: 1-data FSL: 0 fnx: 0x0 Ver: 0x01itl Xid UBA flag lck scn/fsc0x01 0x0001. 00b. 00001001 0x00800026. 011b. 02 ---- 1 FSC 0x0000.000000000x02 0x0006. 01d. 00001047 0x008028c2. 00c8. 3b ---- 1 FSC 0x0000.000000000x03 0x000a. 028.0000104c 0x008001bc. 00d5. 02 ---- 1 FSC 0x0000. when successfully flags is ---- = Transaction is active, or committed pending cleanoutdata_bloc K_dump, data header at 0x4791074 ================== tsiz: 0x1f88hsiz: 0x18pbl: 0x04791074bdba: 0x00c0034a 76543210 flag = -------- ntab = 1 nrow = 3 frre =-1 fsbo = 0x18fseo = Signature = 0x1f55tosp = signature [0] nrow = 3 offs = 00x12ri [0] offs = limit [1] offs = 0x1f760x16ri [2] offs = 0x1f70block_row_dump: tab 0, row 0, @ 0x1f7ctl: 6 FB: -- H-FL -- LB: 0x1 cc: 1 ---> LB: 0x1 is ITL Col 0: [2] C1 02tab 0, Row 1, @ 0x1f76tl: 6 FB: -- H-FL -- LB: 0x2 cc: 1 ---> LB: 0x2 is ITL Col 0: [2] C1 03tab 0, Row 2, @ 0x1f70tl: 6 FB: -- H-FL -- LB: 0x3 cc: 1 ---> LB: 0x3 is ITL Col 0: [2] C1 04end_of_block_dumpend dump data blocks TSN: 8 file #: 3 minblk 842 after 8423 sessions are submitted: Start dump data blocks TSN: 8 file #: 3 minblk 842 maxblk 842 buffer TSN: 8 rdba: 0x00c0034a (3/842) SCN: 0x0000. 006d3817 seq: 0x01 flg: 0x02 tail: 0x38170601frmt: 0 X02 chkval: 0x0000 type: 0x06 = trans datablock header dump: 0x00c0034aobject ID on block? Yseg/obj: 0x80a9 CSC: 0x00. 6d36e9 ITC: 3 flg: O Typ: 1-data FSL: 0 fnx: 0x0 Ver: 0x01itl Xid UBA flag lck scn/fsc0x01 0x0001. 00b. 00001001 0x00800026. 011b. 02 -- U-1 FSC 0x0000. 006d38170x02 0x0006. 01d. 00001047 0x008028c2. 00c8. 3b -- U-1 FSC 0x0000. 006d000020x03 0x000a. 028.0000104c 0x008001bc. 00d5. 02 -- U-1 FSC 0x0000. 006d3800flag -- U-indicates that the transaction has been committed to data_block_dump, data header at 0x4791074 ==================== tsiz: 0x1f88hsiz: 0x18pbl: 0x04791074bdba: 0x00c0034a 76543210 flag = -------- ntab = 1 nrow = 3 frre =-1 fsbo = 0x18fseo = Signature = 0x1f55tosp = signature [0] nrow = 3 offs = 00x12ri [0] offs = limit [1] offs = 0x1f760x16ri [2] offs = 0x1f70block_row_dump: tab 0, row 0, @ 0x1f7ctl: 6 FB: -- H-FL -- LB: 0x1 cc: 1col 0: [2] C1 02tab 0, Row 1, @ 0x1f76tl: 6 FB: -- H-FL -- LB: 0x2 cc: 1col 0: [2] C1 03tab 0, Row 2, @ 0x1f70tl: 6 FB: -- H-FL -- LB: 0x3 cc: 1col 0: [2] C1 04end_of_block_dumpend dump data blocks TSN: 8 file #: 3 minblk 842 maxblk 842

(2) It should not be 5 bytes of actual data. The 11 bytes are 3 bytes row headers, the two bytes occupied by block Hader, and the six-byte rowid, if I remember correctly, what is the structure of row migration?

A: rowid is only a logical concept and is not stored in data block. It only stores 6-bit rowid in the index block, pointing to the actual data of the index. Since Oracle 8, The rowid is 10 bytes, which is 6 bytes.

(3) What is the content of the 3-byte row header?

TL: 5 FB: -- H-FL -- LB: 0x1 cc: 1 => total length, status tag, used by which transaction, there are several columns, all the information should be included in the row header.

(4) Where can I mark the block? The modified block is no longer written to the disk in the memory? How is it marked?

A: dirty data blocks are placed on the so-called dirty list in the data buffer, waiting for data to be written to the disk. The buffer to which the data is written is placed on the LRU chain for reuse.

What is the basic architecture of using a row?

A: the starting position of the row in the block is the pointer value stored in the block row slots, which is the row dict entry of 2 bytes; 2 bytes can represent 64 K space, so the maximum Bock size is 64 K.

Some additional information should be included on the row, that is, the row header of three Bytes:
FB: -- H-FL -- LB: 0x1 cc: 2
One byte indicates the row lock information, and the ITL tag. 1 bytes indicates that up to 255 ITL records are supported.
One byte indicates the number of columns in this block. Because the column type, number of columns, and column sequence are the same and redundant, it is not necessary to store the row in the block, oracle obtains the information from the data dictionary;
1 bytes indicates the number of rows in the block. This indicates that when a row contains more than 255 columns, row migration will automatically take place and extra rows will be stored in other blocks.
Another bytes parameter indicates the status information of the row. If all the data is stored in the block, the Migration link will still occur.
Followed by the length of column N, the content of column N ....
Block_row_dump:
Tab 0, row 0, @ 0x1fb1-8113
TL: 7 FB: -- H-FL -- LB: 0x1 cc: 2 ---- TL = total length; CC = column count; each row has a 3-byte row header;
Col 0: [1] 64 ---- one byte records the column length (if the length is less than 250, 3 bytes are used otherwise ). Followed by the colomn data.
COL 1: [1] 65
End_of_block_dump
In case of row migration/Link, you must mark the position of row piece on the row, 6 bytes fffbbbbrrr