HWM (High Water Mark)

High Water Mark (HWM) is a key element in database maintenance. It is commonly overlooked and forgotten and therefore it has a tremendous (negative) impact on the performance. The High Water mark is like a bookmark in a table that shows the maximum number of blocks used in a lifetime of time (until the object is rebuilt, truncated, or shrunk).

Imagine a table and each row represents a block of data (for the demonstration’s sake). Every time you insert new data, a new block is allocated.

First of all, your table is empty and HWM is set to 0.

Now, we insert 5 records to the ORDER table. After this operation, HWM is set to 5.

We will insert 3 more and the HWM will be set to 8 (because 8 blocks are allocated after this operation)

Now, here comes the trick 🙂 When we DELETE (and let me stress the DELETE word again) last three records the block allocation will remain as you can see below. Even though records are not there, the block allocation will remain the same!!! (HWM = 8) This is a critical roadblock and you can see that deleting a huge amount of data from a table is not recommended !!! More details below.

Now, if when we insert another three records (orders) the HWM will be increased by another 3 (thus HWM = 11, even though only 8 blocks are “occupied”).

As you can see there is a waste of space and especially when you delete a huge amount of data from a table. It is obviously not a good idea to execute such an operation. Well, what else can I do (you might ask)? It depends on what are you after 🙂 If you need to remove more than 30-40% of records in a table – avoid using DELETE command and use CTAS (Create Table As Select) instead.

I saw users many times performing initial load in their scripts. They reload all historical data every single day. I am not going to comment on this kind of approach – I dedicated another article to this subject. It must not necessarily be a problem if they optimize their queries however they commonly use DELETE / INSERT which is the worst case scenario (not to mention DELETE & INSERT /*+APPEND*/ where you multiply the wasted space with each execution) 🙁 Please, never implement this kind of logic because you will waste a stupendous amount of space. If you really need to reload the table everyday use TRUNCATE and INSERT instead (with a hint /+ APPEND */; more about hints in my article Oracle hints).

You can check objects with a wasted space by using the following command

SELECT dts.owner
      ,dts.table_name
      ,dsse.partition_name
      ,dsse.segment_type
      ,dts.blocks blks_used
      ,dts.avg_space
      ,dts.num_rows
      ,dts.avg_row_len
      ,dts.empty_blocks as empty_blks
      ,to_char(dts.avg_row_len * dts.num_rows, '999,999,999') as est_blks
      ,to_char(dsse.blocks, '999,999,999') as alloc_blks
      ,(greatest(dts.blocks, 1) / greatest(dsse.blocks, 1)) * 100 as pct_hwm
      ,dts.num_rows * dts.avg_row_len as data_in_bytes
      ,(dts.num_rows * dts.avg_row_len) / 8192 as data_in_blks
      ,((dts.num_rows * dts.avg_row_len) / 8192) * 1.25 as mod_data_in_blks
      ,(((dts.num_rows * dts.avg_row_len) / 8192) * 1.25) / dsse.blocks as pct_spc_used
FROM dba_tab_statistics dts
--
JOIN dba_segments dsse
ON dts.table_name = dsse.segment_name
--
WHERE 1 = 1    
AND dsse.segment_type = 'TABLE'

Or you can use another way (if you have the privilege)

DECLARE
  CURSOR cu_tables IS
    SELECT owner,
           table_name
    FROM   dba_tables 
    WHERE  1=1
    AND    table_name = Decode(Upper('&&Table_Name'),'ALL',table_name,Upper('&&Table_Name'))
    AND    owner      = Upper('&&Table_Owner') 
    AND    partitioned='NO'
    AND    logging='YES'
    ORDER BY table_name;
 
  op1  NUMBER;
  op2  NUMBER;
  op3  NUMBER;
  op4  NUMBER;
  op5  NUMBER;
  op6  NUMBER;
  op7  NUMBER;
  v_block_sz v$log.blocksize%TYPE;
BEGIN
 
  SELECT blocksize INTO v_block_sz
  FROM v$log WHERE status = 'CURRENT';
 
  Dbms_Output.Disable;
  Dbms_Output.Enable(1000000);
  Dbms_Output.Put_Line('          TABLE_NAME              UNUSED_BLKS      UNUSED_BYTES       TOTAL_BLKS          HWM      ');
  Dbms_Output.Put_Line('------------------------------  ---------------   ---------------   --------------  ---------------');
  FOR cur_rec IN cu_tables LOOP
    dbms_Space.Unused_Space(cur_rec.owner,cur_rec.table_name,'TABLE',op1,op2,op3,op4,op5,op6,op7);
    dbms_Output.Put_Line(RPad(cur_rec.table_name,30,' ') ||
                         LPad(op3,15,' ')                ||
                         LPad(op3 *v_block_sz,15,' ')  ||
                         LPad(op1,15,' ')                ||
                         LPad(Trunc(op1-op3-1),15,' ')); 
  END LOOP;
END;

These are the ways how to reset the HWM:

• TRUNCATE TABLE (most common)
• DBMS_REDEFINITION
• ALTER TABLE … SHRINK
• ALTER TABLE … COALESCE
• EXPORT/IMPORT