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)
- ALTER TABLE … SHRINK
- ALTER TABLE … COALESCE