在线重定义“巧改”分区表

作者介绍:

曾令军,云和恩墨技术专家,2009年开始接触ORACLE数据库,8年数据库运维经验。思维敏捷,擅长于数据库开发、解决棘手的数据库故障和性能问题。服务于公司华南区多个客户,曾参与过国内多家股份制银行、城市商业银行的核心业务系统、数据仓库的部署建设和生产运维工作,在数据库故障诊断、运维监控、性能优化方面积累了丰富的经验。

什么是在线重定义

要了解什么是在线重定义技术,我想从表分区开始说起。在生产系统运维过程中,经常遇到的一个需求是如何把一个数据量非常大的普通表改造成分区表。分区最早在oracle8.0版本引入,支持将一个表或索引物理地分解为多个更小、更可管理的部分。

好处:

  • 提高数据的可用性
  • 数据段变得更小,减轻了管理的负担
  • 改善某些查询的性能
  • 将数据修改分布到多个单独的分区上,减少竞争

分区表在各行业的数据库都得到广泛应用,但是有些业务系统在设计阶段对系统数据和性能容量增长估计不足,或没有考虑到运维过程中的数据归档需求,往往没有对表做分区设计。在生产运行经过长时间的数据积累之后,才发现表越来越大,某些查询或插入数据的性能变得越来越慢,迫切需要做表分区改造。

那么问题来了,业务系统往往都是7*24在线作业,改造的过程又必然涉及表结构的变动,如果对表进行重建,会对系统运行产生非常大的影响,通常会设置计划停机窗口来做这类维护操作。

当然,分区表的改造只是诸多数据重组织或重定义场景中的一种,在数据变动需求越来越多、越来越复杂,而系统停机的成本又显著升高的背景下,从Oracle 8i开始就设计了有限的在线重新组织数据的功能,例如create indexes online, rebuilding indexes online。并在9i进一步扩展这方面的能力,引入了数据在线重定义。

在线重定义技术允许数据库管理员在该表上有读写数据操作的情况下,非常灵活地修改表的物理属性、表数据、表结构。

在线重定义的使用场景

有以下变更需求时,都可以考虑使用在线重定义技术,这些场景也是运维过程中经常遇到的:

  • 修改表的物理属性、存储参数
  • 将表迁移到别的表空间
  • 消除表碎片、释放空间
  • 在表中增加、删除或重命名字段
  • 大批量改变表中的数据

在线重定义的实现原理

ORACLE提供了一个DBMS_REDEFINITION包用于在线重定义操作,主要包含三个过程:

DBMS_REDEFINITION.START_REDEF_TABLE

这个过程首先会创建一个快速刷新的物化视图作为过渡表,然后将源表的数据加载到过渡表中,并在源表上创建物化视图日志,以支持快速刷新同步数据

DBMS_REDEFINITION.SYNC_INTERIM_TABLE

用来把源表中的数据同步到过渡表

DBMS_REDEFINITION.FINISH_REDEF_TABLE

这个过程的操作步骤比较多,也是做在线重定义时需要特别注意的,但其执行时间通常是非常短的:

1)先调用一次DBMS_REDEFINITION.SYNC_INTERIM_TABLE,同步数据

2)锁定源表,锁定之后表数据不再允许发生变化

3)再调用一次DBMS_REDEFINITION.SYNC_INTERIM_TABLE,同步数据

4)交换源表和过渡表的表名

5)删除物化视图和物化视图日志

6)释放表锁资源

将普通表改造成分区表

下面我们通过实际案例来应用这项技术,本次实践中我们要弄清楚几个问题:

  1. 在线重定义的操作过程
  2. 将一个2000万数据量的表进行重定义,需要多长时间
  3. 在线重定义期间,表相关的操作是否受影响,又是如何影响的

1检查用户权限

运行DBMS_REDEFINITION包需要以下权限:

可进入用户后执行以下SQL进行检查确认:

select * from session_privs;

2模拟创建一个源表,并插入测试数据

3模拟业务发生场景,一直持续到所有操作结束

按查询更新插入比例为7:1:2模拟,TPS为10,即每秒发生7笔查询、1笔更新、2笔插入操作,这个负载并不算大,但是变更通常选在空闲时间段,而且对于单表来说已经算很高的负载了。

4按需求创建一个已分区的中间表

以上步骤完成准备工作,开始执行在线重定义过程。

5检查源表是否具备在线重定义的条件

6开始在线重定义,这一步相当于初始化工作,耗时比较长

7在中间表上创建约束和索引并收集统计信息

这一步提前做,可以防止重定义完成后,新表没有可用索引,而产生性能问题。

oracle提供了dbms_redefinition.copy_table_dependents过程,用于复制源表上的索引、约束、触发器、权限等依赖关系到中间表,但是这个包存在的BUG也不少,可以选择性使用。

这一步执行之后,可以再做一次手工同步刷新,耗时15秒

8手工同步数据,将上一步执行中将产生的数据先做同步刷新

9完成在线重定义过程,执行后,中间表和源表的表名互换

10删除中间表,并将索引重命名回来

此时的中间表已经是原来未分区的普通表,而源表已经变成了分区表

至此,使用在线重定义进行表分区改造的工作已经完成。

通过各个步骤的耗时情况可以看到,在我们模拟压力的情况下,整个过程耗时12分钟,而最关键的finish_redef_table步骤,也就是会锁表的步骤,只有2秒就完成了。监控数据库的活动会话、DBTIME等数据,没有感觉到数据库的明显变化。

接下来把模拟压力增加到TPS 100,即每秒发生7笔查询、1笔更新、2笔插入操作,整个操作过程源表上DML的变化趋势图如下:

DML操作略有波动,但每一秒钟都存在DML操作,也就是说在这种压力之下,锁表的时间仍然是毫秒级。这组数据也论证了使用在线重定义进行分区表改造的可行性和稳定性。

要注意的问题

使用在线重定义技术,以下情况是需要注意的:

  • 如果离线操作能够解决问题,就不要用在线重定义例如一些静态数据、历史数据的归档迁移,可使用CTAS、alter table move、或导出导入完成
  • 表空间至少要留有比源表所用空间更大的剩余空间
  • 在线重定义的操作过程耗时较长,但对业务的影响最小
  • 要注意源表上的事务操作,如果过于频繁,可能会发生较严重的等待

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2017-08-23

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏BeJavaGod

网站平台架构演变史(五) - 总结

在大环境下的数据库主要有两种情况会出现负重过载: 1. 海量数据的实时统计,比如报表统计 2. 数据库连接数不够用,网站瞬时访问数过大 在这次分享会上有人提出了...

2627
来自专栏java一日一条

MongoDB和MySQL对比(译)

几十年来,关系型数据库已经成为企业应用程序的基础,自从MySQL在1995年发布以来,它已经成为一种受欢迎并且廉价的选择。然而随着近年来数据量和数据的不断激增,...

602
来自专栏杨建荣的学习笔记

巧用闪回查询来分析事务延迟的问题(r9笔记第77天)

前段时间有个开发的同事向我咨询一个问题, 开发同事:Oracle会存在一个用户插入数据,已经提交了;但是另外一个用户还查询不到吗?都是同一张表 ...

2233
来自专栏企鹅号快讯

一文读懂NoSQL数据库

摘要:SQL数据库对数据类型和一致性有要求,NoSQL为了速度、灵活性和规模而放弃了这些要求。 在开发应用程序时,最基本的选择之一就是是否使用SQL或NoSQL...

27110
来自专栏养码场

工作还是游戏?程序员:我选择边玩游戏边工作!

一方面体现在“数据”构造上仍有广阔的待开发空间,另一方面则是应用场景,除了做客观评价和“讲故事”,还需打通更多的商业模式。

621
来自专栏IT大咖说

分布式数据库企业级功能技术解密与最佳实践

编辑IT大咖说 阅读字数: 2739用时: 10分钟 本文内容来源于彭旸在OSC源创会上海站上的主题演讲,IT大咖说为与开源中国合作的视频知识分享平台。 ? 内...

3315
来自专栏数据和云

Oracle In-Memory Option的细节

在OOW大会之后,Oracle In-Memory Option这一数据库选件的细节被更多的公布出来。我选取一点信息和大家分享。 In-Memory Optio...

2759
来自专栏区块链技术指北

区块链钱包开发

文/温国兵 本文由币乎社区(bihu.com)内容支持计划奖励。 这是「区块链技术指北」的第 21 篇文章。 如果对我感兴趣,想和我交流,我的微信号:Wenta...

7408
来自专栏PHP技术

MYSQL数据库设计的一些小技巧[整理] 有感

选表类型: mysql的myisam表适合读操作大,写操作少;表级锁表 innodb表正好相反;行级锁表 互联网服务,不算支付性的服务外,互动产品,新闻系统等等...

2854
来自专栏杨建荣的学习笔记

远程协助解决重建索引的危机问题 (r8笔记第80天)

最近在工作忙碌之余也帮几位网友查看了几个问题,有一个问题让我印象挺深,其实也可以分享出来作为一些参考,问题之外还是有一些值得借鉴的地方。 首先是在周末的一...

3204

扫码关注云+社区