本文为墨天轮数据库管理服务团队原创内容，如需转载请联系小墨（VX：modb666）并注明来源。
作者：张程
墨天轮数据库管理服务Oracle技术顾问
MySQL OCP，曾就职于软件、保险等行业。目前主要从事Oracle数据库审核和优化的工作。熟悉Oracle方面的SQL和性能优化、熟悉MySQL、达梦以及Mogdb、HANA等非O类的数据库。
-
第一章 案例总结概要 第二章 创建测试案例并测试效率
2.1 创建相关表结构
2.2 在线重定义效率测试
2.3 效率提升方案测试（并行）
2.4 单独创建索引以提升效率
第三章 最终调整方案
3.1 开启并行提升同步效率
3.2 单独并行建索引，仅同步依赖
3.3 完成同步后交换索引名
第一章 案例总结概要
背景和目的
在线重定义的效率瓶颈
首先分析当前步骤的主要耗时部分：
该步骤会同步原表中的数据到目标临时表，受限于IO效率。一般不会太快。
该步骤会基于索引情况，占用相对更多的时间。
根据改造过程中产生的增量数据大小，会占用一定的时间做增量数据同步。
如果前面同步增量数据了，该步骤一般不会占用太久时间。只会在切换表的瞬间锁定一下业务表并完成切换动作。
基于上面分析，对大表在线重定义时，一般主要慢在第1-2步骤。如下展示：
EXEC DBMS_REDEFINITION.START_REDEF_TABLE('OWNER','TABLE_A','TABLE_A_TEMP');
Elapsed:00:22:46.54
var num_errors number;
EXEC DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('OWNER','TABLE_A','TABLE_A_TEMP',DBMS_REDEFINITION.CONS_ORIG_PARAMS,TRUE,TRUE,TRUE,TRUE,:num_errors);
Elapsed:00:58:34.65
EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('OWNER','TABLE_A','TABLE_A_TEMP');
Elapsed:00:00:22.29
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('OWNER','TABLE_A','TABLE_A_TEMP');
Elapsed:00:00:03.68
第二章 创建测试案例并测试效率
本章节创建测试表及案例，来演示上述改造中的效率问题。
创建相关表结构
--创建改造目标表
CREATE TABLE TEST_ZHANGC AS SELECT * FROM DBA_OBJECTS WHERE 1=2;
--创建序列，模拟主键的插入
create sequence SEQ_00001
MINVALUE 1 MAXVALUE 999999999999999999999
START WITH 1 INCREMENT BY 1 CACHE 10000;
--模拟插入大量数据
INSERT INTO TEST_ZHANGC(OBJECT_ID,OWNER,OBJECT_NAME,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME)
SELECT SEQ_00001.NEXTVAL,OWNER,OBJECT_NAME,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME FROM DBA_OBJECTS;
--执行N次增大数据量
INSERT INTO TEST_ZHANGC(OBJECT_ID,OWNER,OBJECT_NAME,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME)
SELECT SEQ_00001.NEXTVAL,OWNER,OBJECT_NAME,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME FROM TEST_ZHANGC;
commit;
--创建主键及普通索引
CREATE UNIQUE INDEX TEST_ZHANGC_PK ON TEST_ZHANGC(OBJECT_ID) TABLESPACE USERS;
ALTER TABLE TEST_ZHANGC add constraint TEST_ZHANGC_PK PRIMARY KEY(OBJECT_ID) USING INDEX TEST_ZHANGC_PK;
DROP INDEX IDX_ZHANGC_MULT;
CREATE INDEX IDX_ZHANGC_MULT ON TEST_ZHANGC(OBJECT_NAME,OBJECT_TYPE,CREATED,LAST_DDL_TIME)TABLESPACE USERS PARALLEL 4;
ALTER INDEX IDX_ZHANGC_MULT NOPARALLEL;
--创建待改造临时表
CREATE TABLE TEST_ZHANGC_PAR
( "OWNER" VARCHAR2(30),
"OBJECT_NAME" VARCHAR2(128),
"SUBOBJECT_NAME" VARCHAR2(30),
"OBJECT_ID" NUMBER,
"DATA_OBJECT_ID" NUMBER,
"OBJECT_TYPE" VARCHAR2(19),
"CREATED" DATE,
"LAST_DDL_TIME" DATE,
"TIMESTAMP" VARCHAR2(19),
"STATUS" VARCHAR2(7),
"TEMPORARY" VARCHAR2(1),
"GENERATED" VARCHAR2(1),
"SECONDARY" VARCHAR2(1),
"NAMESPACE" NUMBER,
"EDITION_NAME" VARCHAR2(30)
)
PARTITION BY RANGE("CREATED") INTERVAL(NUMTOYMINTERVAL(1,'YEAR'))
(partition "P_2020" VALUES LESS THAN(TO_DATE('2021-02-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) TABLESPACE USERS,
partition "P_2021" VALUES LESS THAN(TO_DATE('2022-02-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) TABLESPACE USERS,
partition "P_2022" VALUES LESS THAN(TO_DATE('2023-02-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) TABLESPACE USERS,
partition "P_2023" VALUES LESS THAN(TO_DATE('2024-02-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) TABLESPACE USERS,
partition "P_2024" VALUES LESS THAN(TO_DATE('2025-02-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) TABLESPACE USERS
);
col segment_name for a30
select segment_name,bytes/1024/1024 as size_m from dba_segments where segment_name LIKE '%ZHANGC%'
ORDER BY 2 DESC;
SEGMENT_NAME SIZE_M
------------------------------ ----------
TEST_ZHANGC 648.125
IDX_ZHANGC_MULT 389.5
TEST_ZHANGC_PK 104
在线重定义效率测试
EXEC DBMS_REDEFINITION.START_REDEF_TABLE('SZT','TEST_ZHANGC','TEST_ZHANGC_PAR');
Elapsed:00:00:16.30
var num_errors number;
EXEC DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('SZT','TEST_ZHANGC','TEST_ZHANGC_PAR',DBMS_REDEFINITION.CONS_ORIG_PARAMS,TRUE,TRUE,TRUE,TRUE,:num_errors);
Elapsed:00:00:57.86
INSERT INTO TEST_ZHANGC(OBJECT_ID,OWNER,OBJECT_NAME,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME)
SELECT SEQ_00001.NEXTVAL,OWNER,OBJECT_NAME,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME FROM TEST_ZHANGC;
EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('SZT','TEST_ZHANGC','TEST_ZHANGC_PAR');
Elapsed:00:01:04.77
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('SZT','TEST_ZHANGC','TEST_ZHANGC_PAR');
Elapsed:00:00:01.24
效率提升方案测试（并行）
ALTER SESSION FORCE PARALLEL DML PARALLEL 4;
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 4;
EXEC DBMS_REDEFINITION.START_REDEF_TABLE('SZT','TEST_ZHANGC','TEST_ZHANGC_PAR');
Elapsed:00:00:10.14
SID EVENT SQL_ID INST_ID PLAN_HASH_VALUE EXECS AVG_ELA LAST_ELA SQL_TEXT
------------ --------------- ---------------- ---------- --------------- -------- ---------- --------
125,5 PX Deq: Execute 0qkn41as4rcgg,0 1 4070584540 0 16.90 9 INSERT /*+ BYPASS_RECURSIVE_CHECK APPEND
Plan hash value: 4070584540
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 6257 (100)| | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 4778K| 943M| 6257 (1)| 00:01:16 | Q1,01 | P->S | QC (RAND) |
| 3 | LOAD AS SELECT | | | | | | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 4778K| 943M| 6257 (1)| 00:01:16 | Q1,01 | PCWP | |
| 5 | PX SEND RANDOM LOCAL| :TQ10000 | 4778K| 943M| 6257 (1)| 00:01:16 | Q1,00 | P->P | RANDOM LOCA|
| 6 | PX BLOCK ITERATOR | | 4778K| 943M| 6257 (1)| 00:01:16 | Q1,00 | PCWC | |
|* 7 | TABLE ACCESS FULL | TEST_ZHANGC | 4778K| 943M| 6257 (1)| 00:01:16 | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access(:Z>=:Z AND :Z<=:Z)
var num_errors number;
EXEC DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('SZT','TEST_ZHANGC','TEST_ZHANGC_PAR',DBMS_REDEFINITION.CONS_ORIG_PARAMS,TRUE,TRUE,TRUE,TRUE,:num_errors);
Elapsed:00:01:02.80
CREATE UNIQUE INDEX "TMP$$_TEST_ZHANGC_PK0" ON "TEST_ZHANGC_PAR"(OBJECT_ID);
CREATE INDEX "TMP$$_IDX_ZHANGC_MULT" ON "TEST_ZHANGC_PAR"(OBJECT_NAME,OBJECT_TYPE,CREATED,LAST_DDL_TIME);
EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('SZT','TEST_ZHANGC','TEST_ZHANGC_PAR');
Elapsed:00:00:47.30
Elapsed:00:00:49.67
SID EVENT SQL_ID INST_ID PLAN_HASH_VALUE EXECS AVG_ELA LAST_ELA SQL_TEXT
------------ --------------- ---------------- ---------- --------------- -------- ---------- --------
125,5 PX Deq: Execute 6ctcqj63r3h2w,0 1 3561775107 0 .20 13 /* MV_REFRESH (MRG) */ MERGE INTO
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('SZT','TEST_ZHANGC','TEST_ZHANGC_PAR');
Elapsed:00:00:01.33
Elapsed:00:00:02.12
单独创建索引以提升效率
CREATE UNIQUE INDEX "TMP$$_TEST_ZHANGC_PK0" ON "TEST_ZHANGC_PAR"(OBJECT_ID) parallel 4;
alter index "TMP$$_TEST_ZHANGC_PK0" noparallel;
Elapsed:00:00:05.15
CREATE INDEX "TMP$$_IDX_ZHANGC_MULT" ON "TEST_ZHANGC_PAR"(OBJECT_NAME,OBJECT_TYPE,CREATED,LAST_DDL_TIME) parallel 4;
alter index "TMP$$_IDX_ZHANGC_MULT" noparallel;
Elapsed:00:00:15.35
var num_errors number;
EXEC DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('SZT','TEST_ZHANGC','TEST_ZHANGC_PAR',0,TRUE,TRUE,TRUE,TRUE,:num_errors);
Elapsed:00:00:08.12
Elapsed:00:00:08.26
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('SZT','TEST_ZHANGC','TEST_ZHANGC_PAR');
Elapsed: 00:00:13.96
col constraint_name for a30
col constraint_type for a20
col r_constraint_name for a30
col index_name for a30
col TABLE_NAME for a20
select table_name,constraint_name,constraint_type,r_constraint_name,index_name from dba_constraints
where table_name in('TEST_ZHANGC','TEST_ZHANGC_PAR');
TABLE_NAME CONSTRAINT_NAME CONSTRAINT_TYPE R_CONSTRAINT_NAME INDEX_NAME
-------------------- ------------------------------ -------------------- ------------------------------ ------------------------------
TEST_ZHANGC TEST_ZHANGC_PK P TMP$$_TEST_ZHANGC_PK0
TEST_ZHANGC_PAR TMP$$_TEST_ZHANGC_PK0 PTEST_ZHANGC_PK
ALTER INDEX TEST_ZHANGC_PK RENAME TO TMP_TEST_ZHANGC_PK;
ALTER INDEX IDX_ZHANGC_MULT RENAME TO TMP_IDX_ZHANGC_MULT;
Elapsed: 00:00:00.12
ALTER INDEX "TMP$$_TEST_ZHANGC_PK0" RENAME TO TEST_ZHANGC_PK;
ALTER INDEX "TMP$$_IDX_ZHANGC_MULT" RENAME TO IDX_ZHANGC_MULT;
Elapsed: 00:00:00.05
第三章 最终调整方案
开启并行提升同步效率
ALTER SESSION FORCE PARALLEL DML PARALLEL 4;
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 4;
单独并行建索引，仅同步依赖
CREATE UNIQUE INDEX "TMP$$_TEST_ZHANGC_PK0" ON "TEST_ZHANGC_PAR"(OBJECT_ID) parallel 4;
alter index "TMP$$_TEST_ZHANGC_PK0" noparallel;
CREATE INDEX "TMP$$_IDX_ZHANGC_MULT" ON "TEST_ZHANGC_PAR"(OBJECT_NAME,OBJECT_TYPE,CREATED,LAST_DDL_TIME) parallel 4;
alter index "TMP$$_IDX_ZHANGC_MULT" noparallel;
var num_errors number;
EXEC DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('SZT','TEST_ZHANGC','TEST_ZHANGC_PAR',0,TRUE,TRUE,TRUE,TRUE,:num_errors);
完成同步后交换索引名
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('SZT','TEST_ZHANGC','TEST_ZHANGC_PAR');
ALTER INDEX TEST_ZHANGC_PK RENAME TO TMP_TEST_ZHANGC_PK;
ALTER INDEX IDX_ZHANGC_MULT RENAME TO TMP_IDX_ZHANGC_MULT;
ALTER INDEX "TMP$$_TEST_ZHANGC_PK0" RENAME TO TEST_ZHANGC_PK;
ALTER INDEX "TMP$$_IDX_ZHANGC_MULT" RENAME TO IDX_ZHANGC_MULT;
THE END
墨天轮从乐知乐享的数据库技术社区蓄势出发，全面升级，提供多类型数据库管理服务。墨天轮数据库管理服务旨在为用户构建信赖可托付的数据库环境，并为数据库厂商提供中立的生态支持。
服务官网：https://www.modb.pro/service
点击蓝字进入作者个人主页
本文分享自微信公众号 - 墨天轮（enmocs）。
如有侵权，请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。
