Scribd
Upload
581 views3 pages

Oracle High-Water Mark Explained

The document discusses the concept of high-water mark (HWM) in Oracle databases. It explains that HWM is a pointer that indicates the furthest position of data in a table or index segment. It provides examples to illustrate how the HWM moves with data insertion and deletion. The key points are: HWM does not move on data deletion, leading to full table scans reading unused blocks; truncating a table is the only way to reset the HWM; and rebuilding a table may be worthwhile if the difference between actual and HWM blocks is over 40%. Formulas are given to calculate the HWM percentage.

Uploaded by

Mohd Fauzi
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
581 views3 pages

Oracle High-Water Mark Explained

The document discusses the concept of high-water mark (HWM) in Oracle databases. It explains that HWM is a pointer that indicates the furthest position of data in a table or index segment. It provides examples to illustrate how the HWM moves with data insertion and deletion. The key points are: HWM does not move on data deletion, leading to full table scans reading unused blocks; truncating a table is the only way to reset the HWM; and rebuilding a table may be worthwhile if the difference between actual and HWM blocks is over 40%. Formulas are given to calculate the HWM percentage.

Uploaded by

Mohd Fauzi
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
You are on page 1/ 3

Sameer Wadhwa

[email protected]

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

Venkat Devraj (Author of ORACLE 24/7) suggest that , 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 = <Table_name>;
Or
Select blocks Block containing data,empty_block Empty
blocks from dba_tables
Where table_name = <Table_name>
And owner = <Owner_Name>;

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.

Reference
Oracle Concept Manual
Oracle 24/7 by Venkat S Devraj

Sameer Wadhwa
[email protected]

(Copyright 2001 Sameer Wadhwa ( All right reserved)

You might also like