Oracle降低HWM的集中方法

High Water Mark (HWM)指的是當我們建立一個segment的時候，在segment內的一個指標，界定了segment 內曾經配置過的block水位。
The high water mark is the boundary between used and unused space in a segment ~ (From Oracle Concept)

簡 單如圖所示，剛建立segment時尚未insert資料，HWM通常在segment header附近，當你不斷地insert資料使用了更多的block之後，HWM自然會跟著移動。但是日後你delete資料後，雖然實際儲存資料的 block已經減少，但是HWM仍然不會改變，這就像水庫的水位曾經滿到某個刻度而留下一道水痕，雖然水位下降了但是仍然清晰可見那道高水位。

 

HWM之下如果存在太多空的block，最大的缺點就是當發生Full Table Scan (FTS)的時候，會造成過多無謂的IO，因為FTS會讀取segment內的block直到HWM為止，如此也會造成buffer pool的使用效率不佳。

HWM是oracle中block有没有使用的分界线，它会随着数据的insert而上升，但它并不会随数据的delete而下降，因此全表扫描的时间并不因数据的delete而减少，相反可能由于块清除反而全表扫描时间增加，可以用下面方法的任一一种来降低HWM：

第一种：shrink

适用于10g以后的版本，前提是这个表，索引，物化视图或物化视图log所在表空间segment的管理是auto的，并且表已enable row movement，如：

SQL> select owner,segment_name,bytes,blocks,extents from dba_segments
where segment_name = 'EMP';

SQL> alter table emp enable row movement;

SQL> alter table emp shrink space;

SQL> select owner,segment_name,bytes,blocks,extents from dba_segments
where segment_name = 'EMP';

第二种：online redefinition

online redefinition在oracle 9i引用，10g对DBMS_REDEFINITION包做了一些调整，使用的操作起来理简单，如新增加的COPY_TABLE_DEPENDENTS，UNREGISTER_DEPENDENT_OBJECT，方法如下：

1.调用dbms_redefinition.can_redef_table procedure确认是否可以online redefinition

SQL> EXECUTE dbms_redefinition.can_redef_table ('FINANCE', 'ACCTS_PAYABLE');

2.在redefinition schema下创建interim table

3.如果redefinition一个大表，可以用下面的Statement来改善性能:

SQL> alter session force parallel dml parallel degree-of-parallelism;

SQL> alter session force parallel query parallel degree-of-parallelism;

4.调用dbms_redefinition.start_redef_table procedure开如redefinition

SQL> dbms_redefinition.start_redef_table('FINANCE', 'ACCTS_PAYABLE','ACCTS_PAYABLE_STAGE');

5.创建dependent objects on interim table

有两种方法：

调用dbms_redefinition.copy_table_dependents自动创建dependent objects;

使用create statement手动创建dependent objects，手动创建的dependents需要调用register_dependent_object注册

6.更新在redefinition过程中的更新到interim表

7.调用dbms_redefinition.finish_redef_table完成redefinition

8.删除收回空间如interim table使用的表

注：使用online redefinition需要额外的空间

第三种：alter table … move

在move时可以使用当前表空间也可以指定其他表空间，如：

SQL> alter table emp move;        --使用当前表空间

SQL> alter table emp move tablespace tbs_emp;           --把表移动到tbs_emp表空间上

在move的过程会锁表，类似下的语句是不能执行:

select rownum from emp where rownum=1 for update nowait;

使用move降低HWM也需要额外表的空间.

第四种：expdp/impdp或exp/imp + truncate

第五种：rename + insert append

上面这两种方法在此就不作说明了.

总结：

shrink和online redefinition是首选择方法，其他方法在降低HWM时会有较长的时间表不能访问（锁表或直接不能访问），另外表后面的四种方法都可以改变表的表空间，online redefinition可以将普通表转换为分区表.