Concept of high water level:
All the oracle segments (segments here, for ease of understanding, we recommend that you use segment as a synonym for the table) have an upper limit to accommodate data within the segments, we call this upper limit "high water mark" or HWM. This HWM is a tag used to indicate how many unused data blocks have been allocated to this segment. In principle, HWM only increases and does not shrink. Even if all the data in the table is deleted, HWM is still the original value. Due to this feature, HWM is like the highest historical water level of a reservoir, this is the original meaning of HWM.
For details about this concept, refer:
Http://www.blogjava.net/decode360/archive/2009/07/14/287767.html
Http://www.cnblogs.com/linjiqin/archive/2012/01/15/2323030.html
High water level and low high water level: the status of data blocks between low water level and high water level may be unformatted or formatted. Below the low and high levels are formatted and can be used.
HWM has the following impact on database operations:
1. Full table scan usually reads all the database blocks that belong to the table until the HWM mark, even if the table does not have any data.
3. Even if there are idle database blocks below hwm and the append keyword is used for data insertion, the data blocks above HWM are used for data insertion, And the HWM will automatically increase.
2. During insertion, only data blocks below the high level can be inserted by default, which may cause hot blocks and performance problems in high concurrency.
The following is an experiment to verify:
A full table scan usually reads all the database blocks that are marked with HWM at a low level, even if the table does not have any data -- DELETE for testing. TRUNCATE will be released.
Solution:Expdp/impdp, shrink: You need to enable row movement in the ASSM table. MOVE does not support online, or crteate table a as. Delete the original TABLE and change the new TABLE to its original name ..
######################################## ######## 1. DELETE does not change the high water level, but after deletion, the table can be reclaimed by performing the SHRINK operation, reducing the high water level BYS @ bys3> create table test12 as select * from dba_objects;
Table created.
BYS @ bys3> insert into test12 select * from dba_objects;
17558 rows created.
BYS @ bys3> commit;
Commit complete.
BYS @ bys3> insert into test12 select * from test12;
35116 rows created.
BYS @ bys3> commit;
Commit complete.
BYS @ bys3> insert into test12 select * from test12;
70232 rows created.
BYS @ bys3> commit;
Commit complete.
BYS @ bys3> insert into test12 select * from test12;
140464 rows created.
BYS @ bys3> commit;
Commit complete.
BYS @ bys3> alter system checkpoint; --- checkpoint
System altered.
BYS @ bys3> select header_block, header_file from dba_segments where segment_name = 'test12' and owner = 'bys ';
HEADER_BLOCK HEADER_FILE
-----------------------
922 4
BYS @ bys3> select sum (blocks) from dba_extents where segment_name = 'test12' and owner = 'bys ';
SUM (BLOCKS)
-----------
3840
BYS @ bys3> select count (extent_id) from dba_extents where segment_name = 'test12' and owner = 'bys ';
COUNT (EXTENT_ID)
----------------
45
Execute multiple times and take the following average value:
BYS @ bys3> set autotrace traceonly stat
BYS @ bys3> select * from test12;
280960 rows selected.
Elapsed: 00:00:05. 70
Statistics
----------------------------------------------------------
0 recursive cballs
0 db block gets
23504 consistent gets
3710 physical reads
0 redo size
29190599 bytes sent via SQL * Net to client
206449 bytes encoded ed via SQL * Net from client
18732 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
280960 rows processed
BYS @ bys3> alter system dump datafile 4 block 922;
System altered.
BYS @ bys3> select value from v $ diag_info where name like 'de % ';
VALUE
Bytes ----------------------------------------------------------------------------------------------------
/U01/diag/rdbms/bys3/bys3/trace/bys3_ora_25994.trc
############## DUMP section header to view the high water level information: Highwater: 0x01001180 -- block 4480
Extent Control Header
-----------------------------------------------------------------
Extent Header: spare1: 0 spare2: 0 # extents: 45 # blocks: 3840
Last map 0x00000000 # maps: 0 offset: 2716
Highwater: 0x01002100 ext #: 44 blk #: 128 ext size: 128 -- high-water DBA: 0x01002100, Block 8448
# Blocks in seg. hdr's freelists: 0
# Blocks below: 3784
Mapblk 0x00000000 offset: 44
Unlocked
--------------------------------------------------------
Low HighWater Mark:
Highwater: 0x01002080 ext #: 43 blk #: 128 ext size: 128 -- low high water level 0x01002080
# Blocks in seg. hdr's freelists: 0
# Blocks below: 3712
Mapblk 0x00000000 offset: 43
Level 1 BMB for High HWM block: 0x01002081
Level 1 BMB for Low HWM block: 0x01002001
--------------------------------------------------------
Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x01000399
Last Level 1 BMB: 0x01002081
Last Level ii bmb: 0x01000399
Last Level iii bmb: 0x00000000
Map Header: next 0x00000000 # extents: 45 obj #: 23303 flag: 0x10000000
Inc #0
Extent Map
-----------------------------------------------------------------
0x01000398 length: 8
% ........................
0x01002080 length: 128 -- the last L1 DBA is block 8320 and manages 128 blocks. The high water level is block 8448, which is the last data block of the last L1.
Auxillary Map
--------------------------------------------------------
Extent 0: L1 dba: 0x01000398 Data dba: 0x0100039b
........................
Extent 44: L1 dba: 0x01002080 Data dba: 0x01002082 high water level should be the last block managed by L1: 0x01002082-8322,
--------------------------------------------------------
Second Level Bitmap block DBAs
--------------------------------------------------------
DBA 1: 0x01000399
After the table is analyzedBYS @ bys3> analyze table test12 compute statistics;
Table analyzed.
BYS @ bys3> select pct_free, pct_used, blocks, avg_row_len, chain_cnt from tabs where table_name = 'test12 ';
PCT_FREE PCT_USED BLOCKS AVG_ROW_LEN CHAIN_CNT
---------------------------------------------------
10 3784 93 0
BYS @ bys3> set autotrace traceonly stat
BYS @ bys3> select * from test12;
280960 rows selected.
Elapsed: 00:00:05. 46
Statistics
----------------------------------------------------------
0 recursive cballs
0 db block gets
22204 consistent gets
0 physical reads
0 redo size
29190599 bytes sent via SQL * Net to client
206449 bytes encoded ed via SQL * Net from client
18732 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
280960 rows processed
The DUMP information is the same as that when no table analysis is performed.
##################### DELETE all data in a table BYS @ bys3> delete test12;
280944 rows deleted.
BYS @ bys3> commit;
Commit complete.
BYS @ bys3> alter system checkpoint;
System altered.
BYS @ bys3> select header_block, header_file from dba_segments where segment_name = 'test12' and owner = 'bys ';
HEADER_BLOCK HEADER_FILE
-----------------------
922 4
BYS @ bys3> select sum (blocks) from dba_extents where segment_name = 'test12' and owner = 'bys ';
SUM (BLOCKS)
-----------
3840
BYS @ bys3> select count (extent_id) from dba_extents where segment_name = 'test12' and owner = 'bys ';
COUNT (EXTENT_ID)
----------------
45
BYS @ bys3> analyze table test12 compute statistics;
Table analyzed.
BYS @ bys3> select pct_free, pct_used, blocks, avg_row_len, chain_cnt from tabs where table_name = 'test12 ';
PCT_FREE PCT_USED BLOCKS AVG_ROW_LEN CHAIN_CNT
---------------------------------------------------
10 3784 0 0
BYS @ bys3> alter system dump datafile 4 block 922;
System altered.
BYS @ bys3> select value from v $ diag_info where name like 'de % ';
VALUE
Bytes ----------------------------------------------------------------------------------------------------
/U01/diag/rdbms/bys3/bys3/trace/bys3_ora_26054.trc
BYS @ bys3> set autotrace traceonly stat
BYS @ bys3> select * from test12;
No rows selected
Elapsed: 00:00:00. 02
Statistics
----------------------------------------------------------
0 recursive cballs
0 db block gets
3721 consistent gets
2 physical reads
0 redo size
1183 bytes sent via SQL * Net to client
408 bytes encoded ed via SQL * Net from client
1 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
############## The DUMP information after the DELETE operation is no different from that when the previous operation is not deleted. After deleting the DELETE statement, run the SHRINK command on the table: -- space can be reclaimed to reduce the high-level BYS @ bys3> alter table test12 enable row movement;
Table altered.
BYS @ bys3> alter table test12 shrink space; -- SHRINK and MOVE. For details, see: shrink merging data blocks -- solving the problem of data block fragmentation.
Table altered.
BYS @ bys3> alter system checkpoint;
System altered.
BYS @ bys3> select pct_free, pct_used, blocks, avg_row_len, chain_cnt from tabs where table_name = 'test12 ';
PCT_FREE PCT_USED BLOCKS AVG_ROW_LEN CHAIN_CNT
---------------------------------------------------
10 3784 0 0
BYS @ bys3> analyze table test12 compute statistics; after the table is analyzed, the BLOCKS information in tabs will change.
Table analyzed.
BYS @ bys3> select pct_free, pct_used, blocks, avg_row_len, chain_cnt from tabs where table_name = 'test12 ';
PCT_FREE PCT_USED BLOCKS AVG_ROW_LEN CHAIN_CNT
---------------------------------------------------
10 1 0 0
BYS @ bys3> alter system dump datafile 4 block 922;
System altered.
BYS @ bys3> select value from v $ diag_info where name like 'de % ';
VALUE
Bytes ----------------------------------------------------------------------------------------------------
/U01/diag/rdbms/bys3/bys3/trace/bys3_ora_26432.trc
#########################
DUMP information is as follows: -- we can see that the space has been recycled. High water level has also declined
Extent Control Header
-----------------------------------------------------------------
Extent Header: spare1: 0 spare2: 0 # extents: 1 # blocks: 8
Last map 0x00000000 # maps: 0 offset: 2716
Highwater: 0x0100039c ext #: 0 blk #: 4 ext size: 8
# Blocks in seg. hdr's freelists: 0
# Blocks below: 1
Mapblk 0x00000000 offset: 0
Unlocked
--------------------------------------------------------
Low HighWater Mark:
Highwater: 0x0100039c ext #: 0 blk #: 4 ext size: 8
# Blocks in seg. hdr's freelists: 0
# Blocks below: 1
Mapblk 0x00000000 offset: 0
Level 1 BMB for High HWM block: 0x01000398
Level 1 BMB for Low HWM block: 0x01000398
--------------------------------------------------------
Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x01000399
Last Level 1 BMB: 0x01000398
Last Level ii bmb: 0x01000399
Last Level iii bmb: 0x00000000
Map Header: next 0x00000000 # extents: 1 obj #: 23303 flag: 0x10000000
Inc #1
Extent Map
-----------------------------------------------------------------
0x01000398 length: 8
Auxillary Map
--------------------------------------------------------
Extent 0: L1 dba: 0x01000398 Data dba: 0x0100039b
--------------------------------------------------------
Second Level Bitmap block DBAs
--------------------------------------------------------
DBA 1: 0x01000399
End dump data blocks tsn: 4 file #: 4 minblk 922 maxblk 922
################################## 2. the TRUNCATE operation can directly recycle space and change the high water level. However, if the table is not partitioned, it cannot be used and there are many scenarios without DELETE. Create table test13 as select * from dba_objects;
BYS @ bys3> alter system checkpoint;
System altered.
BYS @ bys3> alter system dump datafile 4 block 466;
System altered.
############ DUMP file information:
Extent Control Header
-----------------------------------------------------------------
Extent Header: spare1: 0 spare2: 0 # extents: 17 # blocks: 256
Last map 0x00000000 # maps: 0 offset: 2716
Highwater: 0x010011f5 ext #: 16 blk #: 117 ext size: 128
# Blocks in seg. hdr's freelists: 0
# Blocks below: 245
Mapblk 0x00000000 offset: 16
Unlocked
--------------------------------------------------------
Low HighWater Mark:
Highwater: 0x010011f5 ext #: 16 blk #: 117 ext size: 128
# Blocks in seg. hdr's freelists: 0
# Blocks below: 245
Mapblk 0x00000000 offset: 16
Level 1 BMB for High HWM block: 0x01001181
Level 1 BMB for Low HWM block: 0x01001181
--------------------------------------------------------
Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x010001d1
Last Level 1 BMB: 0x01001181
Last Level ii bmb: 0x010001d1
Last Level iii bmb: 0x00000000
Map Header: next 0x00000000 # extents: 17 obj #: 23300 flag: 0x10000000
Inc #0
Extent Map
################
Perform the TRUNCATE operation and then DUMP the field header.BYS @ bys3> truncate table test13;
Table truncated.
BYS @ bys3> alter system checkpoint;
System altered.
BYS @ bys3> alter system dump datafile 4 block 466;
System altered.
#########################
Extent Header: spare1: 0 spare2: 0 # extents: 1 # blocks: 8
Last map 0x00000000 # maps: 0 offset: 2716
Highwater: 0x010001d3 ext #: 0 blk #: 3 ext size: 8
# Blocks in seg. hdr's freelists: 0
# Blocks below: 0
Mapblk 0x00000000 offset: 0
Disk Lock: Locked by xid: 0x0002. 019.00001354
--------------------------------------------------------
Low HighWater Mark:
Highwater: 0x010001d3 ext #: 0 blk #: 3 ext size: 8
# Blocks in seg. hdr's freelists: 0
# Blocks below: 0
Mapblk 0x00000000 offset: 0
Level 1 BMB for High HWM block: 0x010001d0
Level 1 BMB for Low HWM block: 0x010001d0
--------------------------------------------------------
Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x010001d1
Last Level 1 BMB: 0x010001d0
Last Level ii bmb: 0x010001d1
Last Level iii bmb: 0x00000000
Map Header: next 0x00000000 # extents: 1 obj #: 23304 flag: 0x10000000
Inc #0
Extent Map
-----------------------------------------------------------------
0x010001d0 length: 8
Auxillary Map
--------------------------------------------------------
Extent 0: L1 dba: 0x010001d0 Data dba: 0x010001d3
--------------------------------------------------------
Second Level Bitmap block DBAs
--------------------------------------------------------
DBA 1: 0x010001d1
