ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > Oracle在线重定义操作一张近300G的表的实验

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

原创 Oracle 作者：bestpaydata 时间：2016-03-25 19:43:42 0 删除 编辑

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

author:zy
目录：

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

总结

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

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

正确的重定义顺序为：

  1. 创建中间表，不要建索引
  2. 执行start_redef_table
  3. 执行dmbs_stat.gather分析表
  4. 创建索引（此步一定要执行，不然sync会超慢，全表扫描）
  5. 执行sync
  6. 执行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,相关的检查已经到位，符合实验

创建中间表

create table test_REDEFINE

(

  dcoper_id   NUMBER(10) not null,

  party_group VARCHAR2(2),

  serv_infname VARCHAR2(50),

  serv_gwname VARCHAR2(128),

  pfaddr      VARCHAR2(256),

  obj_code    VARCHAR2(50),

  obj_value   VARCHAR2(500),

  obj_code2   VARCHAR2(50),

  obj_value2  VARCHAR2(500),

  ret_code    VARCHAR2(10),

  ret_info    VARCHAR2(512),

  begin_date  DATE not null,

  end_date    DATE,

  stat        CHAR(3) not null

) tablespace IDX_MEPF_B;

开始重定义

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

2016/3/25 10: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/25 10:34:30开始操作

select * from util_session where sid in(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/25 10: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分钟

DELETE FROM "ZY"."TEST_REDEFINE"  SNAP$

WHERE ("DCOPER_ID",  "BEGIN_DATE") IN

       (SELECT DISTINCT  MLOG$."DCOPER_ID", MLOG$."BEGIN_DATE"

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

               ALL_SUMMAP                                 MAP$

         WHERE MLOG$.XID$$ = MAP$.XID

           AND MAP$.COMMIT_SCN > :1

           AND MAP$.COMMIT_SCN <= :2

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

20160325 1233

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

100条insert 10条update （已提交）

10条update(未提交)

为什么会这么慢呢？我们看语句是<("DCOPER_ID", "BEGIN_DATE") IN>，会不会是缺少索引才这么慢的。那么下一步，我们建好索引在同步下

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

Sqlid: 58h1udfyqy0jb

/*  MV_REFRESH (MRG) */

MERGE INTO "ZY"."TEST_REDEFINE"  "SNA$"

USING (SELECT  CURRENT$."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$,

              (SELECT DISTINCT  MLOG$."DCOPER_ID", MLOG$."BEGIN_DATE"

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

                      ALL_SUMMAP                                 MAP$

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

                  AND MAP$.COMMIT_SCN > :1

                  AND MAP$.COMMIT_SCN <= :2

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

        WHERE  CURRENT$."DCOPER_ID" = LOG$."DCOPER_ID"

          AND  CURRENT$."BEGIN_DATE" = LOG$."BEGIN_DATE") "AV$"

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

WHEN MATCHED THEN

  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"

WHEN NOT MATCHED THEN

  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/25 10:35:30

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

CREATE MATERIALIZED VIEW TEST_REDEFINE

ON PREBUILT TABLE

REFRESH FAST ON DEMAND

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 * from dba_objects where object_name like '%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执行

insert into REDEFINE select * from T_REDEFINE_ogg where rownum<=100;

update REDEFINE partition(SYS_P1231) set obj_code2='zy test'  where dcoper_id in(3874113719,

3874113721,

3874113731,

3874113733,

3874113736,

3874113741,

3874113744,

3874113755,

3874113756,

3874113757);

commit;

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

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

update REDEFINE partition(SYS_P1231) set obj_code2='zy test1'  where dcoper_id in(3874113719,

3874113721,

3874113731,

3874113733,

3874113736,

3874113741,

3874113744,

3874113755,

3874113756,

3874113757);

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

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

insert into REDEFINE select 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

from (select t.*,rownum rn from T_REDEFINE_ogg t where rownum<=200) where rn>100;

update REDEFINE partition(SYS_P1231) set obj_code2='zy test1'  where dcoper_id in(3874113719,

3874113721,

3874113731,

3874113733,

3874113736,

3874113741,

3874113744,

3874113755,

3874113756,

3874113757);

总结

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

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

正确的重定义顺序为：

  1. 创建中间表，不要建索引
  2. 执行start_redef_table
  3. 执行dmbs_stat.gather分析表
  4. 创建索引（此步一定要执行，不然sync会超慢，全表扫描）
  5. 执行sync
  6. 执行finish

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30109892/viewspace-2063931/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： Ansible API 2.0 研究
下一篇： Oracle Index Clustering Factor
请登录后发表评论 登录
全部评论
bestpaydata

注册时间：2015-01-19

  • 博文量
    126
  • 访问量
    1054947

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593