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

海星星hktk發表於2014-09-10
優化
消除行遷移 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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29475508/viewspace-1267320/，如需轉載，請註明出處，否則將追究法律責任。

相關文章