使用在线重定义重构亿级分区表（r10笔记第34天）

在我的印象中，一直以来都会收到一封报警邮件，之前分析过，排查过，最后发现是一个遗留问题，协调开发同学，停业务维护还是有一些难度，最后不了了之了，在今天又突然想起了这件事情，觉得还是需要做点什么。 报警邮件类似下面的形式： ZABBIX-监控系统: ------------------------------------ 报警内容: Disk I/O is overloaded on 10.127.2.134_xx机房_xxxx ------------------------------------ 报警级别: PROBLEM ------------------------------------ 监控项目: CPU iowait time：17.21 % ------------------------------------ 报警时间:2016.09.26-04:05:33 这是一台备库11gR2的环境，在ADG模式下的数据变化也会单独采样，这一点非常难得。所以很看到备库的DB time情况，可以明显看到在早间的时候有很大的抖动。

而基于快照，定位到具体的语句情况下，可见SQL_ID(4rhpc838qfsmy)就是我们要攻坚的重点了。

这是什么样的一个语句呢，让我很感意外。语句竟然看起来很简单，而且看起来竟然有两个执行计划。 $ sh showsqltext.sh 4rhpc838qfsmy select * from (select user_ip,end_time from bill_logout_cn where cn=:1 order by end_time desc) where rownum=1 而且仔细看语句还是有一点优化的味道。 这样一个语句怎么性能会很差呢。 通过awrsqrpt得到的结果如下：

对这样一个语句，存在两个执行计划，就很奇怪了。

第一个执行计划虽然是索引扫描，但是I/O等待很高。

Plan 2(PHV: 2814547617) -----------------------

第二个执行计划产生了大量的buffer gets,使用了全表扫描。

这是一个什么类型的表呢，数据量有2亿多，CN字段存在一个非唯一性索引。 执行计划如下

这个语句一个是使用了index skip scan,瓶颈在于扫描了大量的分区，结果大量的IO等待都在于此。 而第二个执行计划索性走了全表扫描，可见还是在运行中根据CBO评估而得全表的代价要相对低一些。

对于这个问题有几个疑问，首先这个语句性能如此之差，为什么在主库没有报警而在备库呢，其实原因是这样，主库的配置信息要好很多，这些问题和负载在主库都不是问题，以至于这个问题的影响在主库被弱化了。 而为什么语句走索引全扫描，全表扫描呢。这个其实说来话长，我查看了表的结构信息发现，这个表存在大量的分区，每天会生成一个分区，结果在2014年的某一天开始突然就停止了分区的维护，结果导致分区数据现在全都堆积在了默认分区上，这样就会性能一个很奇怪的数据分布，绝大多数的数据都分布在一个分区上，而还有很多历史数据分布在更多以日期为单位的分区上。 如果了解了问题的原委，其实也可以理解数据库在处理这个问题时的艰辛。 而对于这个问题的改进，就是需要重构分区，摆在我面前的由几件事情。首先是需要和开发确认是否历史数据可以清理，这个经过讨论，大家都带着保守态度；第二个问题是分区的维护，需要添加最近的一些分区，这个是否可以给出维护时间，不过经过讨论，在了解了业务特征之后，其实也可以做一个折中，那就是使用在线重定义来完成，尽管这是一个亿级数据的大表，但是因为是统计系统，所以数据更新很少，而且基本都是在凌晨胡统一更新，其他时段主要是查询为主，这样来看这个问题使用在线重定义其实还蛮不错的，互相成就，也不用互相协调区停业务应用了。 第二个问题解决之后第一个问题就好办了，可以在确认之后再具体部署。 好了，来到了重点的内容，那就是亿级大表的在线重定义，虽然之前做了周密的测试，但是还是有一些期待和小紧张。 和开发同学约定了下午的时间来在线维护，留给我的时间也不到一个小时了，要生成近900多个额外的分区，这个工作量着实不小，我采用了如下的SQL来动态生成需要补充的分区。 select 'PARTITION BILL_LOGOUT_CN_'||to_char((trunc(sysdate)-990+level),'yyyymmdd')||' VALUES LESS THAN (TO_DATE('||chr(39)||(trunc(sysdate)-990+level+1)||chr(39)||', '||chr(39)||'YYYY-MM-DD HH24:MI:SS'||chr(39)||')) ' ||'TABLESPACE ACCSTAT_DATA ,' from dual connect by level<990; 生成的语句类似下面的形式： PARTITION BILL_LOGOUT_CN_20160924 VALUES LESS THAN (TO_DATE('2016-09-25 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) TABLESPACE ACCSTAT_DATA , 。。。 我创建了一个新表BILL_LOGOUT_CN_DEF来和BILL_LOGOUT_CN最后做数据字典信息的交换。 在线重定义的前几步都是套路，因为没有主键，所以我使用rowid的方式。 exec DBMS_REDEFINITION.CAN_REDEF_TABLE('TEST','BILL_LOGOUT_CN',2); exec DBMS_REDEFINITION.START_REDEF_TABLE('TEST','BILL_LOGOUT_CN','BILL_LOGOUT_CN_DEF',NULL,2); 第二步运行之后，后台就开始忙碌起来了。 可以看到有会话在运行这样的语句。 SQL_FULLTEXT ---------------------------------------------------------------------------------------------------- INSERT /*+ BYPASS_RECURSIVE_CHECK APPEND SKIP_UNQ_UNUSABLE_IDX */ INTO "TEST"."BILL_LOGOUT_CN_DEF"(M_ROW$$,"CN",XXXX) SELECT XXXX FROM "TEST"."BILL_LOGOUT_CN" "BILL_LOGOUT_CN" 而查看物化视图相关的数据字典，可以赫然看到有一个prebuilt物化视图，采用快速刷新的方式。

而在数据刷新之后，可以看到后台对于rowid的方式采用了下面的处理方式，即创建一个唯一性索引 SQL_FULLTEXT ---------------------------------------------------------------------------------------------------- CREATE UNIQUE INDEX "TEST"."I_SNAP$_BILL_LOGOUT_CN_DEF" ON "TEST"."BILL_LOGOUT_CN_DEF" ("M_ROW$$") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS TABLESPACE "TEST_DATA" 而这个索引也在不断增大。 SEGMENT_NAME SIZE_M BLOCKS ------------------------------ --------- ---------- I_SNAP$_BILL_LOGOUT_CN_DEF 8065 1032320 --------- sum 8065 好了，有的同学可能会说，在线重定义了解那么多干嘛，够用就行了。 没过多久，就看到数据复制的过程抛错了。 SQL> exec DBMS_REDEFINITION.START_REDEF_TABLE('TEST','BILL_LOGOUT_CN','BILL_LOGOUT_CN_DEF',NULL,2); BEGIN DBMS_REDEFINITION.START_REDEF_TABLE('TEST','BILL_LOGOUT_CN','BILL_LOGOUT_CN_DEF',NULL,2); END; * ERROR at line 1: ORA-14010: this physical attribute may not be specified for an index partition ORA-06512: at "SYS.DBMS_REDEFINITION", line 56 ORA-06512: at "SYS.DBMS_REDEFINITION", line 1490 ORA-06512: at line 1 这个问题如果查看metalink，博客可能还没有很针对性的解答，这个就需要多在线重定义的过程能够很熟悉。 简单分析发现就是在表空间上出了问题。 重新分配扩展空间之后，再次开启重定义，会抛出下面的错误。 SQL> exec DBMS_REDEFINITION.START_REDEF_TABLE('TEST','BILL_LOGOUT_CN','BILL_LOGOUT_CN_DEF',NULL,2); BEGIN DBMS_REDEFINITION.START_REDEF_TABLE('TEST','BILL_LOGOUT_CN','BILL_LOGOUT_CN_DEF',NULL,2); END; * ERROR at line 1: ORA-23539: table "TEST"."BILL_LOGOUT_CN" currently being redefined ORA-06512: at "SYS.DBMS_REDEFINITION", line 56 ORA-06512: at "SYS.DBMS_REDEFINITION", line 1490 ORA-06512: at line 1 为了加快处理过程，我们也可以手工处理一部分 删除对应的物化视图和物化视图日志 SQL> drop materialized view test.BILL_LOGOUT_CN_DEF; Materialized view dropped. SQL> drop materialized view log on test.BILL_LOGOUT_CN; Materialized view log dropped. 看来这个过程可以完全证明在线重定义是使用物化视图快速刷新的。 来终止一下重定义过程，重新来过。其实这个步骤就在做truncate的操作。 execute dbms_redefinition.ABORT_REDEF_TABLE ('TEST','BILL_LOGOUT_CN','BILL_LOGOUT_CN_DEF'); 重新开始数据复制，重定义。 SQL> exec DBMS_REDEFINITION.CAN_REDEF_TABLE('TEST','BILL_LOGOUT_CN',2); SQL>exec DBMS_REDEFINITION.START_REDEF_TABLE('TEST','BILL_LOGOUT_CN','BILL_LOGOUT_CN_DEF',NULL,2); 这一次就顺利多了，一次搞定。 最后完成前可以再手工刷新一下增量数据，保持数据的gap尽可能小。 SQL> execute dbms_redefinition.sync_interim_table ('TEST','BILL_LOGOUT_CN','BILL_LOGOUT_CN_DEF'); PL/SQL procedure successfully completed. 最后交换数据字典信息即可完成。 SQL> exec DBMS_REDEFINITION.FINISH_REDEF_TABLE('TEST','BILL_LOGOUT_CN','BILL_LOGOUT_CN_DEF'); PL/SQL procedure successfully completed. Elapsed: 00:00:39.82 解决了之个问题之后，后续还有一些小的地方需要注意，补充新的分区，持续观察性能改进，历史分区数据的清理等。