一、前言
我们都知道当分区存在maxvalue的分区时候，是无法通过alter table add partition的方式来增加分区的。Oracle推荐的方式则是alter table split partition从maxvalue的分区中进行拆分，但是血的教训告诉却我们通过split partition的方式拆，如果使用时机不正确的时，则可能会给我们带来极大的风险。这次为大家分享的则是在进行split partition操作的时候造成的大量enq:TM-contention争用，导致数据库发生严重阻塞及性能的问题。
二、split partition拆分分区的几种情况
Split partition拆分分区的命令如下(以拆分p_max分区为例)：
alter table &table_name split partition p_max at ('2023-03-01') into (partition p_20230201, partition p_max) update indexes;注：此处加入update indexes主要是为了防止表上的全局唯一索引失效。
而根据p_max分区是否存在数据，又可将上述的split partition操作划分为三种情况：
1）p_max分区为空分区，需要通过split partition方式对分区表进行新增分区操作；
2）p_max分区中存在数据，需要通过split partition方式对分区表进行历史分区创建，并将数据全部存放到历史分区中（比如p_max分区仅存放了上个月的数据，而我们需要新增上个月的分区，并将p_max分区全部存放到上个月的分区中）；
3）p_max分区中存在数据，需要通过split partition方式对分区表进行历史分区创建，并将其中部分数据存放到历史分区中(比如p_max分区存放了历史两个月的数据，而我们需要新增历史两个月的分区，并将p_max分区中的数据分别进行split操作到各自历史分区中)；
当出现第一种情况，我们则可以正常操作，此时split partiton不存在数据上的交互，可以理解为仅存在表定义上的操作，而该操作也被称为fast split，因此整个操作效率极高，不会出现阻塞的情况；
当第二种情况，虽然p_max分区中存在大量数据，但是经过实践，该操作也很快，感觉也是没有对数据进行实际操作，而是直接修改标定义，整个操作效率也很高，可放心大胆操作；
当第三种情况，则需要对p_max分区中的数据进行拆分，此时split partition操作耗时则会很长。同时，如果此时有数据操作(insert、update、delete的时候)，则很会造成enq:TM – contention的争用，影响生产正常运行
三、测试分析
下面则可以通过测试案例，进行split partition操作的测试，来详细了解一下不同的操作到底发生了什么事情。
1.数据准备
1.创建测试用户:
create user split_test identified by split_test default tablespace TS_FNC_TAB quota unlimited on TS_FNC_TAB quota unlimited on TS_FNC_IDX quota unlimited on TS_FNC_LOB;2.赋予测试用户权限:
grant create session,resource to split_test;3.创建分区测试表:
create table split_test(id number,name varchar2(30),trans_date varchar2(80))partition by range(trans_date)(partition p_20220101 values less than('2022-02-01'),partition p_20220201 values less than('2022-03-01'),partition p_20220301 values less than('2022-04-01'),partition p_20220401 values less than('2022-05-01'),partition p_20220501 values less than('2022-06-01'),partition p_20220601 values less than('2022-07-01'),partition p_max values less than(maxvalue));create unique index idx_id on split_test(id) tablespace TS_FNC_IDX;create index idx_trans_date on split_test(trans_date) tablespace TS_FNC_IDX;alter table split_test add constraint pk_id primary key(id) using index idx_id;--创建sequencecreate sequence SEQ_SPLIT_TEST_IDminvalue 1maxvalue 9999999999start with 1increment by 1cache 50cycle;4.插入数据:
declaretemp int := 1000000;beginfor i in 1..temploopif(to_date('2020-12-09','yyyy-mm-dd')+i = to_date('2034-03-21','yyyy-mm-dd')) thenexit;end if;insert into split_test(id,name,trans_date) values(SEQ_SPLIT_TEST_ID.nextval,i,to_char(to_date('2020-12-09','yyyy-mm-dd')+i,'yyyy-mm-dd hh24:mi:ss'));end loop;commit;end;5.收集统计信息：
exec dbms_stats.gather_table_stats(ownname=>'split_test',tabname=>'split_test',degree=>10);结果如下：
TABLETABLE PARTITIONSUBPART PART SUBPART PARTITION PARTITION COLUMNOWNERNAME TYPETYPE COUNT COUNT KEY COUNT COLUMN NAME POSITION--------------- -------------------- ---------- ---------- ----- ------- --------- --------------- --------SPLIT_TESTSPLIT_TEST RANGENONE12 0 1 TRANS_DATE1 HIGH_VALUE TABLESPACEPARTITION EMPTY LAST TIMESUBPARTITIONPARTITION_NAME HIGH_VALUELENGTH NAMENUM_ROWS BLOCKS SIZE_KBBLOCKS ANALYZEDAVG_SPACE COUNT COMPRESSION-------------------- --------------- ---------- --------------- -------- -------- ---------- ------ ------------------- --------- ------------ -----------P_20220101 '2022-02-01' 12 TS_FNC_TAB 46816238 1.86KB0 2023-03-21 18:51:42 0 0 DISABLEDP_20220201 '2022-03-01' 12 TS_FNC_TAB3136 46 .36KB 0 2023-03-21 18:51:42 0 0 DISABLEDP_20220301 '2022-04-01' 12 TS_FNC_TAB3472 46 .36KB 0 2023-03-21 18:51:42 0 0 DISABLEDP_20220401 '2022-05-01' 12 TS_FNC_TAB3360 46 .36KB 0 2023-03-21 18:51:42 0 0 DISABLEDP_20220501 '2022-06-01' 12 TS_FNC_TAB3472 46 .36KB 0 2023-03-21 18:51:42 0 0 DISABLEDP_20220601 '2022-07-01' 12 TS_FNC_TAB3360 46 .36KB 0 2023-03-21 18:51:42 0 0 DISABLEDP_MAX MAXVALUE8 TS_FNC_TAB421644 2148 16.78KB0 2023-03-22 22:00:16 0 0 DISABLED12 rows selected.2.操作测试
1.split partition操作:
---情况一：p_max中没有数据，直接进行分区alter table split_test split partition p_max at ('2022-08-01') into (partition p_20220701, partition p_max) update indexes;---情况二：p_max中的数据全部落到同一个分区alter table split_test split partition p_max at ('2035-09-01') into (partition p_20350801, partition p_max) update indexes;---情况三：p_max中的数据部分落到上一个分区alter table split_test split partition p_max at ('2022-08-01') into (partition p_20220701, partition p_max) update indexes;2.观察锁情况：
在测试时，由于数据量较小，所以通过循环执行的方式观察锁情况，具体执行脚本如下：
while(true)dosqlplus / as sysdba << EOFset line 999--查看锁表对象，及锁表模式select a.object_id,a.locked_mode,a.session_id,b.object_name,b.subobject_namefrom v\$locked_object a,dba_objects bwhere a.object_id = b.object_id;--查看基本锁信息select /*+rule*/ type,id1,id2,lmode from v\$lock where type in('TX','TM');exit;EOFsleep 1done3.分区过程中通过10046进行观察
alter session set events '10046 trace name context forever ,level 12' ; split clause;alter session set events '10046 trace name context off' ;3.实验结果观察
情况一、p_max中没有数据，直接进行分区
通过对锁的循环观察，得到以下结论：
在循环执行查询锁的观察中，没有发现相关的锁信息，操作很快结束(10046中查看也会有相应的锁，但是由于不涉及数据操作，因此锁很快释放，通过脚本难以观测到)，从这一方面可以说明，当p_max中没有数据的时候，split partition操作对业务没有影响。
情况二、p_max中所有的数据被转移到新的分区
通过对锁的循环观察，以及对10046的event观察，可以得到以下结论：
在循环执行查询锁的观察中，没有发现相关的锁信息，操作很快结束(与第一种情况相似)，在10046中，明显可以看到lock table到p_max分区的操作，但是从整体操作全览中查看，也没有涉及到实际数据的操作，即和我们猜想的一样，这样的操作也是只涉及到了表结构的变更，不涉及数据的拆分，因此操作效率也很高，split partition对正常操作几乎无影响。
SQL ID: 35w7ssw5nck41 Plan Hash: 0LOCK TABLE "SPLIT_TEST" PARTITION ("P_MAX")IN EXCLUSIVE MODENOWAIT call count cpuelapsed diskquerycurrentrows------- -------------- ---------- ---------- ---------- --------------------Parse10.00 0.00000 0Execute10.00 0.00000 0Fetch00.00 0.00000 0------- -------------- ---------- ---------- ---------- --------------------total20.00 0.00000 0Misses in library cache during parse: 1Optimizer mode: CHOOSEParsing user id: 139 (recursive depth: 1)...........OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTScall count cpuelapsed diskquerycurrentrows------- -------------- ---------- ---------- ---------- --------------------Parse20.00 0.00050 0Execute20.00 0.000 3289 28 0Fetch00.00 0.00000 0------- -------------- ---------- ---------- ---------- --------------------total40.01 0.010 3294 28 0Misses in library cache during parse: 1Elapsed times include waiting on following events:Event waited on Times Max. WaitTotal Waited---------------------------------------- Waited----------------------SQL*Net message to client 20.000.00SQL*Net message from client 2 25.25 46.06Disk file operations I/O10.000.00log file sync 10.020.02..........情况三、p_max中有部分数据被转移到新的分区，部分数据仍然保留在p_max分区中
通过对锁的循环观察，以及对10046的event观察，可以得到以下结论：
此时split partition操作时间会比较长，同时会在基表obj$上产生3级TM锁，在整张表上产生3级TM锁，在分区p_max上产生一个6级TM锁，此外还会产生一个6级事务锁。而这则说明在此时，如果我们在split_test表的p_max分区上是无法做任何dml操作的，那么如果此时当我们的应用依然运行，且一直向该分区表中做insert/delete/update的dml操作时，则会被split parition操作阻塞，产生大量的enq:TM – contention的争用，直到分区拆分完成，此时一定会造成业务不可用的情况。
SQL ID: 35w7ssw5nck41 Plan Hash: 0LOCK TABLE "SPLIT_TEST" PARTITION ("P_MAX")IN EXCLUSIVE MODENOWAIT call count cpuelapsed diskquerycurrentrows------- -------------- ---------- ---------- ---------- --------------------Parse10.00 0.00000 0Execute10.00 0.00000 0Fetch00.00 0.00000 0------- -------------- ---------- ---------- ---------- --------------------total20.00 0.00000 0Misses in library cache during parse: 1Optimizer mode: CHOOSEParsing user id: 139 (recursive depth: 1)..................OBJECT_ID LOCKED_MODE SESSION_ID OBJECT_NAME SUBOBJECT_NAME---------- ----------- ---------- -------------------------------------------------------- ------------------------------18 3 1141 OBJ$232492 6 1141 SPLIT_TESTP_MAX232485 3 1141 SPLIT_TESTSQL> SQL> TYID1 ID2LMODE-- ---------- ---------- ----------TX 5243177666856TM 232492 06TM 18 03TM 232485 03而对该情况下的split partition操作的10046进行分析，也可以明显的看出两点
1.会对被拆分分区进行lock table操作（即我们看到的p_max分区的6级锁）；
2.会伴随大量的数据操作，因此此时的操作会比较慢。
SQL ID: 35w7ssw5nck41 Plan Hash: 0LOCK TABLE "SPLIT_TEST" PARTITION ("P_MAX")IN EXCLUSIVE MODENOWAIT call count cpuelapsed diskquerycurrentrows------- -------------- ---------- ---------- ---------- --------------------Parse10.00 0.00000 0Execute10.00 0.00000 0Fetch00.00 0.00000 0------- -------------- ---------- ---------- ---------- --------------------total20.00 0.00000 0Misses in library cache during parse: 1Optimizer mode: CHOOSEParsing user id: 139 (recursive depth: 1)...........OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTScall count cpuelapsed diskquerycurrentrows------- -------------- ---------- ---------- ---------- --------------------Parse20.00 0.01000 0Execute24.85 6.181 21841324842422574Fetch00.00 0.00000 0------- -------------- ---------- ---------- ---------- --------------------total44.86 6.201 21841324842422574Misses in library cache during parse: 1Elapsed times include waiting on following events:Event waited on Times Max. WaitTotal Waited---------------------------------------- Waited----------------------SQL*Net message to client 20.000.00SQL*Net message from client 2 34.10 49.93Disk file operations I/O70.000.00direct path write690.000.01direct path sync30.080.16log buffer space 530.131.13reliable message20.000.00enq: RO - fast object reuse 10.000.00db file sequential read 10.000.00enq: CR - block range reuse ckpt10.020.02log file sync 10.000.004.如何优雅的做split partition
根据上述的测试结果，其实我们就了解到针对不同的情况，需要通过不同的split partition的方式和时间去对分区表进行拆分。如果稍有不慎，则可能会对生产业务系统造成运行风险。那么我们该如何优雅安全的做split partition的操作呢？
首先，针对p_max不存在数据的情况，我们则可以直接使用split partition的方式进行新分区的增加(当然，也可以直接删除p_max分区，然后通过add partition的方式新增分区，然后再将p_max分区进行创建即可)；
第二，针对缺少1个分区的情况，该分区的数据一定是落在p_max中。因此，我们也可以直接通过split partition的方式进行分区拆分；
第三，针对缺少多个分区的情况，pmax分区中会存在多个分区的数据。此时，我们则需要组合前两个情况的方式进行处理，比如1、2、3月分别需要存放到p20230101、p20230201、p20230301的分区中，但是现在都被存放到p_max分区中，那么此时我们则可以：
1.查找p_max的最大值：
select max(trans_date) from &table_name partition(p_max);2.直接通过plit partition的方式，将p_max的所有数据拆分到最近的分区中
alter table split_test split partition p_max at ('2023-04-01') into (partition p_20230301, partition p_max) update indexes;3.直接通过plit partition的方式，新增分区(此时p_max中已经没有数据)
alter table split_test split partition p_max at ('2023-05-01') into (partition p_20230401, partition p_max) update indexes;alter table split_test split partition p_max at ('2023-06-01') into (partition p_20230501, partition p_max) update indexes;alter table split_test split partition p_max at ('2023-07-01') into (partition p_20230601, partition p_max) update indexes;alter table split_test split partition p_max at ('2023-08-01') into (partition p_20230701, partition p_max) update indexes;......4.等待3月的时间过去后(俗话说，心急吃不了热豆腐，没错，就是稳下来等待)，数据会写入新的p20230401的分区中，而历史的p20230301的分区则可以被认为是静止状态，当我们只要不在p20230301的分区上有大量的update和delete业务时，那么即使通过split partition操作也只是对p20230301分区上造成TM的6级锁，而不会对其他分区(正在使用的p20230401)产生独占锁，因此不会对新的业务造成大量的阻塞。1、2、3的月份，我们则可以放心大胆的(对分区p20230301)做split parition操作了。这样的分层分步的操作，则可以正好避免在当前使用分区上进行操作，做到不影响业务的优雅拆分分区。
当然，如果是一些非常紧急的情况下，我们也可以缩小第二步中的分区跨度，比如我直接将新的区分到今天甚至这个小时，那么第二天或者下个小时我就可以做第四步的操作了。
四、结论
虽然通过上述方式可以较为优雅的对分区进行拆分，但是毕竟该操作可以被认为是一个非常规操作。所以，我们应该尽量避免此类操作。在Oracle中，分区表是一个很好的解决大表性能的问题方案，但是随之带来的维护量和维护难度也随之上升。我们能做到最好的就是尽可能的解决监控盲点，做到提前运维，提前发现风险，避免让数据进入到默认分区中。