降低HWM时需要注意的问题

想要降低HWM，有2中方法，alter table MOVE， alter table SHRINK 。

ALTER TABLE MOVE 步骤:
1. desc username.table_name ----检查表中是否有LOB

2. 如果表没有LOB字段

直接 alter table move; 然后 rebuild index

如果表中包含了LOB字段

alter table owner.table_name move tablespace tablespace_name lob (lob_column) store as lobsegment tablespace tablespace_name;

也可以单独move lob，但是表上面的index 同样会失效，这是不推荐的

alter table owner.table_name move lob(lob_column) store as lobsegment tablespace tablespace_name ;

3. rebuild index

首先用下面的SQL查看表上面有哪类索引:

select a.owner,a.index_name,a.index_type,a.partitioned,a.status,b.status p_status,b.composite from dba_indexes
a left join dba_ind_partitions b on a.owner=b.index_owner and a.index_name=b.index_name where a.owner='&owner' and a.table_name='&table_name';

对于普通索引直接rebuild online nologging parallel,对于分区索引，必须单独rebuild 每个分区,对于组合分区索引,必须单独rebuild 每个子分区。

4.对表收集统计信息

我通常采取

Move 来降低HWM，因为Move 与 Shrink算法不一样，Move 操作比Shrink快

来自metalink note:577375.1:

The shrink algorithm starts from the bottom of the segment and starts moving those rows to thebeginning of the segment. Shrink is a combination of delete/insert pair for every row movementand thisgenerates many UNDO andREDO blocks .

Move从segment的底部开始，move这些rows到segment的头部。Shrink则是delete/insert相结合，这样会产生非常多的UNDO和REDO。

关于MOVE , SHRINK效率比较实验

SQL> create table t as select * from dba_objects;

Table created.

SQL> delete from t where rownum<=20000;

20000 rows deleted.

SQL> commit;

Commit complete.

SQL> alter session set events '10046 trace name context forever,level 12';

Session altered.

SQL> alter table t move;

Table altered.

SQL> alter session set events '10046 trace name context off';

---找到trace文件，我将其改名为 move.trc tkprof move.trc move.txt sys=yes waits=yes explain=robinson/oracle

alter table t move

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 1 0 0
Execute 1 0.45 0.71 198 741 1057 30446
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.46 0.72 198 742 1057 30446

SQL> drop table t purge;

Table dropped.

SQL> create table t as select * from dba_objects;

Table created.

SQL> delete from t where rownum<=20000;

20000 rows deleted.

SQL> commit;

Commit complete.

SQL> alter table t enable row movement;

Table altered.

SQL> alter session set events '10046 trace name context forever,level 12';

Session altered.

SQL> alter table t shrink space;

Table altered.

SQL> alter session set events '10046 trace name context off';

Session altered.

---找到trace文件，我将其改名为shrink.trc tkprof shrink.trc shrink.txt sys=yes waits=yes explain=robinson/oracle

alter table t shrink space

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 2.67 14.94 183 1265 50349 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 2.67 14.94 183 1265 50349 0

可以看到move速度是 shrink的 14.94/0.72=20.75倍，shrink耗费cpu,产生很多current block这样生成巨大的redo与undo 所以强烈推荐用MOVE降低HWM