oracle 踢重,Oracle在线重定义操作一张近300G的表的实验

在线重定义操作一张近300G的表的实验"/>

oracle 踢重,Oracle在线重定义操作一张近300G的表的实验

Oracle在线重定义操作一张近300G的表的实验

author:zy

目录：

说明：本文相关表名、库名已过敏

总结

通过本实验，证明重定义是可以操作大表的。其原理也就是通过insert/*+ BYPASS_RECURSIVE_CHECK APPEND SKIP_UNQ_UNUSABLE_IDX */这种不会产生undo

重定义需要的数据表空间、TEMP表空间、归档空间较多，在重定义之前需要准备充沛

正确的重定义顺序为：

创建中间表，不要建索引

执行start_redef_table

执行dmbs_stat.gather分析表

创建索引(此步一定要执行，不然sync会超慢，全表扫描)

执行sync

执行finish

大表重定义失败案例

大表重定义失败案例一：

之前我有对一张70G表的，重定义失败。当时花在start_redef_table为60分钟，但花在sync_interim_table确30分钟还没好，后来貌似是ctrl+c导致报错了(PS:当时知识欠火候，应该在等了30分钟没好的情况下，分析后台在做些什么样的操作)。

大表重定义失败案例二：

另外一位同事有对一张276G的表重定义。等了2个小时没响应就取消了。卡在start_redef_table这一步

苏说：估计会报TEMP表空间不足，我记得上次是最后一步报错的，就差一点就成功了 如果不是报temp不足的话

高说：当时追踪后台情况是：数据已经追平，后台在做一些排序的操作。占用temp，估计是一些内部处理。

事后对当时的建议操作：

徐(248169092) 15:57:38

你有没有观察tmp表的段增长速度

徐(248169092) 15:57:48

兴许已经完成200G了

高说(最新):之前的操作是先对中间表建好索引，然后在执行start,结果start花了2小时间还没成功

开始对300G表重定义吧

前言：现在预付卡有张240G的表需要分表，考虑到不好停业务的情形，但以前又有大表重定义失败的案例。特在客户端ogg库做下实验(该库即将下线，因此没业务但资源充沛)

我们现在要操作的这张表：

前期检查

查看表空间：

TABLESPACE_NAME

共G

已用G

已用%

剩余G

AUTOEXTENSIBLE

increment_by(M)

1

IDX_MEPF_B

1020

313.93

30.78

706.07

NO

0

7

TEMP

122

0

0

122

YES

0.625

9

UNDOTBS1

48

0.82

1.71

47.18

YES

5

我们只关心3类指标：TEMP、UNDOTBS1、和用户表空间(IDX_MEPF_B)

另外本库是一张没人使用的库

查看数据库归档，归档目录空间充裕

ok,相关的检查已经到位，符合实验

创建中间表

createtabletest_REDEFINE

(

dcoper_idNUMBER(10)notnull,

party_groupVARCHAR2(2),

serv_infnameVARCHAR2(50),

serv_gwnameVARCHAR2(128),

pfaddrVARCHAR2(256),

obj_codeVARCHAR2(50),

obj_valueVARCHAR2(500),

obj_code2VARCHAR2(50),

obj_value2VARCHAR2(500),

ret_codeVARCHAR2(10),

ret_infoVARCHAR2(512),

begin_dateDATEnotnull,

end_dateDATE,

statCHAR(3)notnull

)tablespaceIDX_MEPF_B;

开始重定义

EXEC dbms_redefinition.can_redef_table(user, 'REDEFINE'); --pass

2016/3/2510:34:30开始操作

[pcloud@khd-ctg-122-23 zy]$ vi cmd.sql

begin

dbms_redefinition.start_redef_table(uname      => 'ZY',

orig_table => 'REDEFINE',

int_table  => 'test_REDEFINE');

end;

/

sqlplus ZY/Zhouyun@172.17.1.1/orcl @cmd.sql&

这一步执行时间较长。

20160325 12:22执行sync

[pcloud@khd-ctg-122-23 zy]$ vi cmd2.sql

begin

dbms_redefinition.sync_interim_table(uname=>'ZY',

orig_table=>'REDEFINE',

int_table=>'TEST_REDEFINE');

end;

/

dbms_redefinition.sync_interim_table(uname=>'MEPF_DEV',orig_table=>'T_GWM_PKTCOLL',int_table=>'TEMP_T_GWM_PKTCOLL');

sqlplus ZY/Zhouyun@172.17.1.1/orcl @cmd2.sql&

25-3月-16 01.02.38.546下午sync结束

20160325 13：08建主键索引

[pcloud@khd-ctg-122-23 zy]$ vi cmd3.sql

create /*+parallel(16)*/ unique index PK_T_REDEFINE_TMP_812  on test_REDEFINE(DCOPER_ID, BEGIN_DATE) tablespace IDX_MEPF_B;

alter table test_REDEFINE

add constraint PK_T_REDEFINE_TMP_812 primary key (DCOPER_ID,  BEGIN_DATE)

using index

tablespace IDX_MEPF_B;

[pcloud@khd-ctg-122-23 zy]$ sqlplus ZY/Zhouyun@172.17.1.1/orcl  @cmd3.sql &

25-3月-16 01.36.14.248下午 索引建完

20160325 1349，在次模拟sync,insert100 update10条。结果是瞬间就完了

20160325 1353执行finish,瞬间完成

检查执行状态

2016/3/2510:34:30开始操作

select*fromutil_sessionwheresidin(1713) --1713,47975

2016/3/25 10:35语句执行完成

20160325 12:22执行sync

25-3月-16 01.02.38.546下午sync结束

20160325 13：08建主键索引

后台抓到的执行的语句

2016/3/2510:39现在这个点在执行以下语句,重定义是不占undo的

等待事件

Sql_id:fuptcd3hsm5w6

INSERT/*+  BYPASS_RECURSIVE_CHECK APPEND SKIP_UNQ_UNUSABLE_IDX */

INTO"ZY"."TEST_REDEFINE"

("DCOPER_ID",

"PARTY_GROUP",

"SERV_INFNAME",

"SERV_GWNAME",

"PFADDR",

"OBJ_CODE",

"OBJ_VALUE",

"OBJ_CODE2",

"OBJ_VALUE2",

"RET_CODE",

"RET_INFO",

"BEGIN_DATE",

"END_DATE",

"STAT")

SELECT"REDEFINE"."DCOPER_ID",

"REDEFINE"."PARTY_GROUP",

"REDEFINE"."SERV_INFNAME",

"REDEFINE"."SERV_GWNAME",

"REDEFINE"."PFADDR",

"REDEFINE"."OBJ_CODE",

"REDEFINE"."OBJ_VALUE",

"REDEFINE"."OBJ_CODE2",

"REDEFINE"."OBJ_VALUE2",

"REDEFINE"."RET_CODE",

"REDEFINE"."RET_INFO",

"REDEFINE"."BEGIN_DATE",

"REDEFINE"."END_DATE",

"REDEFINE"."STAT"

FROM"ZY"."REDEFINE" "REDEFINE"

2016/3/25 11:18还是在执行这条，等待事件direct path write

25-3月-16 11.50.23.771上午语句执行完成，start过程执行完成了

20160325 1228执行sync捕获到的后台代码

Sql_id:35f64tfcknmp6，这一步是在删除中间表含mlog$的数据，mlog$是我们更新数据的记录表。

因为all_summap记录了scn号，所以这个操作应该只是检查下，其它是没有数据删除的。但由于TEST_REDEFINE没建索引，导致这一步耗时了20分钟

DELETEFROM"ZY"."TEST_REDEFINE"  SNAP$

WHERE("DCOPER_ID","BEGIN_DATE")IN

(SELECTDISTINCTMLOG$."DCOPER_ID",MLOG$."BEGIN_DATE"

FROM"ZY"."MLOG$_REDEFINE"  MLOG$,

ALL_SUMMAP                                 MAP$

WHEREMLOG$.XID$$=MAP$.XID

ANDMAP$.COMMIT_SCN>:1

ANDMAP$.COMMIT_SCN<=:2

AND("DMLTYPE$$"!='I'))

20160325 1233

居然要15分钟，我这边更改的数据才

100条insert 10条update(已提交)

10条update(未提交)

为什么会这么慢呢？我们看语句是，会不会是缺少索引才这么慢的。那么下一步，我们建好索引在同步下

20160325 1245语句变成了merge操作，这是实际的更新数据了

Sqlid:58h1udfyqy0jb

/*  MV_REFRESH (MRG) */

MERGEINTO"ZY"."TEST_REDEFINE"  "SNA$"

USING(SELECTCURRENT$."DCOPER_ID",

CURRENT$."PARTY_GROUP",

CURRENT$."SERV_INFNAME",

CURRENT$."SERV_GWNAME",

CURRENT$."PFADDR",

CURRENT$."OBJ_CODE",

CURRENT$."OBJ_VALUE",

CURRENT$."OBJ_CODE2",

CURRENT$."OBJ_VALUE2",

CURRENT$."RET_CODE",

CURRENT$."RET_INFO",

CURRENT$."BEGIN_DATE",

CURRENT$."END_DATE",

CURRENT$."STAT"

FROM(SELECT"REDEFINE"."DCOPER_ID"    "DCOPER_ID",

"REDEFINE"."PARTY_GROUP"  "PARTY_GROUP",

"REDEFINE"."SERV_INFNAME"  "SERV_INFNAME",

"REDEFINE"."SERV_GWNAME"  "SERV_GWNAME",

"REDEFINE"."PFADDR"       "PFADDR",

"REDEFINE"."OBJ_CODE"     "OBJ_CODE",

"REDEFINE"."OBJ_VALUE"    "OBJ_VALUE",

"REDEFINE"."OBJ_CODE2"    "OBJ_CODE2",

"REDEFINE"."OBJ_VALUE2"   "OBJ_VALUE2",

"REDEFINE"."RET_CODE"     "RET_CODE",

"REDEFINE"."RET_INFO"     "RET_INFO",

"REDEFINE"."BEGIN_DATE"   "BEGIN_DATE",

"REDEFINE"."END_DATE"     "END_DATE",

"REDEFINE"."STAT"         "STAT"

FROM"ZY"."REDEFINE"  "REDEFINE")CURRENT$,

(SELECTDISTINCTMLOG$."DCOPER_ID",MLOG$."BEGIN_DATE"

FROM"ZY"."MLOG$_REDEFINE"  MLOG$,

ALL_SUMMAP                                 MAP$

WHEREMLOG$.XID$$=MAP$.XID--xid上有建索引

ANDMAP$.COMMIT_SCN>:1

ANDMAP$.COMMIT_SCN<=:2

AND("DMLTYPE$$"!='D'))LOG$

WHERECURRENT$."DCOPER_ID"=LOG$."DCOPER_ID"

ANDCURRENT$."BEGIN_DATE"=LOG$."BEGIN_DATE")"AV$"

ON("SNA$"."DCOPER_ID"="AV$"."DCOPER_ID"AND"SNA$"."BEGIN_DATE"="AV$"."BEGIN_DATE")

WHENMATCHEDTHEN

UPDATE

SET"SNA$"."DCOPER_ID"="AV$"."DCOPER_ID",

"SNA$"."PARTY_GROUP"="AV$"."PARTY_GROUP",

"SNA$"."SERV_INFNAME"="AV$"."SERV_INFNAME",

"SNA$"."SERV_GWNAME"="AV$"."SERV_GWNAME",

"SNA$"."PFADDR"="AV$"."PFADDR",

"SNA$"."OBJ_CODE"="AV$"."OBJ_CODE",

"SNA$"."OBJ_VALUE"="AV$"."OBJ_VALUE",

"SNA$"."OBJ_CODE2"="AV$"."OBJ_CODE2",

"SNA$"."OBJ_VALUE2"="AV$"."OBJ_VALUE2",

"SNA$"."RET_CODE"="AV$"."RET_CODE",

"SNA$"."RET_INFO"="AV$"."RET_INFO",

"SNA$"."BEGIN_DATE"="AV$"."BEGIN_DATE",

"SNA$"."END_DATE"="AV$"."END_DATE",

"SNA$"."STAT"="AV$"."STAT"

WHENNOTMATCHEDTHEN

INSERT

(SNA$."DCOPER_ID",

SNA$."PARTY_GROUP",

SNA$."SERV_INFNAME",

SNA$."SERV_GWNAME",

SNA$."PFADDR",

SNA$."OBJ_CODE",

SNA$."OBJ_VALUE",

SNA$."OBJ_CODE2",

SNA$."OBJ_VALUE2",

SNA$."RET_CODE",

SNA$."RET_INFO",

SNA$."BEGIN_DATE",

SNA$."END_DATE",

SNA$."STAT")

VALUES

(AV$."DCOPER_ID",

AV$."PARTY_GROUP",

AV$."SERV_INFNAME",

AV$."SERV_GWNAME",

AV$."PFADDR",

AV$."OBJ_CODE",

AV$."OBJ_VALUE",

AV$."OBJ_CODE2",

AV$."OBJ_VALUE2",

AV$."RET_CODE",

AV$."RET_INFO",

AV$."BEGIN_DATE",

AV$."END_DATE",

AV$."STAT")

表空间

2016/3/25 10:43表空间：

数据文件占用了，但temp还没动

2016/3/25 11:21 temp和undo依旧没有占用

2016/3/25 11:50语句执行完成

20160325 1318建索引这步，temp吃掉了20g来了，索引是66g大小(建索引对temp要求较大)

1322表空间达到了47g,我怀疑分析表local分区大小会小点，按分区来的

1324 temp

1326 temp已不在增加，同时idx_mepf_b开始减少空间，说明数据正temp读取到表空间

这步可以通过并发减少及等待事件可以看出

1328索引数据落盘中

1333

1337索引建完

表大小

2016/3/25 10:44临时表空间持续在增加,现在是花了10分钟，大概完成了1/10多，预计在100分之内完成

2016/3/25 11:01

2016/3/25 11:17

20160325 11：25

20160325 11：50

20160325 1330此时在建索引，但segment查不到索引的大小变化

表的定义变化

2016/3/2510:35:30

我们通过点击TEST_REDEFINE，可以看到TEST_REDEFINE其实是张物化视图，映射到了REDEFINE

CREATEMATERIALIZEDVIEWTEST_REDEFINE

ONPREBUILTTABLE

REFRESHFASTONDEMAND

AS

SELECT"REDEFINE"."DCOPER_ID"  "DCOPER_ID","REDEFINE"."PARTY_GROUP"  "PARTY_GROUP","REDEFINE"."SERV_INFNAME"  "SERV_INFNAME","REDEFINE"."SERV_GWNAME"  "SERV_GWNAME","REDEFINE"."PFADDR"  "PFADDR","REDEFINE"."OBJ_CODE"  "OBJ_CODE","REDEFINE"."OBJ_VALUE"  "OBJ_VALUE","REDEFINE"."OBJ_CODE2"  "OBJ_CODE2","REDEFINE"."OBJ_VALUE2"  "OBJ_VALUE2","REDEFINE"."RET_CODE"  "RET_CODE","REDEFINE"."RET_INFO"  "RET_INFO","REDEFINE"."BEGIN_DATE"  "BEGIN_DATE","REDEFINE"."END_DATE"  "END_DATE","REDEFINE"."STAT" "STAT"FROM"ZY"."REDEFINE"  "REDEFINE";

但在dba_objects中查看到的定义仍是table,怀疑他这种定义只是个临时的物化视图，字典无法查到

另外，我们抓到一张表MLOG$_的表

select*fromdba_objectswhereobject_namelike'%MLOG$_REDEFINE%';

OWNER

OBJECT_NAME

SUBOBJECT_NAME

OBJECT_ID

DATA_OBJECT_ID

OBJECT_TYPE

CREATED

LAST_DDL_TIME

TIMESTAMP

STATUS

TEMPORARY

GENERATED

SECONDARY

NAMESPACE

EDITION_NAME

1

ZY

MLOG$_REDEFINE

173082

173082

TABLE

2016/3/25 10:34:31

2016/3/25 10:34:31

2016-03-25:10:34:31

VALID

N

N

N

1

20160325 11：50

定义还是物化视图

20160325 1321趁着在建索引，我们看下mlog$表，该表刚刚在sync后。数据已经清空了

归档

2016/3/25 10:49

最新的归档,归档占用了61个g来了

2016/3/25 10:55

20160325 11：24

20160325 11：29

20160325 11：50归档的占用差不多是一比一

模拟生产，对源表产生修改

20160325 11:44执行

insertintoREDEFINEselect*fromT_REDEFINE_oggwhererownum<=100;

updateREDEFINEpartition(SYS_P1231)setobj_code2='zy test'wheredcoper_idin(3874113719,

3874113721,

3874113731,

3874113733,

3874113736,

3874113741,

3874113744,

3874113755,

3874113756,

3874113757);

commit;

20160325 12:21执行，但不提交，查看数据是否会同步

事后补充：20160325 1255:通过查看mlog$，发现没有提交的数据没在这个表里，事后在没commit的情况下，查询中间表，数据也是没同步的

updateREDEFINEpartition(SYS_P1231)setobj_code2='zy test1'wheredcoper_idin(3874113719,

3874113721,

3874113731,

3874113733,

3874113736,

3874113741,

3874113744,

3874113755,

3874113756,

3874113757);

20160325 12：49，我们查看mlog$表，发现刚刚commit更新的数据记录在了该表。由此可以肯定MLOG$表是记录执行期间变化的表。正好110条

1347在建好索引后，在次模拟速度

insertintoREDEFINEselectdcoper_id,

party_group,

serv_infname,

serv_gwname,

pfaddr,

obj_code,

obj_value,

obj_code2,

obj_value2,

ret_code,

ret_info,

begin_date,

end_date,

stat

from(selectt.*,rownumrnfromT_REDEFINE_ogg twhererownum<=200)wherern>100;

updateREDEFINEpartition(SYS_P1231)setobj_code2='zy test1'wheredcoper_idin(3874113719,

3874113721,

3874113731,

3874113733,

3874113736,

3874113741,

3874113744,

3874113755,

3874113756,

3874113757);

总结

通过本实验，证明重定义是可以操作大表的。其原理也就是通过insert/*+ BYPASS_RECURSIVE_CHECK APPEND SKIP_UNQ_UNUSABLE_IDX */这种不会产生undo

重定义需要的数据表空间、TEMP表空间、归档空间较多，在重定义之前需要准备充沛

正确的重定义顺序为：

创建中间表，不要建索引

执行start_redef_table

执行dmbs_stat.gather分析表

创建索引(此步一定要执行，不然sync会超慢，全表扫描)

执行sync

执行finish