HIGH WATER MARK

High-water mark is an indicator or pointer up to which table or index has ever contain data

Let us illustrate this statement as

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

Unused block or extent occupied by the table segment on initial creation.

|___ HWM

After inserting data in the table segment , the HWM moves and point the position up to which the data is in the segment

DATA ß--------------------à

Un-Used Blocks

By inserting more data in the table segment, HWM moves further to point the position up to which the data is in the segment

ß------- DATA-----------------à

Un-Used Blocks

Now let us delete the data and see the pointer of HWM

DATA ß-------à

Empty Blocks

Un-Used Blocks

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

Now the question arises in front of us , 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 is reset now , after truncating data.

Table where lots of deletion or insertion takes place , probably has High HWM. If the HWM is high , it is better to rebuild table segment for performance sake.

Calculate the HWM as follows

HWM % = (HWM BLOCKS - Actual DATA BLOCKS / HWM BLOCKS ) * 100

If the degree of difference is 40% or more , then performing the rebuild to reset the HWM is worthwhile. However , consider the overall database size in terms of the number of rows in the table, the total blocks held by the table, and the frequency of FTS being performed (on that table), prior to deciding whether the difference between HWM blocks and actual data blocks is substantial and whether the table is indeed a candidate for resetting the HWM. In case of partitioned table , you would be dealing HWM at partition level

Calculate empty blocks and total blocks occupied by the segments

DATA

Empty Blocks

Un-Used Blocks

/* Analyze table to show correct result */ Select blocks “Block containing data”, empty_block “Empty blocks” from user_table Where table_name = ; Or Select blocks “Block containing data”,empty_block “Empty blocks” from dba_tables Where table_name = And owner = ;

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. This procedure returns values for USED BLOCKS and TOTAL BLOCK. Calculate the HWM as (TOTAL_BLOCKS – USED BLOCKS).

Also I want to point out that people confused about setting of HWM through ALTER TABLE DEALLOCATE UNUSED clause. This clause only free unused space above the high water mark but can not reset HWM position.

Hope this article helped you to understand the concept of HWM.

HAPPY LEARINING !