Question: Can you please
explain the concept of a table high water mark (HWM)?
I understand that the data is always below the high water
mark. Can you show a script to display the high water
mark for a table?
Answer: The high water mark (HWM) for
an Oracle table is a construct that shows the table at its
greatest size. Just as a lake has a high-water mark
after a draught, an Oracle table has a high water mark that
shows the greatest size of the table, the point at which it
consumed the most extents.
Also see this great script to
display all high water marks.
As a table undergoes deletes and updates, rows shrink and
table data blocks become empty. For performance
reasons, Oracle keeps the high water mark for a table rather
than re-calculate the high water mark after blocks at the
"end" of the table (the last extent) becomes empty.
For example assume that you have a million row table that
takes 30 seconds to read. After deleting 900,000 rows,
a full scan on the table will still take 30 seconds.
This is because the table high water mark is not re-set
after delete operations.
The issue with the high water mark is that full-table
scans will always read up to the high water mark, even
thought Oracle may be reading through many empty blocks that
were allocated to the table, used for rows, and then
As such, there are no easy SQL scripts that will reveal
the high water mark for an Oracle table, but you can assume
that it is the last extent that was allocated to the table
for estimation purposes.
Here is a simple query to find the high water mark for a
from dba_data_files a,
file_id, max(block_id+blocks-1) hwm
group by file_id
where a.file_id = b.file_id(+)
order by tablespace_name,
This example script will query the dba_tables and
dba_segments to show the table highwater mark:
set verify off
column alcblks heading 'Allocated|Blocks' just
column usdblks heading 'Used|Blocks'
column hgwtr heading 'High|Water'
break on owner skip page
and a.owner not
and a.blocks <>
a.owner like upper('&owner')||'%'
a.table_name like upper('&table_name')||'%'
order by 1,2;
Please see these other scripts
to display the high water mark for a table.
The remedy for a too-high high water mark (e.g. a
fragmented table will lots of empty blocks) is to reorganize
the table with Data Pump (expdp and impdp), the
dbms_redefinition utility, or with the table shrink or
coalesce Also see my notes on
method for lowering a table high water mark.
Also see my notes on
sessions high water mark
Oracle Training from Don Burleson
The best on site
training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!
Burleson is the American Team
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
and include the URL for the page.
Copyright © 1996 - 2020
All rights reserved by
is the registered trademark of Oracle Corporation.