关于修改分区表的准备和操作细则(r3笔记26天）

在之前的博文中，讨论过一个根据分区键值发现性能问题的案例。90%以上的数据都分布在了一个分区上，其它的分区要么没有数据要么数据很少，这是很明显的分区问题。当然这个过程中也发现了分区的划分从开发角度和数据角度还是存在很大的差别，导致了分区的问题。 通过分区键值发现性能问题 http://blog.itpub.net/23718752/viewspace-1263068/ 发现了问题，以点带面，发现一些相关的分区表也有类似的问题，最后确认和分析后，发现收到影响的表有20多个，而且数据量都不小。 看来又得是一个忙碌的夜晚来修复这个问题了。 如果要准备相应的脚本，也要考虑很多的问题，我大体列了几个步骤。相应的脚本也会按照这个步骤处理。 大体的思路和数据迁移有些类似,相比来说增加了分区的操作。 step1_dump_bak 关于备份，最好存有两份备份，物理备份和逻辑备份 step2_truncate 分区之前，需要清空表中的数据。这个过程中需要考虑disable foreign key和trigger. step3_drop_par 重新分区的时候，只保留一个默认分区maxvalue,然后使用drop partition命令完成。 step4_pre_par 在正式分区之前，可以先把表设为nologging,index设置为nologging,lob字段也设置为nologging.作为后面数据导入的时候的优化准备。 step5_par_one 开始正式的分区修改，这个操作依赖于默认的maxvalue分区，不断的split，因为没有了数据所以速度还是很快的。这个部分处理分区键值为一个的表 step6_par_two 开始正式的分区修改，这个部分处理分区键值为2个的表。 step7_post_par 这个部分需要在数据导入之前再次验证分区的规则和分区数据是否和预期一致，在数据导入之后检查就太晚了。 step8_data_append 确认之后，开始数据的导入，这个部分使用数据迁移中的外部表方式，速度还是很快的，在反复比较了imp/impdp,sqlldr之后，外部表处理和控制要更好一些。 step9_stat_gather 这个部分是在数据导入之后。需要重新收集统计信息，尽管表的数据条数没有变化，但是分区级的统计信息是极大的变化。这个也需要考虑。 #1 step1_dump_bak 关于备份,个人建议还是最好有两种不同的备份，比如exp/expdp一种备份，这个作为物理备份,外部表导出来作为另外一种备份，同时在数据加载的时候用到，有了这两种备份，就不会在出现问题的时候慌乱了,如果外部表导出因为空间等不可知因素，还有希望去弥补。 #step2_truncate 这个部分，就是直接truncate来完成了。可以开几个session来并行执行。 step3_drop_par 这个部分可以参考如下的脚本来完成,会删除掉其它的分区，只保留默认分区,当然如果分区规则有变化也需要适当的做一些变更。这个部分完全可以用shell写成批处理脚本。 set pages 0 set linesize 150 set feedback off select 'alter table '||table_name||' drop partition '||partition_name||' ;' from user_tab_partitions where table_name='XXXXXX' and partition_name not like '%MAXVALUE%' step4_pre_par 这个过程中需要设置表为Nologging..使用的脚本比较长,可以参考http://blog.itpub.net/23718752/viewspace-1192153/ step5_par_one step6_par_two 关于分区的修改部分,之前自己写了一个Pl/sql来处理，花了不少的时间。脚本虽然完成了，但是不够通用，最后发现本来几十行的pl./sql用几行shell就完成了。 比如我们修改分区的时候，语句类似下面的格式。 alter table XXXXX SPLIT PARTITION CMAXVALUE_MMAXVALUEat (2,2) INTO ( PARTITION C2_M2, PARTITION CMAXVALUE_MMAXVALUE);

比如我们的需求是这样的。如果是一个键值，分区字段就是PERIOD_KEY，会有120个分区，如果分区键值是2个，分区字段就是PERIOD_KEY,ENTITY_KEY两个组合起来。这样就是两千多个分区了。

如果分区键值为1个，就可以用shell这么做。如果默认分区有一定的变化，可以作为输入参数灵活变更。 for i in {0..199} do echo 'alter table '$1' split partition '$2' at('$i') into (partition P'$i',partition '$2');' done 如果分区键值为2个，类似下面的方式。注意ENTITY_KEY是按照5n的方式来递增的。 for i in {0..199} do for ((j=5;j<=100;j=$j+5)) do echo 'alter table '$1' split partition '$2' at('$i','$j') into (partition P'$i'_C'$j',partition '$2');' done done 脚本运行后的效果如下,就完全可以脱离数据库环境来完成。 alter table XXXXX split partition PMAXVALUE at(37) into (partition P37,partition PMAXVALUE); ..... alter table XXXXX split partition PMAXVALUE_CMAXVALUE at(198,90) into (partition P198_C90,partition PMAXVALUE_CMAXVALUE); .... step7_post_par 关于分区的检查和验证，可以根据具体的业务逻辑来判断。比如我可以使用如下的方式来做一个简单验证。 这个脚本能够得到一个数据条数的列表，能够清晰的判断出来，不用全部分区的数据都查，可以根据自己的选择针对性来查就可以了。 set pages 0 set linesize 150 set feedback off select 'alter table '||table_name||' drop partition '||partition_name||' ;' from user_tab_partitions where table_name='XXXXX' and partition_name not like '%MAX%';

比如在第一步中导出的时候，有个表的数据全部分区在默认分区中。 . . exporting partition P40_C99 0 rows exported . . exporting partition PMAXVALUE_C99 1048387 rows exported Export terminated successfully without warnings. 分区之后的数据为，可以看到数据的分区就好多了。 PAR_TAB_TEST P55_C10 22161 PAR_TAB_TEST P55_C100 22224 PAR_TAB_TEST P55_C15 22215 PAR_TAB_TEST P55_C20 22370 PAR_TAB_TEST P55_C25 22207 PAR_TAB_TEST P55_C30 22422 PAR_TAB_TEST P55_C35 22374 PAR_TAB_TEST P55_C40 22501 PAR_TAB_TEST P55_C45 22225 PAR_TAB_TEST P55_C5 22349 PAR_TAB_TEST P55_C50 22391 以上是一个直观的验证，还需要再做一层验证，看看数据的分区是不是和需求一致的。这个检查至关重要。比如分区P55_C10存放的数据和键值的匹配情况。 如果粗放的检查完，不做这一层次的检查，如果出现问题，后面的步骤全都没有意义了。 SQL> select period_key,ENTITY_KEY from XXXXX partition( P55_C10) group by period_key,customer_key order by customer_key; PERIOD_KEY ENTITY_KEY ---------- ------------ 55 5 55 6 55 7 55 8 55 9 SQL> select period_key,ENTITY_KEY from XXXXXX partition( P55_C5) group by period_key,customer_key order by customer_key; PERIOD_KEY ENTITY_KEY ---------- ------------ 55 0 55 1 55 2 55 3 55 4 step8_data_append 这个部分就开始正式的数据导入了。外部表的数据迁移方式，可以参考我的文集，就不从头赘述了。http://blog.itpub.net/special/show/sid/383/ step9_stat_gather 最后就是收集统计信息了，这个部分可能会消耗一定的时间，可以先在检查后开放环境给开发来做确认，毕竟收集统计信息是可以online完成的，让他们先确认业务，后台并发跑一些session收集，可以节省较多的时间。