Oracle在线重定义参考操作

bisal的个人杂货铺 • 阅读原文 • • 文档

Oracle数据库提供了很多贴合实际的技术方案，这些在我们设计自己的应用软件时都可以进行参考，像在线重定义这个技术，主要应用于需要对数据库结构进行调整的场景，尤其是在要求高可用性和最小化停机的情况下，它允许我们在不影响业务操作的情况下对数据库表、索引、约束等对象进行变更，减少了因为结构性变更而造成的服务中断，简单讲，它就是为最小化应用程序停机时间、提高可用性和降低对生产环境的影响而设计的。

在线重定义的使用场景主要包括如下：

1. 表结构修改

  • 增加或删除列：可以在线地增加新列或删除不再需要的列，而无需锁住表或停机维护。
  • 修改列的数据类型：例如，改变列的数据类型大小，或将字符类型更改为数字类型等，通常在系统活跃时执行，减少停机时间。
  • 重命名列：可以重命名表的列，而不影响数据访问。

2. 数据分区管理

  • 表分区：对于大型表，通过在线重定义技术可以重新分区表，将表分成更小的分区，以提高查询性能。
  • 分区操作：包括添加、删除或重新组织分区，适用于数据量非常大的场景。
  • 合并或拆分分区：在生产环境中重新组织分区，减少系统停机，确保业务持续运行。

3. 索引管理

  • 重建索引：可以在线重建表的索引，优化查询性能，而不影响对表的访问。
  • 改变索引的结构：修改索引的存储方式或类型，比如将单列索引改为复合索引，或者改变索引的存储分区。

4. 约束与触发器修改

  • 修改或添加约束：例如添加唯一约束、外键约束等，通常不会导致表锁定，可以在活跃环境中进行。
  • 更新触发器：触发器的修改也可以在线进行，减少对应用程序的干扰。

5. 表数据迁移与重组织

  • 表重组：通过在线重定义技术，可以将表的数据存储结构重新安排，使得表更加优化，减少数据碎片。
  • 数据迁移：在数据库表的重定义过程中，可以将数据迁移到新表或不同的存储结构中，同时确保数据一致性。

6. 高可用性与最小化停机

  • 最小化维护窗口：在线重定义技术可以减少数据库表结构变更过程中所需的停机时间，使得系统可以24/7不间断运行。
  • 提高生产环境的可用性：避免了传统重定义方式带来的长时间锁表问题，使得应用程序可以在表重定义的过程中继续正常运行。

7. 数据转换与数据质量改进

  • 数据类型变更和数据清洗：在数据仓库和数据湖的环境中，经常需要对数据结构进行变更。Oracle在线重定义技术允许在表结构变更的同时，进行数据清洗或数据类型的转换，而不影响数据访问。

8. 升级和迁移

  • 版本升级和迁移：在数据库版本升级过程中，通常会涉及表结构的调整。使用在线重定义技术，可以使得这些变更不影响应用程序的运行，支持平滑升级。

技术社群的这篇文章给出了在线重定义的操作过程，可以自行尝试。原文链接：https://mp.weixin.qq.com/s/wFD9f6rzoF2Ud0jwR9YxGA

第一章 案例总结概要

1.1

背景和目的基于前期对部分库中的大表做分区改造的过程中，发现随着表体检的增大（超过200GB），分区改造的时间影响较大。如果某些场景对IO开销或维护窗口有时间限制，就要求改造过程（在线重定义的时间）尽可能短。尽管在线重定义大多数时间是不影响原始业务表的，但其仍然会占用较长时间对整体IO造成压力。因此总结本篇的目的，是希望找出更高效率的在线重定义步骤。以期在需要较短时间内完成改造工作时，可以提供帮助。

1.2

在线重定义的效率瓶颈

首先分析当前步骤的主要耗时部分：1、同步数据DBMS_REDEFINITION.START_REDEF_TABLE：

该步骤会同步原表中的数据到目标临时表，受限于IO效率。一般不会太快。2、复制索引依赖DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS：

该步骤会基于索引情况，占用相对更多的时间。3、同步增量数据DBMS_REDEFINITION.SYNC_INTERIM_TABLE：

根据改造过程中产生的增量数据大小，会占用一定的时间做增量数据同步。4、完成重定义DBMS_REDEFINITION.FINISH_REDEF_TABLE：

如果前面同步增量数据了，该步骤一般不会占用太久时间。只会在切换表的瞬间锁定一下业务表并完成切换动作。

基于上面分析，对大表在线重定义时，一般主要慢在第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

因此，为了提升上述改造的效率，首先想到的就是利用Oracle的并行去进行。后续章节会创建测试案例，来实际测试验证如何提升效率。

第二章 创建测试案例并测试效率

本章节创建测试表及案例，来演示上述改造中的效率问题。

2.1

创建相关表结构创建测试表如下：

--创建改造目标表
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

本例仅为了演示改造步骤的效率提升程度。

2.2

在线重定义效率测试基于上述测试表情况，模拟在线重定义改造为分区表的实际效率。1、同步表数据及索引约束：

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

2、模拟业务会话插入较多增量数据，测试增量同步效率：

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

3、完成在线重定义：

EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('SZT','TEST_ZHANGC','TEST_ZHANGC_PAR');
Elapsed:00:00:01.24

2.3

效率提升方案测试（并行）为了提升上述案例中出现的效率问题，初步的方案设想是利用并行提升数据同步效率。即同步前开启并行：

ALTER SESSION FORCE PARALLEL DML PARALLEL 4;
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 4;

下面演示开启并行的提升方案及效率：1、同步表数据：

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)

结论：开启并行后，确实能提升效率，相关同步进程也采用了并行处理。2、同步索引依赖：

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);

相应创建索引的步骤无法采用并行完成，而前述分析创建索引是同步依赖这部分的主要耗时步骤。因此这里需要继续分析其余方案。3、同步增量数据：

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

经过多次测试，开启并行后的执行时间略好于串行执行。执行计划的同步语句也确实使用到了并行，因此这部分也确认可以在并行中受益。4、最终完成重定义

EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('SZT','TEST_ZHANGC','TEST_ZHANGC_PAR');
Elapsed:00:00:01.33
Elapsed:00:00:02.12

前面同步了增量数据，这部分切换基本不再消耗太多时间。基于本章节的测试，开启并行动作仅能对第1、3步骤有提升。而在线重定义中最耗时的步骤为1-2。如果索引数量较多、体积较大。造成的第二步骤才是耗时的关键部分。需要继续分析其他方案。

2.4

单独创建索引以提升效率基于上述方案测试出的结论：并行操作仅能提升步骤1/3。步骤2主要耗时为同步索引且仅能串行操作。因此为了提升这部分效率：考虑将创建索引单独提出来，手动并行创建索引。第2步骤仅做依赖的同步。1、测试改造后的效率：

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

如上所述，可以单独并行创建索引，使用合适的并行度效率可控。同步依赖仅需要数秒钟完成。上述操作累计也仅需要30秒内。相比默认串行同步索引节省了一半的时间。2、手动创建索引带来的问题：手动创建索引提升了同步索引这部分的效率，但也带来了相应问题。创建完索引后，直至最终交换完成。

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          P                                                   TEST_ZHANGC_PK

手动创建的索引并不会随着第4步骤完成，做自动交换名称。而直接同步的索引则可以完成自动重命名切换。这部分问题，需要采取在完成重定义后手动做索引名称的命名交换。即：将临时表索引重命名为TMP字样。将手动创建的索引（已完成交换）重命名为原始目标表的索引名。

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

该步骤在没有持续占用对象的阻塞会话时，均可快速完成。即使有部分更新会话，只要会话不是持续占用对象，也可以在空闲时完成交换。

第三章 最终调整方案

3.1

开启并行提升同步效率

ALTER SESSION FORCE PARALLEL DML PARALLEL 4;
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 4;

步骤1和3操作方式不变，均能利用并行提升效率。

3.2

单独并行建索引，仅同步依赖

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);

3.3

完成同步后交换索引名

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;

手动创建的索引重命名为原始索引名称。在线重定义完成，利用上述调整步骤可以有效提升大表操作的效率。

声明：文中观点不代表本站立场。本文传送门：https://eyangzhen.com/423942.html

0

作者专栏

bisal的个人杂货铺的头像

bisal的个人杂货铺

317 文章
Oracle ACE（Alumni），TXY TVP，技术爱好者，足球爱好者，梦想是走遍世界上顶级球会的主场，在这个杂货铺中你会看到有关技术、足球、工作和生活各种有趣的文章、希望零碎的文字，让你有所收获，每个工作日的7:50，我们不见不散！ 欢迎关注微信公众号：bisal的个人杂货铺
联系我们
联系我们
分享本页
返回顶部