降低HWM方法实验move shrink

想要降低HWM，有2中方法

alter table table_name MOVE;

alter table table_name SHRINK space;

 

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

查询含有LOB类型字段的表：

select DISTINCT a.table_name fromdba_tab_columns a,dba_segments b where a.table_name=b.segment_name andb.owner='GISTAR' AND A.DATA_TYPE IN ('LOB') AND B.segment_TYPE='TABLE' orderby 1;

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;
   

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 the beginning of the segment. Shrink is a combination of delete/insert pair for every row movement and this generates many UNDO and REDO 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