High Water Mark - Space Allocation and Release

Oracle uses the high water mark to identify the highest amount of space used by a particular segment. It acts as the boundary between used and unused space/blocks. As the amount of data grows due to row inserts and updates, the segment's high water mark grows accordingly. But when you delete the Rows then HWM cannot come down automatically, extra steps needs to done to bring down HWM. Deleting rows will never reset the HWM even if all the rows are deleted from the table.

• The high water mark level is just a line separate the used blocks and free blocks.
• The blocks above the HWM level is free blocks, they are ready to use.
• The blocks below the HWM level is used blocks, they are already used.

First let's have a look at the question when space is allocated

When you create a table at least one extent (contiguous blocks) is allocated to the table

- If you have specified MINEXTENTS the number of MINEXTENTS extents will be allocated immediately to the table

- If you have not specified MINEXTENTS then exactly one extent will be allocated.

Immediately after creation of the segment (table) the high watermark will be at the first block of the first extent as long as there are no inserts made.

Suppose we create an empty table, the high-water mark would be at the beginning of the table segment

X	Unused space

HWM

When you insert rows into the table the high watermark will be bumped up step by step. This is done by the server process which makes the inserts.

Used DATA/Rows

X

Un used Space

                                                               HWM                                                            

Now let us take a look at when space is released again from a segment like a table or index: Now if we insert a table with 10,000 rows. And let’s assume that we deleted 5,000 rows later.        

Used data

Empty blocks

X

Un used space

                           <--------------------------------------------------------------->HWM       

                                 Full table scan

In this case the high watermark will have reached the level of 10,000 and will have stayed there. Which means that we have empty blocks below the high watermark now.

Oracle has a good reason this: it might occur that you delete rows and immediately this you insert rows into the same table. In this case it is good that the space was not released with the deletes, because it had to be get reallocate again for the following inserts, which would mean permanent changes to the data dictionary

(=> dba_free_space, dba_extents, dba_segements) .

Furthermore the physical addresses of the deleted row get recycled by new rows.

  As you seen above by deleting the data, HWM does not move.  The main disadvantage of this is that oracle always read the blocks up to high water mark in case of full table scan.  You may have ever notice that doing a count (*) on empty table, takes time to show you 0 rows.  The reason for delay is setting of HWM at higher position.

NOTE:  Whenever optimizer takes full table scan, it scans all the blocks below HWM. This would degrade the performance and slowdown the Full table scan process. To avoid this, we need to shrink the table to reset the HWM.

These empty blocks below the high watermark can get annoying in a number of situations because they are not used by DIRECT LOADs and DIRECT PATH LOADs:

1. Serial direct load:

INSERT /*+ APPEND */ 

INTO hr.employees 

NOLOGGING

SELECT * 

FROM oe.emps;

2. Parallel direct load:

ALTER SESSION ENABLE PARALLEL DML;

INSERT /*+PARALLLEL(hr.employees,2)

INTO hr.employees 

NOLOGGING

SELECT * 

FROM oe.emps;

3. Direct path loads:

sqlldr hr/hr control=lcaselutz.ctl ... direct=y (default is direct=n)

All the above actions case that the SGA is not used for the inserts but the PGA. There will be temporary segments filled and dumped into newly formatted blocks above the high watermark.

So we might want to get high watermark down before we load data into the table in order to use the free empty blocks for the loading.

Release Unused Space From Table

There are a number of possible options which are already available before Oracle 10g:

• What we always could do is export and import the segment. After an import the table will have only one extent. The rows will have new physical addresses and the high watermark will be adjusted.
• Another option would be to TRUNCATE the table. With this we would loose all rows which are in the table. So we cannot use this if we want to keep existing records.

With Oracle 9i another possibilty was implemented:

ALTER TABLE emp MOVE TABLESPACE users;

This statement will also cause that

- The rows will have new physical addresses and

- The high watermark will be adjusted.

But for this:

- We need a full (exclusive) table lock

- The indexes will be left with the status unusable (because they contain the old rowids) and must be rebuilt.

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 Segment Space Management. In such a tablespace a table does not really have a High watermark. It uses two watermarks instead:

- The High High Watermark referred to as HHWM, above which alle blocks ar unformatted.

- The Low High Watermark referred to as LHWM below which all blocks are formatted.

We now can have unformatted blocks in the middle of a segment!

ASSM was introduced in Oracle 9iR2 and it was made the default for tablespaces in Oracle 10gR2.

With the table shrinking feature we can get Oracle to move rows which are located in the middle or at the end of a segment further more down to the beginning of the segment and by this make the segment more compact. 

For this we must first allow ORACLE to change the ROWIDs of these rows by issuing 

ALTER TABLE emp ENABLE ROW MOVEMENT;

ROWIDs are normally assigned to a row for the life time of the row at insert time.

After we have given Oracle the permission to change the ROWIDs we can now issue a shrink statement.

ALTER TABLE emp SHRINK SPACE;

This statement will proceed in two steps:

• The first step makes the segment compact by moving rows further down to free blocks at the beginning of the segment.
• The second step adjusts the high watermark. For this Oracle needs an exclusive table lock, but for a very short moment only.

Table shrinking Feature:

• will adjust the high watermark
• can be done online
• will cause only rowlocks during the operation and just a very short full table lock at the end of the operation
• indexes will be maintained and remain usable
• can be made in one go
• can be made in two steps

This can be usefull if you cannot get a full table lock during certain hours, you only make the first step and adjust the high watermark later when it is more conveniant.

- ALTER TABLE emp SHRINK SPACE; -- only for the emp table

- ALTER TABLE emp SHRINK SPACE CASCADE; -- for all dependent objects as well

- ALTER TABLE emp SHRINK SPACE COMPACT; -- only makes the first step (moves the rows)

How are the Indexes maintained?

In the first phase Oracle scans the segment from the back to find the position of the last row.

Afterwards it scan the segment from the beginning to find the position of the first free slot in a block in this segment. In case the two positions are the same, there is nothing to shrink. In case the two positions are different Oracle deletes teh row from the back and inserts it into the free position at front of the segement. Now Oracle scan teh segement from the back and front again and again until it finds that the two positions are the same.

Since it is DML statements performed to move the rows, the indexes are maintained at the same time. Only row level locks are used for these operations in the first pase of SHRINK TABLE statement.

The following restrictions apply to table shrinking:

1.)It is only possible in tablespaces with ASSM.

2.) You cannot shrink:

- UNDO segments

- Temporary segments

- Clustered tables

- Tables with a column of datatype LONG

- LOB indexes

- IOT mapping tables and IOT overflow segments

- Tables with MVIEWS with ON COMMIT

- Tables with MVIEWS which are based on ROWIDs

The Oracle 10g Oracle comes with a Segment Advisor utility.

The Enterprise Manager, Database Control, even has a wizzard which can search for shrink candidates. This advisor is run automatically by an autotask job on a regular basis in the default maintainance window. You can use the built in package DBMS_SPACE to run the advisor manually as well.

Source:

http://chandu208.blogspot.in/2012/02/high-water-mark-hwm.html

http://www.sysdba.ch/index.php/13-how-to-adjust-the-high-watermark-as-of-oracle-10g-alter-table-shrink