The function of the High Water Mark in Oracle

Recently a potential client in San Diego quizzed me on the high water mark and performance tuning in Oracle.

Basically the high water mark (HWM) has a function that comes into play with tables that have heavy insert, update and delete activity. Every time data is changed in a table, the HWM moves to a setting in each table that shows how much free space is left in terms of blocks used and free in the segment object. What this means is that say I have a segment object type table A that has 100,000 rows inserted and then I perform a delete operation to delete 10,000 rows from that table, Oracle will move the HWM to a value dependent on the PCT_FREE and PCT_USED values based on the free list available in that table.

Things get tricky when we go to Oracle 9i which added the option to create automatic segment space management (ASSM) functionality with the table. Prior to Oracle 9i, all tables were manual segment managed which meant that a DBA had to play around with various parameters for PCT_FREE, PCT_USED, etc. to get the balance right for performance optimization of that table. ASSM allows bitmaps to manage that table and improves performance and management is far easier than tweaking all of the previous parameters.

Which leads us to the main issue of HWM and Oracle performance. When a DELETE operation is taking a long time, that is because the HWM value has not been reset. To clean this issue up and improve performance, the HWM has to be reset.

Now there are a couple of ways to do this: one is to perform a TRUNCATE operation on the table which will reset the HWM values and run a lot faster than a DELETE operation.

A second option is to reorganize the table using either export or import for Oracle 8i/9i releases or to use the data pump (expdp/impdp) utility available with Oracle 10g and Oracle 11g to reset the HWM values.

Lutz Hartman has an excellent discussion on the new high watermark settings for Oracle 10g and 11g:

To quote Oracle ACE Lutz Hartman on the concept of the HWM for Oracle:

"Starting with ORACLE 10gR1 we can use a new feature for adjusting the high watermark, it is called segment shrinking and is only possible for segments which use ASSM, in other words, which are located in tablespaces which use Automatic Segement Space Management."

Which brings us to a new discussion with terms that there is now a HHWM and LHWM values for segment objects for tables and other objects.

To further enhance performance, in terms of the values for tables, it is now possible with Oracle 10g and 11g to shrink segments in a table.

The nice thing about Oracle 10g and 11g is that we are not having to do the export and import tables and move them around to reset the HWM for performance reasons we can use the "ALTER TABLE ... SHRINK " commands after we have enabled row movement for the table and all of this can be done online with no downtime for the table!