Table high water mark tips

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

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 table:

select
   a.tablespace_name,
   a.file_name,
   ceil( (nvl(hwm,1)*8192)/1024/1024 ) "Mo"
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
       from dba_extents
       group by file_id
     ) b
where a.file_id = b.file_id(+)
order by tablespace_name, file_name;

This example script will query the dba_tables and dba_segments to show the table highwater mark:

set verify off
column owner format a10
column alcblks heading 'Allocated|Blocks' just c
column usdblks heading 'Used|Blocks'      just c
column hgwtr heading 'High|Water'         just c
break on owner skip page
 
select
    a.owner,
    a.table_name,
    b.blocks                        alcblks,
    a.blocks                        usdblks,
    (b.blocks-a.empty_blocks-1)     hgwtr
from
    dba_tables a,
    dba_segments b
where
    a.table_name=b.segment_name
    and a.owner=b.owner
    and a.owner not in('SYS','SYSTEM')
    and a.blocks <> (b.blocks-a.empty_blocks-1)
    and a.owner like upper('&owner')||'%'
    and 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