High Water Mark (HWM)

High Water Mark (HWM)

Oracle uses the high water mark to identify the highest amount of space used by a particular segment.

It acts as the boundary between used and unused space. As the amount of data grows due to row inserts

and updates, the segment's high water mark grows accordingly.

The HIGH WATER MARK is set at the beginning of the segment when the table is created. 

Deleting rows will never reset the HWM even if all the rows are deleted from the table. 

Only the TRUNCATE command will reset the HWM.

To reduce the high water mark export / data pump the table, drop it and import it back in again.

Suppose we create an empty table, the high-water mark would be at the beginning of the table segment

Unused space

 HWM

When you insert rows into the table the high watermark will be bumped up step by step. This is done by 

the server process which makes the inserts.

Used DATA

Un used Space

                                                              HWM                                                            

Let’s assume that we have filled a table with 100’000 rows. And let’s assume that we deleted 50’000 rows 

afterwards.        

                    

Used data

Empty blocks

Un used space

                           <--------------------------------------------------------------->HWM       

                                 Full table scan

  As you seen above by deleting the data, HWM does not move.  The main disadvantage of this is 

that oracle always read the blocks up to high water mark in case of full table scan.  You may have

ever notice that doing a count (*) on empty table, takes time to show you 0 rows.  The reason for delay is 

setting of HWM at higher position.

NOTE:  Whenever optimizer takes full table scan, it scans all the blocks below HWM. This would degrade

the performance and slowdown the Full table scan process. To avoid this, we need to shrink the table to

reset the HWM.

So, now how we set the high-water mark at lower position?

The only way to set the HWM is to truncate a table. Let us see how truncate set the HWM.

No data in the segment

                                 

  HWM

HWM is reset now, after truncating data.   

HWMBLOCKS = “Blocks Containing data” + “Empty blocks”          HWMBLOCKS – “Blocks containing data” HWM % = (----------------------------------)  * 100                   HWMBLOCKS

You can also use DBMS_SPACE.UNUSED_SPACE procedure to determine HWM.

High Watermark = Total blocks - Unused blocks – 1

SQL> select blocks from dba_segments where owner=upper('HR') and segment_name = upper('JOBS');

SQL> analyze table hr.jobs estimate statistics;

SQL> select empty_blocks from dba_tables where owner=upper('HR') and table_name = upper('jobs');

Ways for Resetting HWM………..

Option 1 Alter table  tab_name  move tablespace.

This option requires rebuilding the indexes. Since the index will be invalid/unusable after running

the above command. The downside is, rebuilding the index is additional overhead when we use

this option. Also users cannot use the application or reports while rebuilding the index.

Option 2

1. Export the data

2. Truncate the table

3. Import the table

4. Analyze the table

Option 3  

1. Copy the table data

2. Truncate the original table

3. Insert back.

  

    Option 4    Use DBMS_REDEFINITION package to copy the table

People confused about setting of HWM through ALTER TABLE DEALLOCATE UNUSED clause. 

This clause only frees unused space above the high water mark but cannot reset HWM position.

Starting with ORACLE 10gR1 we can use a new feature for adjusting the high watermark,

it is called segment shrinking and is only possible for segments which use ASSM, in other words, 

which are located in tablespaces which use Automatic Segement Space Management.