消除行遷移 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

【效能最佳化】消除行遷移 table fetch continued row

相關文章