ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 降低HWM时需要注意的问题

降低HWM时需要注意的问题

原创 Linux操作系统 作者:lsm_3036 时间:2011-08-24 14:12:06 0 删除 编辑

想要降低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 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

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16978544/viewspace-705704/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-12-08

  • 博文量
    64
  • 访问量
    138428