Detailed description of oracle high water level line and oracle water level line

Detailed description of oracle high water level line and oracle water level line

1. What is a High Water Mark )?

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. HWM usually increases by five data blocks at a time. In principle, HWM only increases and does not shrink. Even if all the data in the table is deleted, HWM is the original value, this makes HWM very similar to the historical highest water level of a reservoir, which is the original meaning of HWM. Of course, it cannot be said that a reservoir has no water, but the historical highest water level of the reservoir is 0. However, if we use the truncate command on the table, the HWM of the table will be reset to 0.

Ii. operations on the HWM database have the following impacts:

A) Full table scan usually reads all the database blocks in the table until the HWM mark, even if the table does not have any data.

B) 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.

3. How do I know the HWM of a table?

A) analyze the table first:

Analyze table <tablename> ESTIMATE/compute statistics;

B) SELECT blocks, empty_blocks, num_rows

FROM user_tables

WHERE table_name = <tablename>;

Note:

The BLOCKS column indicates the number of database BLOCKS used in the table, that is, the waterline.

EMPTY_BLOCKS indicates the database block that is allocated to the table but above the waterline, that is, the data block that has never been used.

Let's take a BIG_EMP1 table with 28672 rows as an example:

1) SQL> SELECT segment_name, segment_type, blocks

FROM dba_segments

WHERE segment_name = 'Big _ emp1 ';

SEGMENT_NAME SEGMENT_TYPE BLOCKS

----------------------------------------

BIG_EMP1 TABLE 1024

1 row selected.

2) SQL> ANALYZE TABLE big_emp1 ESTIMATE STATISTICS;

Statement processed.

3) SQL> SELECT table_name, num_rows, blocks, empty_blocks

FROM user_tables

WHERE table_name = 'Big _ emp1 ';

TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS

--------------------------------------

BIG_EMP1 28672 700 323

1 row selected.

Note:

BLOCKS + EMPTY_BLOCKS (700 + 323 = 1023) has one database block less than DBA_SEGMENTS.BLOCKS because one database block is reserved as the segment header. DBA_SEGMENTS.BLOCKS indicates the number of all database blocks allocated to this table. USER_TABLES.BLOCKS indicates the number of used database blocks.

4) SQL> SELECT COUNT (DISTINCT

DBMS_ROWID.ROWID_BLOCK_NUMBER (rowid) |

DBMS_ROWID.ROWID_RELATIVE_FNO (rowid) "Used"

FROM big_emp1;

Used

----------

700

1 row selected.

5) SQL> delete from big_emp1;

28672 rows processed.

6) SQL> commit;

Statement processed.

 

7) SQL> ANALYZE TABLE big_emp1 ESTIMATE STATISTICS;

Statement processed.

 

8) SQL> SELECT table_name, num_rows, blocks, empty_blocks

FROM user_tables

WHERE table_name = 'Big _ emp1 ';

TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS

----------------------------------

BIG_EMP1 0 700 323

1 row selected.

 

9) SQL> SELECT COUNT (DISTINCT

DBMS_ROWID.ROWID_BLOCK_NUMBER (rowid) |

DBMS_ROWID.ROWID_RELATIVE_FNO (rowid) "Used"

FROM big_emp1;

Used

----------

0 ---- This table name does not contain any database block to accommodate data, that is, the table does not have data

1 row selected.

10) SQL> TRUNCATE TABLE big_emp1;

Statement processed.

11) SQL> ANALYZE TABLE big_emp1 ESTIMATE STATISTICS;

Statement processed.

12) SQL> SELECT table_name, num_rows, blocks, empty_blocks

2> FROM user_tables

3> WHERE table_name = 'Big _ emp1 ';

TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS

--------------------------------------

BIG_EMP1 0 0 511

1 row selected.

13) SQL> SELECT segment_name, segment_type, blocks

FROM dba_segments

WHERE segment_name = 'Big _ emp1 ';

SEGMENT_NAME SEGMENT_TYPE BLOCKS

-------------------------------

BIG_EMP1 TABLE 512

1 row selected.

Note:

The TRUNCATE command recycles the free space generated by the delete command. Note that the space allocated to the table is reduced from the original 1024 blocks to 512 blocks.

To retain the free space generated by the delete command, you can use truncate table big_emp1 reuse storage.

After using this command, the table will still be the first 1024 blocks.

4. HWM in the Oracle table segment

In the storage of Oracle data, you can think of the storage space as a reservoir, and the data as water in the reservoir. There is a line of water in the reservoir called a waterline. in Oracle, this line is called a High-warter mark (HWM ). When a database table is created, there is no data, so the watermark line is empty at this time, that is, HWM is the lowest value. When the data is inserted, the High-level line will rise, but here there is also a feature, that is, if you use the delete statement to delete the data, although the data is deleted, however, the high water level has not been lowered, but it is still as high as before you deleted the data. That is to say, this high water level line will only rise in daily addition, deletion operations, and will not fall.

The following describes the features of Select statements in Oracle. The Select statement scans the data in the table once, but how many data storage blocks are scanned does not mean that Oracle scans such a large data block as to how much data is in the database, instead, Oracle scans data blocks below the high waterline. Now imagine that if you have just created an empty table and performed a Select operation, the HWM of the high watermark line is at the lowest position, therefore, no data blocks need to be scanned, and the scanning time is very short. If at this time, you first Insert 10 million data records, and then use the delete statement to delete the 10 million data records. Because 10 million pieces of data are inserted, the high water level line is here 10 million pieces of data. Later, when the 10 million data records were deleted, because the delete statement does not affect the high waterline, the high waterline is still in the 10 million data records. At this time, the select statement is used again for scanning. Although there is no data in the table at this time, the scanning is based on the high water level, therefore, you need to scan the storage space of 10 million pieces of data once. That is to say, the time required for this scan is the same as that required for scanning 10 million pieces of data. So sometimes some people often say that my table does not have a few data records, but it is still so slow. In fact, the mystery is the high water level here.

Is there a way to reduce the high water level line? In fact, there is a simple method, that is, using the TRUNCATE statement to delete data. When the TRUNCATE statement is used to delete the data of a table, it is similar to re-establishing the table, not only deleting the data, but also clearing the HWM and restoring it to 0. Therefore, if you need to clear the table, you can use the TRUNCATE statement to delete the table when using the TRUNCATE statement to delete the data, especially the temporary storage table with a large amount of data.

In Manual Segment Space Management, there is only one HWM in the Segment, but in the Automatic Segment Space Management added in Oracle9iRelease1, there is a low HWM concept. Why is there another low HWM with HWM? This is caused by the feature of automatic segment space management. When data is inserted into a new data block, the data block is automatically formatted and waiting for data access. In automatic segment space management, after data is inserted into a new data block, the data block is not formatted. Instead, the block is formatted when the data block is accessed for the first time. So we need another waterline to mark the formatted block. This waterline is called low HWM. In general, low HWM must be lower than or equal to HWM.

V. Modify the high watermark line of an ORACLE table

In ORACLE, deleting a table does not reduce the high watermark line of the table. The full table scan always reads all the blocks lower than the high watermark in a segment (extent. If the high watermark mark is not lowered after the delete operation is executed, the query statement performance will be poor. The following methods can reduce the mark of the high waterline.

1. Execute the alter table table_name move command for table reconstruction;

(Online transfer tablespace alter table... Move tablespace... Alter table... MOVE is followed by no parameter. If it is not followed by the parameter table, it is still in the original tablespace. Remember to recreate the index after move. If you want to add data to the table later, you do not need to move it. Instead, you only need to use the table to release the space. Other tables or segments cannot use the space)

2. Execute alter table table_name shrink space. Note that this command is a new feature for Oracle 10 Gb. Before executing this command, you must allow rows to move alter table table_name enable row movement;

3. copy the data to the temporary table t, drop the original table, and rename the temporary table t as the original table

4. emp/imp

5. alter table table_name deallocate unused

6. truncate as much as possible