分析Oracle能否在2小时内在线完成表结构字段类型变更
这篇文章主要介绍“分析Oracle能否在2小时内在线完成表结构字段类型变更”,在日常操作中,相信很多人在分析Oracle能否在2小时内在线完成表结构字段类型变更问题上存在疑惑,小编查阅了各式资料,整理出简单好用的操作方法,希望对大家解答”分析Oracle能否在2小时内在线完成表结构字段类型变更”的疑惑有所帮助!接下来,请跟着小编一起来学习吧!
黄梅ssl适用于网站、小程序/APP、API接口等需要进行数据传输应用场景,ssl证书未来市场广阔!成为创新互联建站的ssl证书销售渠道,可以享受市场价格4-6折优惠!如果有意向欢迎电话联系或者加微信:18982081108(备注:SSL证书合作)期待与您的合作!
摘要:Oracle 12c 能否在2小时内在线完成一张14亿条记录的表结构字段类型变更
概述
前面分享过Oracle大表在线修改的脚本(在线重定义),经过几轮的测试发现,都存在些缺陷,效率始终不是很满意。这次把索引和统计信息拆出来后发现效率相对算是最高的。
在线重定义的目标,是对在线业务影响最小,通过最短的锁表时间来实现表结构的变更,锁表只发生在finish_redef_table过程中,正式切换前先执行sync_interim_table过程异步同步数据,以尽可能减少业务影响。
由于是要对客户的核心业务变更,按管理要求没办法提前执行finish_redef_table前的过程,且维护窗口时间有限,业务又不能完全停掉,才有了这次的测试。
主要测试常见的2种场景,如下:
场景1:
复制全部依赖 - COPY_TABLE_DEPENDENTS(索引 + 约束 + 统计信息),触发器和权限这种基本没有,就没有复制。优点:操作方便,脚本直接把原表所有依赖全部复制过去,改后的表直接使用,不需要额外处理,适合百万或千万的表,且对效率没要求可用。
缺点:上亿的表测试发现效率非常低。
场景2:
有主键的表只复制约束 - COPY_TABLE_DEPENDENTS(会复制主键和唯一索引),其它索引和统计信息等重定义完成后再开并行重建和收集,这里要补充说明一下为什么要复制约束,因为创建主键不能并行操作,等重定义完成数据转换后,相当于在普通大表上创建主键,效率非常低。优点:目前针对10亿以上的表测试发现效率是最高的,14亿的表全部弄完约2小时左右。
缺点:操作过程稍微麻烦一点,别的还好。
复制规则,如下:
copy_indexes => 0, copy_triggers => FALSE, copy_constraints => TRUE, copy_privileges => FALSE, ignore_errors => FALSE, num_errors => num_errors, copy_statistics => FALSE);
由于场景1的效率比较差,我这里就只列举场景2的测试过程,后续实际业务变更也是在场景2中进行,以下是整个变更过程:
硬软配置一般,如下:
CPU:Intel® Xeon® CPU E7-4820 v3 @ 1.90GHz(物理4个,10核心,80个逻辑cpu)
内存:500 GB
存储:华为某型号
数据库软件:Oracle 12.2 Nocdb RAC,未打补丁。
我们先看一下原表数据行数,接近14亿条,人工造的,表实际大小和生产相差1倍以上。
SQL> select /*+ parallel(40) */ count(*) from OM_OFFERING_INST_TEST; COUNT(*)----------1399999996 Elapsed: 00:00:17.39
创建临时表,有35个分区,部份省略了,主键、索引等都不要建。
CREATE TABLE "CUSTINFO"."INT_OM_OFFERING_INST_TEST" ( "BUSINESS_SEQ" VARCHAR2(20), "PROD_ID" NUMBER(20, 0), "OFFERING_INST_ID" NUMBER(20, 0), "OFFERING_ID" NUMBER(20, 0), "OFFERING_NAME" VARCHAR2(256), "OFFERING_CODE" VARCHAR2(50), "CUST_TYPE" VARCHAR2(20), "CUST_ID" NUMBER(20, 0), "BRAND" VARCHAR2(50), ...... "RECORD_STATUS" NUMBER(3, 0) DEFAULT 1) PARTITION BY LIST ( "BE_ID" ) ( PARTITION "P_000" VALUES ( '000' ), PARTITION "P_001" VALUES ( '001' ), PARTITION "P_002" VALUES ( '002' ) , PARTITION "P_100" VALUES ( '100' ) , PARTITION "P_200" VALUES ( '200' ) , ..........
定义参数,设置并行和行迁移
define USERNAME = 'CUSTINFO'; --用户名define SOURCE_TAB = 'OM_OFFERING_INST_TEST';-- 原表名define INT_TAB = 'INT_OM_OFFERING_INST_TEST';-- 临时表名,需要手工提前创建define PARALLELS = 35; --并行数,这里设的分区数alter session enable parallel dml ;alter session force parallel dml parallel &PARALLELS;alter session force parallel query parallel &PARALLELS;alter table &INT_TAB enable row movement; --临时表开启行迁移
检查原表是否支持在线重定义,比较快,仅用了1秒不到。
SQL> begin 2 dbms_redefinition.can_redef_table(uname => '&USERNAME', 3 tname => '&SOURCE_TAB', 4 options_flag => DBMS_REDEFINITION.CONS_USE_PK); 5 end; 6 / PL/SQL procedure successfully completed Executed in 0.027 seconds
映射字段类型,启动重定义进程,用了近10分钟,稍微有点慢。从这里开始到结束, 如果中途有错误,想要重来,需要调abort_redef_table过程取消任务。
SQL> set timing on; SQL> begin 2 DBMS_REDEFINITION.START_REDEF_TABLE(uname => '&USERNAME', 3 orig_table => '&SOURCE_TAB', 4 int_table => '&INT_TAB', 5 col_mapping => 'to_number(owner_party_role_id) owner_party_role_id, 7 to_number(offering_inst_id) offering_inst_id, 8 to_number(subs_id) subs_id, 9 to_number(group_id) group_id, 10 to_number(apply_obj_id) apply_obj_id', --这里只列举了需要变更的字段类型 11 options_flag => DBMS_REDEFINITION.CONS_USE_PK); 12 end; 13 / PL/SQL procedure successfully completed Executed in 576.565 seconds
复制依赖对象,这里只复制了主键约束,耗时54分钟,如果全部复制,我在测试跑了3个小时没有结果,只接Kill了。
SQL> DECLARE 2 num_errors PLS_INTEGER; 3 BEGIN 4 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname => '&USERNAME', 5 orig_table => '&SOURCE_TAB', 6 int_table => '&INT_TAB', 7 copy_indexes => 0, 8 copy_triggers => FALSE, 9 copy_constraints => TRUE, 10 copy_privileges => FALSE, 11 ignore_errors => FALSE, 12 num_errors => num_errors, 13 copy_statistics => FALSE); 14 END; 15 / PL/SQL procedure successfully completed Executed in 3230.441 seconds
异步同步数据,耗时28秒,比较快。
SQL> begin 2 dbms_redefinition.sync_interim_table(uname => '&USERNAME', 3 orig_table => '&SOURCE_TAB', 4 int_table => '&INT_TAB'); 5 end; 6 / PL/SQL procedure successfully completed Executed in 27.908 seconds
完成在线重定义,结束任务,耗时73秒,也是比较快。
SQL> begin 2 dbms_redefinition.finish_redef_table(uname => '&USERNAME', 3 orig_table => '&SOURCE_TAB', 4 int_table => '&INT_TAB'); 5 end; 6 / PL/SQL procedure successfully completed Executed in 72.302 seconds
创建索引,这个分区表上的索引不多,就3个普通索引,开53个并行,平均每个耗时4分钟左右,累计13分钟。
SQL> CREATE INDEX "CUSTINFO"."INX_OM_OFFERING_INST_TEST_CUSTID" ON "CUSTINFO"."OM_OFFERING_INST_TEST" ("CUST_ID") online parallel 35; Index created Executed in 257.138 seconds SQL> CREATE INDEX "CUSTINFO"."INX_OM_OFFERING_INST_TEST_GROUPID" ON "CUSTINFO"."OM_OFFERING_INST_TEST" ("GROUP_ID") online parallel 35; Index created Executed in 244.853 seconds SQL> CREATE INDEX "CUSTINFO"."INX_OM_OFFERING_INST_TEST_SUBSID" ON"CUSTINFO"."OM_OFFERING_INST_TEST" ("SUBS_ID") online parallel 35; Index created Executed in 261.665 seconds
收集统计信息,同样也是开35个并行,耗时4分钟左右。CASCADE => true表示收集表、列、索引等。
SQL> exec dbms_stats.gather_table_stats(ownname => 'CUSTINFO',tabname => 'OM_OFFERING_INST_TEST',CASCADE => true,degree => 35); PL/SQL procedure successfully completed. Elapsed: 00:04:18.35
取消表、索引上的并行度,检查字段是否修改成功,删除临时表,至此整个修改过程结束,这里耗时约10分钟左右。
--取消表上的并行alter table &SOURCE_TAB noparallel;--取消索引上的并行alter index INX_OM_OFFERING_INST_TEST_CUSTID noparallel;alter index INX_OM_OFFERING_INST_TEST_GROUPID noparallel;alter index INX_OM_OFFERING_INST_TEST_SUBSID noparallel; --删除临时表drop table &INT_TAB;
到此,关于“分析Oracle能否在2小时内在线完成表结构字段类型变更”的学习就结束了,希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习,快去试试吧!若想继续学习更多相关知识,请继续关注创新互联网站,小编会继续努力为大家带来更多实用的文章!
分享标题:分析Oracle能否在2小时内在线完成表结构字段类型变更
链接URL:http://pwwzsj.com/article/gggecg.html