Home » Oracle » High Water mark in Oracle database

High Water mark in Oracle database

high water mark in oracle table

High Water Mark of Oracle table

High water mark in oracle is a term used in oracle table to show the blocks  which has been ever used in the life cycle of the oracle table. So basically if the oracle table block is flat structure, it is right most block in the structure.

This is used by Oracle process to read the data in the table in Full table scan.

For Manual Segment space Management, HWM is definite but for Automatic Segment space management, oracle maintained an HWM and a low HWM. Maintain two HWM as rows are not formatted like MSSM on increasing the High water Mark

When you delete the rows from table, the blocks below HWM may becomes empty but high water mark will stay as it is. Suppose you load the table with 1 million rows .Now you will have suppose the high water mark as 1 GB. Now if you delete all the 1 millions rows, then even the high water mark will be remain same as 1 GB. The only way to reduce the HWM is to rebuild the able or use truncate table. Oracle Truncate table reduce the High water mark

The following process may be used at any time to determine the  high water mark of the oracle table in an non ASSM tablespace (Manual segment space management)

set serveroutput on
declare
TOTAL_BLOCKS number;
TOTAL_BYTES number;
UNUSED_BLOCKS number;
UNUSED_BYTES number;
LAST_USED_EXTENT_FILE_ID number;
LAST_USED_EXTENT_BLOCK_ID number;
LAST_USED_BLOCK number;
HWM number;
FREE_BLOCKS  number; 

begin
dbms_space.unused_space(
'&table_owner',
'&table_name',
'TABLE',
TOTAL_BLOCKS,
TOTAL_BYTES,
UNUSED_BLOCKS,
UNUSED_BYTES,
LAST_USED_EXTENT_FILE_ID,
LAST_USED_EXTENT_BLOCK_ID,
LAST_USED_BLOCK);

dbms_space.free_blocks
    ( '&table_owner',
      '&table_name',
      'TABLE',
       0,
       FREE_BLOCKS);

dbms_output.put_line('TOTAL_BLOCKS = '||TOTAL_BLOCKS);
dbms_output.put_line('UNUSED_BLOCKS = '||UNUSED_BLOCKS);
HWM= TOTAL_BLOCKS  - UNUSED_BLOCKS -1;
dbms_output.put_line('HIGH WATER MARK = '||HWM); 
 dbms_output.put_line('FREE BLOCKS = '|| FREE_BLOCKS);  
end;
/

Once you determined the high water mark , you may want to check for the reason for this . If you are doing full table scan and it is showing performance issue.you can think about rebuilding the table to fix it. If we dont have any full table scan, then it may not give any benefit.

See also  Move SQL Profiles from One Database to Another in Oracle

The following procedure may be used at any time to see the block allocations for a table stored in an ASSM tablespace(Automatic segment space management)

set serveroutput on
declare
v_unformatted_blocks number;
v_unformatted_bytes number;
v_fs1_blocks number;
v_fs1_bytes number;
v_fs2_blocks number;
v_fs2_bytes number;
v_fs3_blocks number;
v_fs3_bytes number;
v_fs4_blocks number;
v_fs4_bytes number;
v_full_blocks number;
v_full_bytes number;

begin
dbms_space.space_usage (
'table_owner',
'table_name',
'TABLE',
v_unformatted_blocks,
v_unformatted_bytes,
v_fs1_blocks,
v_fs1_bytes,
v_fs2_blocks,
v_fs2_bytes,
v_fs3_blocks,
v_fs3_bytes,
v_fs4_blocks,
v_fs4_bytes,
v_full_blocks,
v_full_bytes);

dbms_output.put_line('Unformatted Blocks                       = '||v_unformatted_blocks);
dbms_output.put_line('Blocks with 00-25% free space   = '||v_fs1_blocks);
dbms_output.put_line('Blocks with 26-50% free space   = '||v_fs2_blocks);
dbms_output.put_line('Blocks with 51-75% free space   = '||v_fs3_blocks);
dbms_output.put_line('Blocks with 76-100% free space = '||v_fs4_blocks);
dbms_output.put_line('Full Blocks                                        = '||v_full_blocks);

end;
/

The above procedure was for Table, For table partition , we can use similar queries also

set serveroutput on

declare
v_unformatted_blocks number;
v_unformatted_bytes number;
v_fs1_blocks number;
v_fs1_bytes number;
v_fs2_blocks number;
v_fs2_bytes number;
v_fs3_blocks number;
v_fs3_bytes number;
v_fs4_blocks number;
v_fs4_bytes number;
v_full_blocks number;
v_full_bytes number;

begin
dbms_space.space_usage (
'table_owner',
'table_name',
'TABLE PARTITION',
v_unformatted_blocks,
v_unformatted_bytes,
v_fs1_blocks,
v_fs1_bytes,
v_fs2_blocks,
v_fs2_bytes,
v_fs3_blocks,
v_fs3_bytes,
v_fs4_blocks,
v_fs4_bytes,
v_full_blocks,
v_full_bytes,

'PARTITION_NAME');

dbms_output.put_line('Unformatted Blocks                       = '||v_unformatted_blocks);
dbms_output.put_line('Blocks with 00-25% free space   = '||v_fs1_blocks);
dbms_output.put_line('Blocks with 26-50% free space   = '||v_fs2_blocks);
dbms_output.put_line('Blocks with 51-75% free space   = '||v_fs3_blocks);
dbms_output.put_line('Blocks with 76-100% free space = '||v_fs4_blocks);
dbms_output.put_line('Full Blocks                                        = '||v_full_blocks);

end;
/

Once you know the HWM or free space, you may decide to shrink or rebuild the table. Here is the post which talks about how to rebuild the table depending on the condition in the database. It is not required many times to rebuild the table for the sake of this reason as it may not give any performance benefit and it will again be fragmented after some time. It is recommended to test the rebuild in Test before moving to Production.

Read more about package used in this article at

DBMS_SPACE

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top