Shulou(Shulou.com)06/01 Report--

HWM: high water mark

The dividing line between the available space and the used space marks the use of the segment of space.

All oracle segments (segments, here, for ease of understanding, it is recommended to use segment as a synonym for tables) have an upper limit to hold data within the segment, which we call "high water mark" or HWM. The HWM is a tag that indicates how many unused blocks have been allocated to the segment. HWM usually increases by 5 data blocks at a time, in principle HWM will only increase, not shrink, even if all the data in the table are deleted, HWM is still the original value. Because of this feature, HWM is very much like the historical highest water level of a reservoir, which is the original meaning of HWM. Of course, you can't say that a reservoir is out of water, that is to say, the historical highest water level of the reservoir is 0. But if we use the truncate command on the table, the table's HWM will be reset to 0.

The HWM is first located in the first block in the newly created table. Over time, HWM rises as more data is inserted into the table and more blocks are used.

If we delete some rows in the table, there may be many blocks that no longer contain data, but they remain under HWM until the object is rebuilt or truncated (truncate) or shrunk (shrink).

When scanning a segment, oracle scans all the fast under the HWM, even if it does not contain data, if most of the blocks under the HWM are empty blocks, this will affect the performance of the scan.

Let's say that in a table with 100000 rows, you execute select count (*) from tab; under delete from tab, delete all rows in the table, and execute select count (*) from tab; again. The result shows that there are only 0 rows, but it takes as much time to execute the statement as it takes to count out 100000 rows.

The operation of the HWM database has the following effects:

A) A full table scan usually reads out everything marked by HWM that belongs to the table database block, even if there is no data in the table.

B) even if there are free database blocks below HWM, and typing uses the append keyword when inserting data, data blocks above HWM are used when inserting, and HWM will automatically grow. When data loaded using the direct path mechanism is deleted, it is possible to waste a lot of unused space in the table.

Track and detect the space below the high water mark:

Using this simple test, you can also check if there is unused space below the high water mark.

1. Set autot trace

two。 Execute query commands for full table scan

3. Compare the processed line number with the Ihand O

If the processed row number is low, but the Imax O logical label is high, some data blocks may be wasted in the space below the high watermark mark.

Use the DBMS_SPACE package to also check the space below the high water mark (check it yourself)

View the partitioned view of the data dictionary:

Viewing the DBA_extents view can also check the table for high water mark problems.

If the table has a large number of extents but no data, a large amount of data is deleted from the table:

Select count (*) from user_extents where segment_name='INV'

Then check the line number in the table:

Select count (*) from inv

Ways to lower the mark of the high water mark:

1. Truncate

2. Alter table... shrink space

3. Alter table... move

1. Shrinkage table:

To adjust the high water mark, you must first turn on the start movement function for the table.

Then you can use the alter table... shrink space statement.

The table space in which the table resides must have automatic segment space management enabled.

Check if it is in automatic segment management mode:

Select tablespace_name,segment_space_management from dba_tablespaces

Start the mobile function for the table:

Alter table inv enable row movement

Shrink the space used by the table:

Alter table inv shrink space

Alter table inv shrink space cascade

You can also shrink the space used by the index segment through the cascade clause

two。 Move the table:

Moving a table means rebuilding the table in the current tablespace or creating the table in another tablespace.

If there is a disk storage problem in the current tablespace, or if you need to lower the high water mark of the table, you need to move the table.

Alter table inv move tablespace users

Query user_tables view can be validated

Select table_name,tablespace_name from user_tables where table_name = 'INV'

Performing alter table... Oracle does not allow the execution of move statements when DML statements are made.

You can also set the nologging feature when you move the table:

Alter table inv move tablespace users nologging

Moving the table through the nologging clause can reduce a large number of redo logs.

Cons: if the expiration time occurs immediately after the table is moved (so there is no backup), the contents of the table cannot be restored.

If the table contains critical data, you should not use the logging clause when moving the table.

After moving the table, the rowid changes, and all indexes contain incorrect information.

The index needs to be rebuilt manually, alter index. The rebuild command.

Rowid is shoddy and is not stored in the database. When you query it, Oracle will calculate its value.

3. Temporary watch

CREATE TABLE table_name_1 AS SELECT * FROM table_name

TRUNCATE TABLE table_name

INSERT INTO table_name SELECT * FROM table_name_1

Or delete the source table and rename the new table created by CTS.

4. Data pump

Export and import the data.

Check method:

Through the analysis of the table block before and after the deletion of data occupation changes.

SELECT num_rows, blocks, empty_blocks FROM dba_tables WHERE owner='SDBMGR' AND table_name = 'TABLE_NAME'

Test:

How do I know the HWM of a table?

A) first analyze the table:

ANALYZE TABLE ESTIMATE/COMPUTE STATISTICS

B) SELECT blocks, empty_blocks, num_rows

FROM user_tables

WHERE table_name =

Description:

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

EMPTY_BLOCKS represents the database block assigned 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 1 less database block than DBA_SEGMENTS.BLOCKS because one database block is reserved for segment header. DBA_SEGMENTS.BLOCKS represents the number of all database blocks assigned to this table. USER_TABLES.BLOCKS indicates the number of database blocks that have been used.

4) SQL > SELECT COUNT (DISTINCT

DBMS_ROWID.ROWID_BLOCK_NUMBER (rowid) | |

DBMS_ROWID.ROWID_RELATIVE_FNO (rowid)) "Used"

FROM big_emp1

Used

-

seven hundred

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 blocks, that is, there is no data in the table

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 reclaims the free space generated by the delete command, and notice that the space allocated by the table is reduced from 1024 to 512.

To preserve the free space generated by the delete command, you can use TRUNCATE TABLE big_emp1 REUSE STORAGE. Exe.

After using this command, the table will still be the original 1024 yuan.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.