Fix High Water Mark in Oracle Database

High Water Mark management in Oracle Database

Definition:
Each table is made up of extents and each extent is made up of oracle blocks - a common block size is 8k. So you have a table with 10 extents (80K).

You populate your table with 2 million rows of data and you will have many hundreds of extents. Now lets assume that you delete over half of the records in the table. Oracle still has the same number of extents but many of the blocks are empty. When you run a query against the table Oracle will scan through all the blocks including empty ones looking for data. So you can think of the total number of extents / blocks used as the high water mark.

Fix-1:
To fix you export the table, drop it and import it back in.  But down time must be required. Some precautions to be taken like grants, constraint enable script etc. But this is best way.

So how can we release unused space from a 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.

Fix-2: Move table to different tablespace:

SQL> 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.

Fix-3: Starting with Oracle 10g/11g

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. 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 all 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


SQL> 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.

SQL> ALTER TABLE emp SHRINK SPACE;

This statement will procede 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…
- 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 convenient:

- 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 nth row from the back and inserts it into the free position at front of the segment. Now Oracle scan nth segment 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 colmn 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 wizard which can search for shrink candidates. This advisor is run automatically by an autotask job on a regular basis in the default maintenance window. You can use the built in package DBMS_SPACE to run the advisor manually as well.

To run manually segment advisor in Oracle 11g/11g: Click here

Caution: Be careful when you are performing these activities. Take proper backups and then do. If you are using Oracle 10g/11g then check with segment advisor notice.

It will be better to reorganize the entire tablespace if you have more tables to manage water mark.

Thanks