Resetting High Water Mark in Oracle10g

I would like to write how to reset HWM in oracle10g. Prior to Oracle10g, resetting high water mark is painful procedure in busy environment. Oracle made our life easy to reset the High Water Mark in oracle10g. I am not going to discuss about what is HWM. Since i already discussed this in another topic. Please click to read about what is HWM and what are the various options available to reset the HMW.

We have traditional ways to reset the HWM prior to Oracle10g. We might need Table downtime when we use below traditional methods....

1. Imp/exp
2. Alter tablespace move
3. truncate and insert
4. user dbms_redefinition package to copy the table

Okay... Let us talk about resetting HWM in Oracle10g. The tablespace should be ASSM(Automatic segment space Management) enabled to leverge this feature. In oracle10g, we do not need table downtime to reset the HWM. It would be easy to reset the HWM in 24/7 environment.

What does Oracle do while using Oracle10g feature to reset the HMW?

Oracle split this process as two phase. Let me explain what is happening in each phase.

Phase I. Oracle move the rows which are located in the middle or at the end of a segment further more down to the beginning of the segment and make the segment more compact. This shrinking process is kind of delete and insert. But it is not really!!!. This process is moving the data row by row. It acquires a row level lock when the row is moved down to the begining of the segment. The corresponding index data will be handled like any other row level DML. So we do not need to worry about rebuilding the indexes for the row. Also row level lock will happen for very short moment. Before we start this phase, we need to enable row movement. Here is the command to complete the phase I. Here i am using the table called bookings.

SQL> alter table bookings enable row movement;

Table altered.

SQL> alter table bookings shrink space compact;

Table altered

Phase II This step will reset the high water mark. This will acquire table level lock for very short moment while resetting the HWM. Here is the command to accomplish this task.

SQL> alter table bookings shrink space;

Table altered

SQL> alter table bookings shrink space cascade; (it is for all dependent objects as well)

Table altered

If we want to reset the HWM in one go, then below command will accomplish the task. The below command moves the rows and reset the HWM.

SQL> alter table bookings shrink space;

Table altered

What are the advantages of using Oracle10g new feature to reset the HWM?

There are serveral advantages over traditional methods. Let me list the advantages here.

1. It can be done in online. There is a table level lock for very short moment. Traditional methods are not supporting to reset the HWM in online.

2. It does not take extra space while resetting the HWM. If we use traditional method, DBMS_REDEFINITION package use double the amount of space.

3. It does acquire only row level lock while performing majority of the shrinking(moving rows) work. It acquires table level lock only when it resets the HWM which is in phase II. But traditional methods requires table down time for resetting the HWM except using dbms_redefinition package.

4. Index will be maintained and remain usable. But in traditional methods, we need to rebuild the index. Especially when we use ALTER TABLESPACE MOVE command.

5. It can be made in one command(alter table emp shrink space). In traditional method, there are multiple steps.

6. If you are not sure that you can afford table level lock at specific time, then you can do the majority of the shriniking work and later we can reset the HWM. Since table level lock is required only while resetting the HWM. The whole process can be done in two steps as i explained above. This advantage is not available in traditional methods.

What are the restriction of using Oracle10g new feature to reset the HWM?

1. It is only possible in ASSM tablespace
2. Not supporting for clustered tables, tables with column data type LONG

What circumstances we can reset the HWM as two phase?

The table is not frequently used(insert/update/delete) and it can afford to have table level lock, then we can reset the HWM in one go. We can reset the HWM as two steps when the table is used by several people and it is always busy and it does not permit the table level lock even for short moment at specific time. This scenario, we can move the rows to shrink the table. Then in the night time or off peak hours, we can reset the HWM.

Benefits of RMAN Backup vs. Scripted Backups

RMAN was introduced in oracle8i. Before oracle8i, we used traditional backup or scripted backup. Why did oracle move to RMAN backup? Why do we need to use RMAN to backup our database? We may already be doing online backup with some wonderfully crafted, home-grown scripts. We may ask this question ourself. Why should we start using RMAN when my scripted backups are working fine?

We are moving to RMAN backup for two reasons. One is, when backup scripts breaks, the database backup would fail. Another issue is, when script fails, some one has to fix the backup scripts.

There are number of other positive reason to move to RMAN backup. Here are these....

1. RMAN will detect corrupted blocks and report them to us. RMAN also can recover only corrupted blocks. This is called Block Media Recovery(BMR). In traditional recovery, we need to restore and recover the whole data file. But in RMAN, it is not required to restore and recover the entire data file for just few corrupted blocks.

2. RMAN can back up your database online without having to put the tablespace in hot backup mode. Thus, the additional redo generated during a hot backup is reduced.

3. RMAN will automatically track new datafiles and tablespaces for us. In scripted backup, we might need to change the backup script when new tablespace or data file is added in the database.

4. RMAN will only backup up used data blocks(up to the high water mark(HWM)). So RMAN backup images typically are smaller than those of traditional online back images.

5. RMAN provides easy, automated backup, restore and recovery operations. RMAN tracks all the backups needed to recover the database.

6. RMAN can work fairly seamlessly with third-party media management products.

7. RMAN supports incremental backup strategies.

8. With RMAN, we can actually test backups without restoring them. We can validate the backup and make sure backup will work at the time of restore.

9. If we use the repository, then RMAN provides a nice, centralized reporting facility.

I have discussed some of the other topics which are related to this thread. Please go through below links in case if you are interested....

How do we enable archivelog in oracle? Click here

How do we set up RMAN in oracle? Click here

What are the new features in RMAN since Oracle9i? Click here

Different type of recovery scenario in RMAN? Click here

HWM - High Water Mark

What is High Water Mark(HWM)?

HWM is boundry between used block and unused block. Blocks below HWM is used blocks and above HWM is unused blocks. Used blocks might or might not have the data. In normal operations (Insert/update), the HWM is mostly bump up and never go down. Oracle tracks the HWM for the segment in segment header.

How do we identify the HWM?

Option 1

We can use DBMS_SPACE.UNUSED_SPACE procedure.

Please remember, i used pipe symbol in the below code and it is not visible in blog. So placed ### symbol in the place of pipe sign. Please replace thie ### sign with pipe symbol when we run this code in any of your test database.

DECLARE
alc_bks NUMBER;
alc_bts NUMBER;
unsd_bks NUMBER;
unsd_bts NUMBER;
luefi NUMBER;
luebi NUMBER;
lub NUMBER;
BEGIN
FOR c1_row IN (SELECT table_name
FROM user_tables where table_name='COLLECTION') LOOP
DBMS_SPACE.UNUSED_SPACE (
segment_owner => USER,
segment_name => c1_row.table_name ,
segment_type => 'TABLE' ,
total_blocks => alc_bks ,
total_bytes => alc_bts ,
unused_blocks => unsd_bks ,
unused_bytes => unsd_bts ,
last_used_extent_file_id => luefi ,
last_used_extent_block_id => luebi ,
last_used_block => lub);
DBMS_OUTPUT.PUT_LINE( 'Data for '### c1_row.table_name);
DBMS_OUTPUT.PUT_LINE( RPAD('*',LENGTH(c1_row.table_name) + 10,'*'));
DBMS_OUTPUT.PUT_LINE( 'Total Blocks.................'### alc_bks );
DBMS_OUTPUT.PUT_LINE( 'Total Bytes..................'###alc_bts );
DBMS_OUTPUT.PUT_LINE( 'Unused Blocks................'### unsd_bks );
DBMS_OUTPUT.PUT_LINE( 'Unused Bytes.................'###unsd_bts );
DBMS_OUTPUT.PUT_LINE( 'Last Used Ext File Id........'### luefi );
DBMS_OUTPUT.PUT_LINE( 'Last Used Ext Block Id.......'### luebi );
DBMS_OUTPUT.PUT_LINE( 'Last Used Block..............'###lub );
DBMS_OUTPUT.NEW_LINE;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,250));
END;
/

Here is the output for the above code.

Data for COLLECTION
************************
Total Blocks.................38656
Total Bytes..................158334976
Unused Blocks................0
Unused Bytes.................0
Last Used Ext File Id........60
Last Used Ext Block Id.......1284368
Last Used Block..............256

PL/SQL procedure successfully completed.

SQL>
HWM = Total Blocks - Unused Blocks
HWM = 38656-0
HWM = 38656

Option 2

we can use user_tables.
select sum(blocks)-sum(empty_blocks) from user_tables

Please remember, the above query gives the result based on the current statistics. Best way to find the HWM is to use DBMS_SPACE.UNUSED_SPACE(Option 1) procedure.

How do we find which table is good candidate to reset the HWM?

I have answered for this as a different topic in the same blog. Please click to answer this question.

Why do we need to reset the HWM?

1. When ever optimizer takes full table scan, it scan 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.

2. Oracle does not release the free space under HWM for use by other objects for a simple reason: because that space is reserved for new inserts and to accommodate the growth of existing rows. The additional benefit of resetting HWM is a more efficient utilization of space because the space freed is available for other objects.

3. When rows are inserted with direct path—for example, through Direct Load Insert (insert with the APPEND hint) or through the SQL*Loader direct path—the data blocks are placed directly above the HWM. The space below it remains wasted.

How do we reset the HWM?

Option 1
Alter table emp move tablespace

This option requires rebuilding the indexes. since the index will be invalid/unusable after running the above command. The downside is, rebuilding the index is additional overhead when we use this option. Also users can not use the application or reports while rebuilding the index.

Option 2

1. Export the data
2. truncate the table
3. import the table
4. Analyze the table

Option 3
1. copy the table data
2. truncate the original table
3. insert back.

Option 4
Use DBMS_REDEFINITION package to copy the table.

Option5
In oracle10g, we have better option to reset the high water mark. We need to enable row movement before we shrink the space. We do not need to rebuild the index after reseting the HWM. This is the best option to reset the HWM. But this feature is not available in oracle9i.

SQL@DBA1> alter table bookings enable row movement;

Table altered.

SQL@DBA1>
SQL@DBA1> alter table bookings shrink space compact;

Table altered.

If you need to read more about how to reset the HWM in oracle10g, please click this link. I wrote an article as a different topics.