Talking about the HWM of Oracle's high water level line

Talking about the high water level line of Oracle-The HWM high water level is the upper limit of the data that can be accommodated in the record segment. The full table in the high water level segment scans the first reading segment header, then, find HWM in the header block and use the experiment to understand HWM www.2cto.com [SQL] of Oracle from the outside. -- The t table has a data hr @ ORCL> select * from t; id name ---------- 1 AAAAA -- find the header block www.2cto.com hr @ ORCL> select header_file, header_block from dba_segments where segment_name = 'T' and owner = 'hr '; HEADER_FILE HEADER_BLOCK ----------- ---------- 4 387 -- open another session and dump the header block sys @ ORCL> alter session set tracefile_identifier = 'sys _ dump_t_01 '; Session altered. sys @ ORCL> alter system dump datafile 4 block 387; System altered. part of the trc content of dump is extracted into www.2cto.com [SQL] Extent Control Header extends Extent Header: spare1: 0 spare2: 0 # extents: 1 # blocks: 8 last map 0x00000000 # maps: 0 offset: 2716 Highwater: 0x01000189 ext #: 0 blk #: 8 ext size: 8 -- Highwater is high water level,
0x01000189 this is the address of HWM # blocks in seg. hdr's freelists: 0 # blocks below: 5 mapblk 0x00000000 offset: 0 Unlocked slow Low HighWater Mark: Highwater: 0x01000189 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: 0x01000181 Level 1 BMB for Low HWM block: 0x0100018 1 ---------------------------------------------------- Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0 L2 Array start offset: 0x00001434 First Level 3 BMB: 0x00000000 L2 Hint for inserts: 0x01000182 Last Level 1 BMB: 0x01000181 Last Level ii bmb: 0x01000182 Last Level iii bmb: 0x00000000 Map Header: next 0x00000000 # extents: 1 obj #: 52713 flag: 0x10000000 Inc #0 Extent Map ----------------------- -------------------------------------- 0x01000181 length: 8 Auxillary Map worker Extent 0: L1 dba: 0x01000181 Data dba: 0x01000184 worker Second Level Bitmap block DBAs primary DBA 1: 0x01000182 d dump data blocks tsn: 4 file #: 4 minblk 387 maxbl K 387 [SQL] -- perform a full table scan on the t table hr @ ORCL> set autot traceonly hr @ ORCL> select * from t; Execution Plan -------------------------------------------------- Plan hash value: 1601196873 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | -------------------------------------------------------------------------- | 0 | SE Lect statement | 1 | 20 | 3 (0) | 00:00:01 | 1 | table access full | T | 1 | 20 | 3 (0) | 00:00:01 | notice Note ------dynamic sampling used for this statement Statistics limit 0 recursive CILS 0 db block gets 7 consistent gets -- the whole table has read 6 blocks 0 physical reads 0 redo size 469 byt Es sent via SQL * Net to client 385 bytes provisioned ed via SQL * Net from client 2 SQL * Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) how are the six rows processed blocks calculated? [SQL] hr @ ORCL> select file_id, block_id, blocks from dba_extents where segment_name = 'T '; FILE_ID BLOCK_ID BLOCKS ---------- 4 385 8 This t section uses a total of 8 BLOCKS, respectively 385 386 387 388 389 390 Highwater: 0x391 that is: the block 393 of file 4 can be calculated from the following dbms_utility package [SQL] sys @ ORCL> select to_number ('123', 'xxxxxxxx') from dual; TO_NUMBER ('2008080', 'xxxxxxxx') ---------------------------- 01000189 sys @ ORCL> select round (16777609) from dual; round (16777609) limit 4 sys @ ORCL> select round (16777609) from dual; DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK (16777609) ------------------------------------------------- 393 read the header block once: no. 4 file No. 387 reads the 388 389 390 391 392 and other 5 blocks under the high water level. In this way, a total of 6 blocks are read. Note: 385 is the first level bitmap block 386 is the second level bitmap block. Then we analyze the dump content: www.2cto.com [SQL] Low HighWater Mark: Highwater: 0x01000189 ext #: 0 blk #: 8 ext size: 8 next we will talk about the possible existence of formated blocks between highwater mark and low highwater mark low high water mark and high water mark.
Unformatted block

First, clarify the meaning of the Field blocks in dba_tables. dba_tables.blocks records the total number of formatted blocks obtained from the analysis, while the formatted block and unfomatted block may exist between low hwm and high hwm. it cannot represent low hwm or high hwm. If there is no formatted block between low hwm and high hwm, dba_tables.blocks and
Blocks under low hwm is consistent. So what are unformatted blocks in Oracle? Unformatted: This block already belongs to this segment, but it still keeps the original format unchanged. It is used to clear the data in the block, change the block header to the segments in the MSSM tablespace of this object. There is only one high level. The blocks under the high level are formatted, but the segments in the ASSM tablespace have two high levels: high water level and high water level: Highwater: 0x01000189 and Low HighWater Mark Highwater :: 0x01000189 all blocks at low and high levels are formatted, but the blocks between low and high levels may be formatted, it may also be because the current t's high water level is file 4, block 393; its low water level is file 4, block 393. Let's take a look at t's current data_object_id: [SQL] hr @ ORCL> select object_id, data_object_id from dba_objects where object_name = 'T'; OBJECT_ID DATA_OBJECT_ID ---------- ------------ 52713 52714 here it is obvious that T's data_object_id is greater than object_id, the move or truncate operation has occurred on t. Note that for the truncate operation, the data_object_id after truncate is not necessarily in the original data _
Add 1 [SQL] sys @ ORCL> select to_char ('000000', 'xxxxxxxx') from dual; TO_CHAR ('--------- CDEA in other words, t is in the block between its low and high water level, as long as the data recorded on this block
_ Object_id is not equal to CDEA. We can use seg/obj in the dump part of the Block header to determine its data_object_id.
If it is equal to the segment number, the block is an unformatted block. That is to say, you can use data_object_id to determine whether the block is at the position of HWM and LHWM. Why does Oracle Add a low-level high-level setting? For what purpose? During full table scan, it is at a low level,
Or to a high level? The Oracle design principle is to distribute a large operation to many small operations to ensure that a large operation will not
This idea is embodied in both delayed submission and whatever it takes for too long. This is similar to the concept of Linux, and the combination of low and high water level lines, it is the embodiment of this idea that you do not need to format all the newly allocated blocks at a time, and leave a part for the next operation to complete the full table scan, which is usually read to the low-and high-water level line, then, read the formatted block between the low height and the high height based on the bitmap,
How to view HWM without formatting blocks? How do I know how much free space is available in HWM? The most practical method is dump segment_header, which is fast and has no impact on the application. in addition, "# blocks in seg in trc. hdr's freelists: "You can tell us the number of idle blocks in HWM. Note that if dba_segments.freelist_groups> 1, freelist will not be included in the segment header. For example, freelist_group = 3, then you need to dump the three blocks following the dump header separately to view each group
How can we reduce the number of freelist in HWM? Shrink can be used for www.2cto.com ① expdp/impdp ② 10g and later versions. Note that the tablespace In the table must be an automatic segment space management alter table tab_name enable row movement; alter table tab_name shrink space; ③ use move. However, during Move, DML statements, such as UPDATE, you also need to consider the space problem. In short, moving locks the table and TM also affects the index. Therefore, remember to rebuild index alter table move tab_name; move alter table move tab_name tablespace tbs_name in the current tablespace; move it to another tablespace ④ CTAS and rename, rebuild index ⑤ online redefinition, etc .......