行迁移表重组计划

一、行迁移简介

Oracle按照块保存数据，当一条数据由于某种因素如update，导致这条数据变长，且当前BLOCK没有足够的空间容纳变长的数据，这时Oracle会将这条数据移到其他BLOCK中，然后在以前的块保存一个新存储位置的地址链接。即在原先存储该行的地方增加了一个新的指针，该指针指向迁移后的block id，这种现象叫做行迁移。

 

在访问发生了行迁移的数据时，要读取迁移前和迁移后的2个block。由于要多读一个block，所以会多消耗一定的IO。导致的后果就是访问行迁移的数据变慢。所以对一些访问频繁，数据量大的表需要尽量避免行迁移的发生。

 

二、迁移步骤

 

1.需要重组的表共19张

SQL> select  owner , table_name , num_rows,avg_space,chain_cnt   2    from dba_tables   3   where num_rows > 1000 and   4     chain_cnt > 1   5   order by chain_cnt desc;

 

 

	OWNER	TABLE_NAME	NUM_ROWS	AVG_SPACE	CHAIN_CNT
1	CAICPROD	VSMARK	5841264	3078	597147
2	CAICPROD	PRPJPAYREFREC	4832555	2378	120240
3	CAICPROD_CA	PRPITLOG	806671	823	25013
4	CAICPROD	CICLAIMDEMAND	351086	965	9461
5	CAICPROD	ACCARTICLEBALANCE	5306350	886	4960
6	CAICPROD	ACCMAINVOUCHER	32594	2858	4919
7	CAICPROD	FSOBRANCHD	4266172	855	4098
8	CAICPROD	WFFLOWMAIN	2595263	849	2552
9	CAICPROD	PRPCARDSUB	2430071	902	2314
10	CAICPROD_CA	PRPLCHECKTASK	259608	955	504
11	CAICPROD_CA	JBPM_VARIABLEACCESS	69139	3220	161
12	CAICPROD	FZPLANDETAIL	25348	1410	105
13	CAICPROD	UTIUSERGRADEPOWER	10300	1428	98
14	CAICPROD	PRPJREINSFEE	60111	1156	54
15	CAICPROD	ACCFEEALLOC	24663	1444	52
16	CAICPROD	MTIMPORTDATA	4231	5120	18
17	CAICPROD	FZPLANMAIN	12447	3443	16
18	CAICPROD	PRPDUSERSUB	10844	1275	15
19	CAICPROD	PRPDCOMPANY	1696	881	4

 

由于ACCMAINVOUCHER表在今年8月份已经重组过，现在又发生了行迁移。

鉴于此表发生行迁移的频率较高，考虑增加此表的pctfree 到20或30后在进行重组。

SQL> select t.table_name,   2         t.pct_free,   3         t.ini_trans,   4         t.num_rows,   5         t.avg_space,   6         t.avg_row_len,   7         t.chain_cnt   8    from user_tables t   9   where t.table_name = 'ACCMAINVOUCHER';

 

	TABLE_NAME	PCT_FREE	INI_TRANS	NUM_ROWS	AVG_SPACE	AVG_ROW_LEN
1	ACCMAINVOUCHER	10	1	32594	2858	134

 

 

alter table ACCMAINVOUCHER pctfree 20;

2.行迁移表及索引所占用的存储空间 约13G

SQL> select sum(s.bytes/1024/1024)  存储空间MB   2    from dba_segments s   3   where s.segment_name in   4         ('VSMARK', 'PRPJPAYREFREC', 'PRPITLOG', 'CICLAIMDEMAND',   5          'ACCARTICLEBALANCE', 'ACCMAINVOUCHER', 'FSOBRANCHD', 'WFFLOWMAIN',   6          'PRPCARDSUB', 'PRPLCHECKTASK', 'JBPM_VARIABLEACCESS', 'FZPLANDETAIL',   7          'UTIUSERGRADEPOWER', 'PRPJREINSFEE', 'ACCFEEALLOC', 'MTIMPORTDATA',   8          'FZPLANMAIN', 'PRPDUSERSUB', 'PRPDCOMPANY',   9          'SYS_IL0000058036C00021$$', 'IDX_ITCERTINO', 'PK_PRPITLOG',  10          'IND_LCHECKTASK_REGISTNO', 'IND_LCHECKTASK_PRPLCHECKID',  11          'PK_LCHECKTASK', 'SYS_C0088241', 'SYS_IL0000058007C00008$$',  12          'SYS_IL0000058007C00007$$', 'PK_PRPDCOMPANY', 'IDX_COM_UPPERCOMCODE',  13          'IDX_UTIUSERGRADEPOWER_USERCODE', 'PK_UTIUSERGRAD2',  14          'IDX_VSMARK_VISANAME', 'IDX_VSMARK_BUSINESSNO',  15          'IDX_VSMARK_USERCODE', 'IDX_VSMARK_VISACODE', 'IDX_VSMARKBUSVS',  16          'PK_VSMARK', 'IDX_VSMARK_USEDATE', 'IDX_VSMARK_FINALUSERNAME',  17          'IDX_VSMARK_FINALUSERCODE', 'IDX_VSMARK_OPERATEDATE',  18          'IDX_VSMARK_OPERATENAME', 'IDX_VSMARK_OPERATECODE',  19          'IDX_VSMARK_COMCODE', 'IDX_ACCARTICLEBALANCE_BOOKTYPE',  20          'PK_ACCARTICLEBALANCE', 'PK_ACCFEEALLOC',  21       'IDX_ACCMAINVOUCHER_VOUCHERTYPE', 'IDX_ACCMAINVOUCHER_ACCBOOKCODE',  22          'PK_ACCMAINVOUCHER', 'PK_CICLAIMDEMAND', 'IND_FSOBRANCHD_PAYTYPE',  23          'IND_FSOBRANCHD_PAYDATE', 'PK_FSOBRANCHD', 'PK_FZPLANDETAIL',  24          'PK_FZPLANMAIN', 'PK_MTIMPORTDATA', 'PK_PRPDUSERSUB',  25          'IDX_PRPJPAYREFREC_PAYREFNO', 'IDX_JPAYREFREC_OPERATORCODE',  26          'IDX_JPAYREFREC_HANDLERCODE', 'IDX_JPAYREFREC_HANDLER1CODE',  27          'PK_PRPJPAYREFREC', 'IDX_PRPJPAYREFREC_UWRITEDATE',  28          'IDX_PRPJPAYREFREC_STARTDATE', 'IDX_PRPJPAYREFREC_RISKCODE',  29          'IDX_PRPJPAYREFREC_APPLINAME', 'IDX_PRPJPAYREFREC_CERTINO',  30          'IDX_PRPJPAYREFREC_PAYREFDATE', 'IDX_PRPJPAYREFREC_VISACODESEL',  31          'IDX_PRPJPAYREFREC_VISACODE', 'IDX_PRPJPAYREFREC_COMCODE',  32          'IDX_PRPJPAYREFREC_COMCERTI', 'IDX_PRPJPAYREFREC_POLICYNO',  33          'PK_PRPJREINSFEE', 'PK_WFFLOWMAIN', 'PK_PRPCARDSUB',  34          'IDX_PRPCARDSUB_POLICYNO', 'IDX_PRPCARDSUB_CERTINO',  35          'IDX_PRPCARDSUB_BUSINESSNO');

 

存储空间MB

13014.75

 

3.表备份

EXP CAICPROD/*** file=201010CAIC.dmp  direct=y tables= '('CICLAIMDEMAND', 'ACCARTICLEBALANCE', 'ACCMAINVOUCHER', 'FSOBRANCHD', 'WFFLOWMAIN','PRPCARDSUB','JBPM_VARIABLEACCESS','FZPLANDETAIL','UTIUSERGRADEPOWER','PRPJREINSFEE','ACCFEEALLOC','MTIMPORTDATA','FZPLANMAIN','PRPDUSERSUB','PRPDCOMPANY','VSMARK','PRPJPAYREFREC')';

 

EXP CAICPROD_CA/*** file=201010CA.dmp  direct=y tables= '('PRPITLOG', 'PRPLCHECKTASK', 'JBPM_VARIABLEACCESS')';

 

 

4.重组行迁移表

-- CAICPROD 用户下执行

select 'alter table   ' || TABLE_NAME ||        '   MOVE TABLESPACE COSTSHAR ; '   from user_tables WHERE TABLE_NAME in ('VSMARK','PRPJPAYREFREC');

select 'alter table   ' || TABLE_NAME || '   MOVE  ; '   from user_tables  WHERE TABLE_NAME in        ('CICLAIMDEMAND', 'ACCARTICLEBALANCE', 'ACCMAINVOUCHER', 'FSOBRANCHD', 'WFFLOWMAIN', 'PRPCARDSUB', 'JBPM_VARIABLEACCESS', 'FZPLANDETAIL', 'UTIUSERGRADEPOWER', 'PRPJREINSFEE', 'ACCFEEALLOC', 'MTIMPORTDATA', 'FZPLANMAIN', 'PRPDUSERSUB', 'PRPDCOMPANY');

 

--CAICPROD_ca 用户下执行

select 'alter table   ' || TABLE_NAME || '   MOVE  ; '   from user_tables  WHERE TABLE_NAME in ('PRPITLOG', 'PRPLCHECKTASK', 'JBPM_VARIABLEACCESS');

5.rebuild行迁移表索引

--CAICPROD用户下执行

select 'alter index   ' || INDEX_NAME || '   rebuild  ; '   from user_indexes  WHERE TABLE_NAME in        ('CICLAIMDEMAND', 'ACCARTICLEBALANCE', 'ACCMAINVOUCHER', 'FSOBRANCHD', 'WFFLOWMAIN', 'PRPCARDSUB', 'JBPM_VARIABLEACCESS', 'FZPLANDETAIL', 'UTIUSERGRADEPOWER', 'PRPJREINSFEE', 'ACCFEEALLOC', 'MTIMPORTDATA', 'FZPLANMAIN', 'PRPDUSERSUB', 'PRPDCOMPANY', 'VSMARK','PRPJPAYREFREC');

 

--CAICPROD_CA用户下执行

select 'alter index   ' || INDEX_NAME || '   rebuild  ; '   from user_indexes  WHERE TABLE_NAME in ('PRPITLOG', 'PRPLCHECKTASK', 'JBPM_VARIABLEACCESS');

 

6.统计分析

--CAICPROD用户下执行

select  'analyze    table     ' || table_name   || '  estimate    statistics;'  from user_tables t where t.table_name  in        ('CICLAIMDEMAND', 'ACCARTICLEBALANCE', 'ACCMAINVOUCHER', 'FSOBRANCHD', 'WFFLOWMAIN', 'PRPCARDSUB', 'JBPM_VARIABLEACCESS', 'FZPLANDETAIL', 'UTIUSERGRADEPOWER', 'PRPJREINSFEE', 'ACCFEEALLOC', 'MTIMPORTDATA','FZPLANMAIN','PRPDUSERSUB','PRPDCOMPANY','VSMARK', 'PRPJPAYREFREC');

 

--CAICPROD_CA用户下执行

select 'analyze    table     ' || table_name || '  estimate    statistics;' from user_tables t  where t.table_name in ('PRPITLOG', 'PRPLCHECKTASK', 'JBPM_VARIABLEACCESS');

 

7.检查是否有无效对象

--CAICPROD用户下执行

select 'alter  ' || object_type || ' ' || owner || '.' || object_name ||     ' compile '  || ';'   from dba_objects  o   where  o.status='INVALID';

 

 

三、建议

由于行迁移的产生是不可避免的，同时为了减小行迁移对对性能带来的影响。建议周期性（每月或每季度）的对生产环境表迁移情况进行检查。及时消除行迁移带来的性能影响。