Oracle Basic Article---HWM

3.1.1 HWM (High water Mark)3.1.1.1 definition

To manage space, Oracle Database tracks the state of blocks in the segment. The High Water mark (HWM) is the "point" segment beyond which data blocks is unformatted and has never been Used.

• Above the HWM
  These blocks is unformatted and has never been used.

• Below the HWM
• Allocated, but currently unformatted and unused
• Formatted and contain data
• Formatted and empty because the data was deleted

> ———-

The low -water mark (low HWM) marks the point below which all blocks is known to be formatted because they E Ither contain data or formerly contained data.

The database chooses a block between the HWM and low HWM and writes to it. The database could has just as easily chosen any other block between the HWM and low HWM, or any block below the low HWM that had available space.

3.1.1.2 Understanding

In the storage of Oracle data, storage space can be imagined as a reservoir, the data is imagined as water in the reservoir. The position of the water in the reservoir is a line called the watermark, in Oracle, this line is called the High watermark (High-warter mark, HWM). When the database table was just established, because there is no data, so this time the watermark is empty, that is, HWM is the lowest value. When the data is inserted, the high watermark will go up, but there is also a feature here, that is, if you delete the data using a DELETE statement, the data is deleted, but the high watermark is not lowered, or you just deleted the data before the high water level. In other words, this high watermark will only rise in daily additions and deletions and will not fall.

Let's talk about the features of the SELECT statement in Oracle. The SELECT statement scans the data in the table one at a time, but how much data storage blocks are scanned, this does not mean how much data is in the database, and Oracle scans such large chunks of data, but Oracle scans the blocks below the high watermark. Now imagine that if you had just created an empty table and you did a select operation, because the high watermark HWM in the lowest 0 position, no data block would have to be scanned and the scan time would be very short. And if you first insert 10 million data at this time, then delete the 10 million data with the DELETE statement. Since 10 million data is inserted, the high watermark at this time is 10 million data here. When the 10 million data is deleted, the high watermark is still 10 million data, because the DELETE statement does not affect the high watermark line. This time again with the SELECT statement to scan, although this time there is no data in the table, but because the scan is based on the high watermark, so you need to 10 million of data storage space to be scanned once, that is, the time required for this scan and scan 10 million data will take as much time. So sometimes people always say, how my table is not a few data, but still so slow, this time in fact, the mystery is the high water mark here.

There is no way to let the high water mark down, in fact, there is a relatively simple way, that is, using the TRUNCATE statement to delete data. When using the TRUNCATE statement to delete data from a table, it is similar to re-establishing the table, not only deleting the data, but also restoring the HWM to 0. So if you need to empty the table, use the TRUNCATE statement to delete the table when it is possible to use the TRUNCATE statement to delete the data, especially the temporary storage table that has a potentially large amount of data.

In manual segment space management (Manual Segment spaces Management), there is only one HWM in the segment, but the automatic segment space management that is added in Oracle9irelease1 (Automatic Segment Space Management), there is a concept of a low HWM. Why is there a HWM and a low HWM, this is due to the characteristics of automatic section space management. In the method section space management, when the data is inserted, if it is inserted into the new data block, the data block will be automatically formatted to wait for data access. In automatic section space management, when data is inserted into a new block, the data block is not formatted, but the block is formatted the first time it is accessed by the data block. So we need a watermark to indicate which blocks have been formatted. This watermark is called low HWM. Generally speaking, low HWM must be below or equal to HWM.

3.1.1.3 influence

• A full table scan is usually read out until all of the HWM tags belong to the table database block, even if there is no data in the table.

• Even if you have an idle database block below HWM, and you type the APPEND keyword when inserting data, the HWM automatically increases when you insert more than HWM data blocks.

3.1.1.4 Practice

################### #Truncate reduce hwm########################### #分析表emp3 sql> analyze table Emp3 estimate statistics;  Table analyzed. #查看高水位线为blocks =5#blocks column represents the number of database blocks that have been used in the table, that is, the waterline, #USER_TABLES. Blocks indicates the number of database blocks that have been used. #EMPTY_BLOCKS represents a database block that is assigned to the table, but above the waterline, that is, a block of data that has never been used.    Sql> Select blocks, Empty_blocks, num_rows from user_tables where table_name = ' EMP3 '; BLOCKS empty_blocks num_rows--------------------------------5 3 19# View the block size allocated for this segment, BLOCKS=8#DBA _segments. Blocks represents the number of all database blocks allocated to this table sql> select Segment_name, Segment_type, Segment_subtype, blocks from dba_segments where segment_name = ' EMP3 ';            Segment_name segment_type segment_su BLOCKS-----------------------------------------------------EMP3  TABLE ASSM 8 Sql> Select COUNT (*) from Emp3; COUNT (*)----------19sql> Delete from emp3;19 rows deleted. Sql> commit; Commit complete. Sql> Select blocks, Empty_blocks, num_rows from User_tables WHere table_name = ' EMP3 '; BLOCKS empty_blocks num_rows--------------------------------5 3 19sql> Delete from Emp3 ; rows deleted. Sql> commit; Commit complete. Unable to reduce HWM after #delete data sql> select blocks, Empty_blocks, num_rows from user_tables where table_name = ' EMP3    ‘; BLOCKS empty_blocks num_rows--------------------------------5 3 19sql> truncate TABLE E mp3; Table truncated.    Sql> Select blocks, Empty_blocks, num_rows from user_tables where table_name = ' EMP3 '; BLOCKS empty_blocks num_rows--------------------------------5 3 19sql> Analyze table em P3 estimate statistics; Table analyzed. #使用Truncate表可以降低HWMSQL > select blocks, empty_blocks, num_rows from user_tables where table_name = ' EMP3    ‘; BLOCKS empty_blocks num_rows--------------------------------0 8 0 ############### # # # # #Shrink Space reduced hwm########################## #SQL > SELect blocks, empty_blocks, num_rows from user_tables where table_name = ' EMP3 ';  BLOCKS empty_blocks num_rows--------------------------------5 3 19sql> Delete from Emp3 where dep_id = 2;11 rows deleted. Sql> commit; Commit complete.    Sql> Select blocks, Empty_blocks, num_rows from user_tables where table_name = ' EMP3 ';  BLOCKS empty_blocks num_rows--------------------------------5 3 19sql> ALTER TABLE Emp3  Shrink space;alter table Emp3 shrink space*error at line 1:ora-10636:row movement are not enabledsql> ALTER TABLE Emp3 Enable row movement; Table altered. sql> ALTER TABLE Emp3 shrink space; Table altered. Sql> Analyze table Emp3 estimate statistics; Table analyzed.    Sql> Select blocks, Empty_blocks, num_rows from user_tables where table_name = ' EMP3 '; BLOCKS empty_blocks num_rows--------------------------------1 7 8

Oracle Basic Article---HWM