一、行迁移简介
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 || |
select 'alter table ' || TABLE_NAME || ' MOVE ; ' |
--CAICPROD_ca 用户下执行
select 'alter table ' || TABLE_NAME || ' MOVE ; ' |
5.rebuild行迁移表索引
--CAICPROD用户下执行
select 'alter index ' || INDEX_NAME || ' rebuild ; ' |
--CAICPROD_CA用户下执行
select 'alter index ' || INDEX_NAME || ' rebuild ; ' |
6.统计分析
--CAICPROD用户下执行
select 'analyze table ' || table_name || ' estimate statistics;' 'PRPJPAYREFREC'); |
--CAICPROD_CA用户下执行
|
7.检查是否有无效对象
--CAICPROD用户下执行
select 'alter ' || object_type || ' ' || owner || '.' || object_name || |
三、建议
由于行迁移的产生是不可避免的,同时为了减小行迁移对对性能带来的影响。建议周期性(每月或每季度)的对生产环境表迁移情况进行检查。及时消除行迁移带来的性能影响。