【性能优化】消除行迁移 table fetch continued row

消除行迁移 table fetch continued row



1、搭建实验环境

1.1、创建新表,同时把表的pctfree设置为0
SCOTT@ prod>create table emp3 as select * from emp where 1=2;
SCOTT@ prod>alter table emp3 modify empno number(10);
SCOTT@ prod>alter table emp3 modify ENAME varchar2(30);
SCOTT@ prod>alter table emp3 pctfree 0;
1.2、创建序列
CREATE SEQUENCE emp3_empno
    INCREMENT BY 1
    START WITH 1
    MAXVALUE 100000000
    CACHE 10000
    NOCYCLE;
1.3、插入数据
SCOTT@ prod>begin
 2    for i in 1..20000000 loop
 3      insert into emp3
 4      values (emp3_empno.nextval,'lvxinghao','SALESMAN',7698,sysdate,1600,300,30);
 5      if mod(i,1000)=0 then
 6      commit;
 7      end if;
 8    end loop;
 9    commit;
10  end;
11  /

1.4、修改表emp3,使其产生行迁移
undo表空间开启自动扩展。

SCOTT@ prod>update emp3 set ename='LVXINGHAO_BEIJING';
20000000 rows updated.
Elapsed: 00:21:55.54
SCOTT@ prod>commit;

1.5、然后分析表,查看行迁移的数量
SCOTT@ prod>analyze table emp3 estimate statistics;

SCOTT@ prod>select chain_cnt from user_tables where table_name='EMP3';
CHAIN_CNT
----------
  4702154


2、解决方案:消除行迁移
2.1、查看EMP3表的pct_free
SYS@ prod>select owner,table_name,tablespace_name,pct_free
   from dba_tables where owner='SCOTT' and table_name='EMP3';

OWNER      TABLE_NAME TABLESPACE_NAME        PCT_FREE
---------- ---------- -------------------- ----------
SCOTT      EMP3       TEST3                         0

SYS@ prod>alter table scott.emp3 pctfree 10;

Table altered.

SYS@ prod>select owner,table_name,tablespace_name,pct_free
   from dba_tables where owner='SCOTT' and table_name='EMP3';

OWNER      TABLE_NAME TABLESPACE_NAME        PCT_FREE
---------- ---------- -------------------- ----------
SCOTT      EMP3       TEST3                        10


2.2、生成chained_rows表
SCOTT@ prod>@?/rdbms/admin/utlchain.sql
SCOTT@ prod>analyze table emp3 list chained rows into chained_rows;
Table analyzed.
Elapsed: 00:04:15.55

2.3、建立新表存放行迁移的数据
SCOTT@ prod>create table emp3_tmp as select * from emp3                                                      
 2  where rowid in (select HEAD_ROWID from chained_rows);
Table created.
Elapsed: 00:01:14.16


2.4、删除原表中发生行迁移的行
SCOTT@ prod>delete emp3 where rowid in (select HEAD_ROWID from chained_rows);
3772857 rows deleted.
Elapsed: 00:12:04.48



2.5、重新插入这些数据
SCOTT@ prod>insert into emp3 select * from emp3_tmp;    
3772857 rows created.
Elapsed: 00:01:51.93

2.6、分析表、查看行迁移的数量
SCOTT@ prod>analyze table emp3 estimate statistics;
Table analyzed.
Elapsed: 00:00:07.60
SCOTT@ prod>select chain_cnt from user_tables where table_name='EMP3';
CHAIN_CNT
----------
        0
Elapsed: 00:00:00.02
SCOTT@ prod>



【检查行迁移的方法】
1) 运行$ORACLE_HOME/rdbms/admin/utlchain.sql
2) analyze table table_name list chained rows into CHAINED_ROWS
3) select * from CHAINED_ROWS where table_name='table_name';

消除行迁移的方法】
方法1:
create table table_name_tmp as select * from table_name where rowed in (select head_rowid from chained_rows);
Delete from table_name where rowed in (select head_rowid from chained_rows);
Insert into table_name select * from table_name_tmp;
方法2:
create table table_name_tmp as  select * from table_name ;
truncate table table_name
insert into table_name    select * from table_name_tmp
方法3:
用exp工具导出表,然后删除这个表,最后用imp工具导入这表
方法4:
alter table table_name move tablespace tablespace_name,然后再重新创建表的索引

上面的4种方法可以用以消除已经存在的行迁移现象,但是行迁移的产生很多情况下时由于PCT_FREE参数设置的太小所导致,所以需要调整PCT_FREE参数的值。


吕星昊
2014.9.10
上一篇:交互式编程及注释的分类 | 手把手教你入门Python之十二


下一篇:博客园REST系列文章索引