High Water Mark in Oracle

Many a time in performance issues High Water Mark is termed as a culprit. So what is this “High Water Mark” (HWM)? Let us take a look.
If you have been to the shores, you would have noticed junk (mostly sea weeds) making a sort of line along the length of the shore. This is the highest level in the shore to which sea water has ever risen. Similarly, in an Oracle table, High Water Mark can be termed as the highest level to which data was ever occupied in a table. Given below is a simple pictorial representation of the same.
Assume that you have created a table “example”, and have not inserted any rows. At this moment of time, the HWM will be the first extent.
Now let’s assume that you have inserted 10000 rows to this table. The HWM is incremented for each block used. The current HWM is depicted in red.
If you delete say 7000 records from the examples table, the amount of blocks used will come down, but the HWM will be at the same position i.e. the highest level to which data was ever present.
The following query will give you the empty blocks below HWM for a table.
(SELECT BLOCKS
   FROM DBA_TABLES
  WHERE TABLE_NAME = ‘:table_name’
)
MINUS
(SELECT COUNT( DISTINCT dbms_rowid.ROWID_RELATIVE_FNO (rowid)||'_'|| dbms_rowid.ROWID_BLOCK_NUMBER(rowid))
   FROM :table_name
);
A very high value for HWM can be a performance issue in many cases. A custom program written by the IT team of my client was taking days to finish. The performance went from bad to worse day by day. I was asked to fix this. The execution plan showed me that a Full Table Scan (FTS) on a custom table was the bottleneck. When I checked the table for stats and indexes present, I was surprised to see that it contained 0 rows! I immediately checked the HWM and it was a very unrealistic value. On checking the code for the program, I found that the developer inserted data into this table and then deleted them by the end of the execution. However, the HWM for this table would have kept on increasing with every run of this program.
The above program took hours to complete because when Oracle does a FTS on any table, it will scan from the first block up to the HWM, so that it goes thro all the blocks which could potentially hold data.
With each run of the program, the HWM was getting higher and higher and oracle had to scan that many blocks. This issue was solved by replacing DELETE at the end of the program with TRUNCATE.

Oracle resets all the stats related to table when the table is truncated. The indexes on this table too are affected. In this process, the HWM is also reset. Another option to reclaim the empty space below HWM is to MOVE the table using “ALTER TABLE table_name MOVE;


If you are in 10g, you could use the shrink command to reclaim the unused space
ALTER TABLE table_name SHRINK SPAECE
I have not discussed in detail how HWM can be cured not\r have I dealt with the high HWM and low HWM. I will be covering these in another post shortly

-J