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. But when you delete the Rows then HWM cannot come down automatically, extra steps needs to done to bring down HWM.

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. 

To reduce the high water mark:
a. Take export of table and re-import back
b. Truncate the table (only if you want to remove data/rows completely)

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

X	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/Rows

X

Un used Space

                                                               HWM                                                            

Now if we insert a table with 10,000 rows. And let’s assume that we deleted 5,000 rows later.        

                    

Used data

Empty blocks

X

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 1Alter table  tab_name  move tablespace

This option requires rebuilding of indexes. Since the index will be invalid/unusable after running the above command. Also users cannot use the application or reports while rebuilding the index. Make sure you stop Goldengate if this table is in extract process otherwise it will go ABEND (because of Unusable indexes)

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 (redefining the Table structure)

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.

To determine the exact number of blocks that contain data i.e. space used by table below the high water mark, query rowid and get the first and last used block from the rowid.

SQL> select count (distinct dbms_rowid.rowid_block_number(rowid)) "used blocks" from table_name;

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.

How to View High Water Mark - Step-by-Step Instructions (Doc ID 262353.1)