Functions of L1, L2, and L3 blocks:-facilitates the query of data blocks. L3 has pointers to L2, L2 has pointers to L1, and L1 has pointers and statuses of multiple data blocks. 1. Each L3 contains multiple L2 addresses (the first L3 is the segment header ). 2. Each L2 contains multiple L1 addresses. 3. Each L1 contains multiple data block addresses. ORACLE supports a maximum of three levels
Functions of L1, L2, and L3 blocks:-facilitates the query of data blocks. L3 has pointers to L2, L2 has pointers to L1, and L1 has pointers and statuses of multiple data blocks. 1. Each L3 contains multiple L2 addresses (the first L3 is the segment header ). 2. Each L2 contains multiple L1 addresses. 3. Each L1 contains multiple data block addresses. ORACLE supports a maximum of three levels
Functions of L1, L2, and L3 blocks:-facilitates the query of data blocks.
L3 has pointers to L2, L2 has pointers to L1, and L1 has pointers and statuses of multiple data blocks.
1. Each L3 contains multiple L2 addresses (the first L3 is the segment header ).
2. Each L2 contains multiple L1 addresses.
3. Each L1 contains multiple data block addresses.
ORACLE supports a maximum of three levels of Bitmap.
A level-1 bitmap is used to manage specific data blocks.
The second-level bitmap block records the address of the first-level bitmap block.
Level-3 bitmap blocks record the addresses of level-2 bitmap blocks. Segment Heade can manage the space of objects with a large amount of data, making it difficult to create another level-3 bitmap block.
1. How to Find the field header-the first L3 BlockBYS @ bys3> create tablespace test2 datafile '/u01/oradata/bys3/test22.dbf' size 10 m;
Tablespace created.
BYS @ bys3> create table test2 (aa varchar2 (10) tablespace test2;
Table created.
BYS @ bys3> insert into test2 valuees (789 );
1 row created.
BYS @ bys3> commit;
Commit complete.
BYS @ bys3> select dbms_rowid.ROWID_BLOCK_NUMBER (rowid), dbms_rowid.ROWID_RELATIVE_FNO (rowid), aa from test2;
DBMS_ROWID.ROWID_BLOCK_NUMBER (ROWID) DBMS_ROWID.ROWID_RELATIVE_FNO (ROWID) AA
----------------------------------------------------------------------------------
131 7 789
BYS @ bys3> select
Header_block, header_fileFrom
Dba_segmentsWhere segment_name = 'test2' and owner = 'bys ';
HEADER_BLOCK HEADER_FILE
------- Locate the field header from this statement
-----------------------
130 7
BYS @ bys3> select object_id, data_object_id from dba_objects where object_name = 'test2' and owner = 'bys ';
OBJECT_ID DATA_OBJECT_ID
------------------------
23199 23199
BYS @ bys3> alter table test2 allocate extent (size 960 k );
Table altered.
BYS @ bys3> alter system dump datafile 7 block 130;
System altered.
BYS @ bys3> select value from v $ diag_info where name like 'de % ';
VALUE
Bytes ----------------------------------------------------------------------------------------------------
/U01/diag/rdbms/bys3/bys3/trace/bys3_ora_4215.trc
################
Segment header-Explanation of the first L3 block: Start dump data blocks tsn: 7 file #: 7 minblk 130 maxblk 130
Block dump from cache: --- this section is the block header information of buffer cache, refer to: http://blog.csdn.net/haibusuanyun/article/details/17525523
Dump of buffer cache at level 4 for tsn = 7 rdba = 29360258
BH (0x20fe6d64) file #: 7 rdba: 0x01c00082 (7/130) class: 4 ba: 0x20cb6000
Set: 3 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0, 0
Dbwrid: 0 obj: 23199 objn: 23199 tsn: 7 afn: 7 hint: f
Hash: [0x2a39d804, 0x2a39d804] lru: [0x217f7ccc, 0x21befb08]
Lru-flags: on_auxiliary_list
Ckptq: [NULL] fileq: [NULL] objq: [0x21befb20, 0x22beab34] objaq: [0x2443a408, 0x2
1befb28]
St: XCURRENT md: NULL fpin: 'kt swh03: KT scts' tch: 2
Flags: block_written_once redo_since_read
LRBA: [0x0. 0.0] LSCN: [0x0.0] HSCN: [0xffff. ffffffff] HSUB: [1]
Block dump from disk: -- the following content in the physical data file
Buffer tsn: 7 rdba: 0x01c00082 (7/130)
Scn: 0x0000. 0082b051 seq: 0x01 flg: 0x04 tail: 0xb0512301
Frmt: 0x02 chkval: 0xf0d6 type: 0x23 = pagetable segment header -- data block type: Field HEADER
Hex dump of block: st = 0, typ_found = 1
Dump of memory from 0xB6BC0600 to 0xB6BC2600
B6BC0600 running a223 01C00082 0082B051 04010000 [#....... Q ......] -- B6BC0600 116a223, where 23 refers to the block type of the block header in the data block, which corresponds to type: 0x23 =.
Repeat 185 times ------- omitted most irrelevant output
B6BC25F0 00000000 00000000 00000000 B0512301 [...... # Q.]
Extent Control Header
-----------------------------------------------------------------
Extent Header: spare1: 0 spare2: 0 # extents: 18 # blocks: 384 -- 18 zones, 384 blocks = 16 zones-8 blocks, 2 zones-128 blocks, 16*8 + 2*128 = 384
Last map 0x00000000 # maps: 0 offset: 2716
Highwater: 0x01c00088 ext #: 0 blk #: 8 ext size: 8
High-water block DBA-0x01c00088-136 is the first block in the second zone. Insert only inserts blocks below the high water level <=, and advances in a zone-or half of the zone-it is said that the algorithm is very complicated. You can also find the high water level to query the blocks under the high water level.
During Concurrent Insertion, the High-level position will affect the limited number of concurrent inserts, because only blocks below the high-level can be inserted, such as 8KBLOCK; 128 blocks in 1 m zone, more than 128 concurrent threads have hot blocks, resulting in buffer busy waits .; for an 8 m zone, there will be 1024 blocks, and more than 1024 concurrent connections will also generate hot blocks.
# Blocks in seg. hdr's freelists: 0
# Blocks below: 5
Mapblk 0x00000000 offset: 0
Unlocked
--------------------------------------------------------
Low HighWater Mark: -- data under Low and high water levels has been used. Some data blocks are used between low and high levels, and some are not used-not formatted. The format is to add the OJBECT number to the block header, and the data dictionary is dba_objects.DATA_OBJECT_ID.
Highwater: 0x01c00088 ext #: 0 blk #: 8 ext size: 8
# Blocks in seg. hdr's freelists: 0
# Blocks below: 5
Mapblk 0x00000000 offset: 0
Level 1 BMB for High HWM block: 0x01c00080
Level 1 BMB for Low HWM block: 0x01c00080
--------------------------------------------------------
Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x01c00081 ---- DBA of the second-level bitmap block, which is converted to 0000 0001 1100. The first 10 digits of the file number are binary 111-decimal 7, and hexadecimal 81-decimal 129. during insertion, the L1 block under the L2 will be selected, and the DBA of the L1 will be changed here only when all L1 blocks under the L2 are used up.
Last Level 1 BMB: 0x01c00181
Last Level ii bmb: 0x01c00081
Last Level iii bmb: 0x00000000
Map Header: next 0x00000000 # extents: 18 obj #: 23199 flag: 0x10000000
Inc #0
Extent Map-area Map, which contains several areas, the starting address of the area, and the number of blocks.
-----------------------------------------------------------------
0x01c00080 length: 8
0x01c00088 length: 8
0x01c00090 length: 8
0x01c00098 length: 8
0x01c000a0 length: 8
0x01c000a8 length: 8
0x01c000b0 length: 8
0x01c000b8 length: 8
0x01c000c0 length: 8
0x01c000c8 length: 8
0x01c000d0 length: 8
0x01c000d8 length: 8
0x01c000e0 length: 8
0x01c000e8 length: 8
0x01c000f0 length: 8
0x01c000f8 length: 8
0x01c00100 length: 128
0x01c00180 length: 128
Auxillary Map-Auxiliary table, which is managed by L1 and managed by the partition-the starting position of the data block DBA-the starting address of the non-Metadata block .. The address of the secondary area shows which blocks share the same L1, as shown below:
--------------------------------------------------------
Extent 0: L1 dba: 0x01c00080 Data dba: 0x01c00083 -- 131, address of the first available Block
Extent 1: L1 dba: 0x01c00080 Data dba: 0x01c00088 -- 136. You can see that this L1 is the same as the previous L1, and one L1 manages two zones.
Extent 2: L1 dba: 0x01c00090 Data dba: 0x01c00091 -- 145
Extent 3: L1 dba: 0x01c00090 Data dba: 0x01c00098
Extent 4: L1 dba: 0x01c000a0 Data dba: 0x01c000a1
Extent 5: L1 dba: 0x01c000a0 Data dba: 0x01c000a8
Extent 6: L1 dba: 0x01c000b0 Data dba: 0x01c000b1
Extent 7: L1 dba: 0x01c000b0 Data dba: 0x01c000b8
Extent 8: L1 dba: 0x01c000c0 Data dba: 0x01c000c1
Extent 9: L1 dba: 0x01c000c0 Data dba: 0x01c000c8
Extent 10: L1 dba: 0x01c000d0 Data dba: 0x01c000d1
Extent 11: L1 dba: 0x01c000d0 Data dba: 0x01c000d8
Extent 12: L1 dba: 0x01c000e0 Data dba: 0x01c000e1
Extent 13: L1 dba: 0x01c000e0 Data dba: 0x01c000e8
Extent 14: L1 dba: 0x01c000f0 Data dba: 0x01c000f1
Extent 15: L1 dba: 0x01c000f0 Data dba: 0x01c000f8
Extent 16: L1 dba: 0x01c00100 Data dba: 0x01c00102 -- 128 blocks. L1 manages one zone.
In the 8 kb block and 8 m area, a L1 can manage 1024 blocks, and no experiment has been performed in a large area.
Extent 17: L1 dba: 0x01c00180 Data dba: 0x01c00182
--------------------------------------------------------
Second Level Bitmap block DBAs
--------------------------------------------------------
DBA 1: 0x01c00081 -- DBA of the level-2 bitmap block. There is only one L2. If there are multiple L2, it will be displayed here.
End dump data blocks tsn: 7 file #: 7 minblk 130 maxblk 130
######################################## ######## 3
DUMP L2 block -- locate the L2 DBA Based on the field HeaderBYS @ bys3> alter system dump datafile 7 block 129;
System altered.
BYS @ bys3> select value from v $ diag_info where name like 'de % ';
VALUE
Bytes ----------------------------------------------------------------------------------------------------
/U01/diag/rdbms/bys3/bys3/trace/bys3_ora_4982.trc
#######
Start dump data blocks tsn: 7 file #: 7 minblk 129 maxblk 129
Block dump from cache:
Dump of buffer cache at level 4 for tsn = 7 rdba = 29360257
Block dump from disk:
Buffer tsn: 7 rdba: 0x01c00081 (7/129)
Scn: 0x0000. 0082b04b seq: 0x03 flg: 0x04 tail: 0xb04b2103
Frmt: 0x02 chkval: 0xcf4e type: 0x21 = second level bitmap block -- L2
Hex dump of block: st = 0, typ_found = 1
Dump of memory from 0xB6B43600 to 0xB6B45600
B6B43600 109a221 01C00081 0082B04B 04030000 [!....... K...] 21 in a221 corresponds to type: 0x21 =
Repeat 496 times
B6B455F0 00000000 00000000 00000000 B04B2103 [.............! K.]
Dump of Second Level Bitmap Block
Number: 12 nfree: 12 ffree: 0 pdba: 0x01c00082 -- parent DBA, that is, block 130 of L3 address,
Number: 12 -- total number of L1, nfree: 12 -- number of idle L1,
Inc #: 0 Objd: 23199
Opcode: 1
Xid:
L1 Ranges: -- when multiple L1 instances are inserted, they are inserted in the order of L1 DBAs /////??????? When inserting data: HASH the data based on the PID to obtain a random value. Then, select L1 in L2 based on this value, which is affected by the high water level.
--------------------------------------------------------
0x01c00080 Free: 5 Inst: 1 --- block 128, Free: 5 mark the available space status in L1
0x01c00090 Free: 5 Inst: 1 -- block 144, Free: 0 FULL status
0x01c000a0 Free: 5 Inst: 1
0x01c000b0 Free: 5 Inst: 1
0x01c000c0 Free: 5 Inst: 1
0x01c000d0 Free: 5 Inst: 1
0x01c000e0 Free: 5 Inst: 1
0x01c000f0 Free: 5 Inst: 1
0x01c00100 Free: 5 Inst: 1
0x01c00101 Free: 5 Inst: 1
0x01c00180 Free: 5 Inst: 1
0x01c00181 Free: 5 Inst: 1
--------------------------------------------------------
End dump data blocks tsn: 7 file #: 7 minblk 129 maxblk 129
#####################################
DUMP L3 block -- find the dba of the first L1 Block Based on L1 Ranges in L2
BYS @ bys3> alter system dump datafile 7 block 128;
System altered.
BYS @ bys3> select value from v $ diag_info where name like 'de % ';
VALUE
Bytes ----------------------------------------------------------------------------------------------------
/U01/diag/rdbms/bys3/bys3/trace/bys3_ora_5162.trc
Start dump data blocks tsn: 7 file #: 7 minblk 128 maxblk 128
Block dump from cache:
Dump of buffer cache at level 4 for tsn = 7 rdba = 29360256
Block dump from disk:
Buffer tsn: 7 rdba: 0x01c00080 (7/128)
Scn: 0x0000. 0082afe2 seq: 0x01 flg: 0x04 tail: 0xafe22001
Frmt: 0x02 chkval: 0x9d2d
Type: 0x20 = FIRST LEVEL BITMAP BLOCK
Hex dump of block: st = 0, typ_found = 1
Dump of memory from 0xB6CBD600 to 0xB6CBF600
B6CBD600 running a220 01C00080 0082AFE2 04010000 [......] 20 of a220, indicating: type: 0x20 = FIRST LEVEL BITMAP BLOCK
B6CBD610 20179d2d 00000000 00000000 00000000 [-......]
B6CBD620 00000000 00000000 00000000 00000000 [......]
Repeat 1 times
Repeat 8 times
B6CBD780 00000000 00000000 00000000 55551511 [...... UU]
B6CBD790 00000000 00000000 00000000 00000000 [......]
Repeat 485 times
B6CBF5F0 00000000 00000000 00000000 AFE22001 [......]
Dump of First Level Bitmap Block
--------------------------------
Nbits: 4 nranges: 2 parent dba: 0x01c00081 poffset: 0 -- parent dba: upper-level DBA, L2 address -- block 129
Unformatted: 8 total: 16 first useful block: 3
Owning instance: 1
Instance ownership changed at 01/22/2014 11:22:40
Last successful Search 01/22/2014 11:22:40
Freeness Status: nf1 0 nf2 0 nf3 0 nf4 5
Extent Map Block Offset: 4294967295
First free datablock: 3
Bitmap block lock opcode 0
Locker xid: 0x0000. 000.00000000
Dealloc scn: 3.0
Flag: 0x00000001 (-/HWM)
Inc #: 0 Objd: 23199
HWM Flag: HWM Set
Highwater: 0x01c00088 ext #: 0 blk #: 8 ext size: 8
The high water level is at Block 0x01c00088 136, and the first block in the next zone
# Blocks in seg. hdr's freelists: 0
# Blocks below: 5
Mapblk 0x00000000 offset: 0
--------------------------------------------------------
DBA Ranges: -- the range of data blocks managed by L1
--------------------------------------------------------
0x01c00080 Length: 8 Offset: 0
0x01c00088 Length: 8 Offset: 8
0: Metadata 1: Metadata 2: Metadata 3: 75-100% free --- Metadata is a block with no data.
4: 75-100% free 5: 75-100% free 6: 75-100% free 7: 75-100% free
8: unformatted 9: unformatted 10: unformatted 11: unformatted
12: unformatted 13: unformatted 14: unformatted 15: unformatted can be DUMP 12 and 5 to compare the difference between formatting and unformatting.
There are 7 formats for the status of data blocks in L1 blocks: Unformat 75-100% 50-75% 25-50% 0-25% FULL Metadata
--------------------------------------------------------
End dump data blocks tsn: 7 file #: 7 minblk 128 maxblk 128
