以优雅的方式对oracle分区表进行分区拆分操作

## 一、前言

我们都知道当分区存在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;



--创建sequence

create sequence SEQ_SPLIT_TEST_ID

minvalue 1

maxvalue 9999999999

start with 1

increment by 1

cache 50

cycle;

4.插入数据:

declare

temp int := 1000000;

begin

for i in 1..temp

loop

if(to_date('2020-12-09','yyyy-mm-dd')+i = to_date('2034-03-21','yyyy-mm-dd')) then

exit;

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

结果如下：

TABLE TABLE PARTITION SUBPART PART SUBPART PARTITION PARTITION COLUMN

OWNER NAME TYPE TYPE COUNT COUNT KEY COUNT COLUMN NAME POSITION

--------------- -------------------- ---------- ---------- ----- ------- --------- --------------- --------

SPLIT_TEST SPLIT_TEST RANGE NONE 12 0 1 TRANS_DATE 1

  


HIGH_VALUE TABLESPACE PARTITION EMPTY LAST TIME SUBPARTITION

PARTITION_NAME HIGH_VALUE LENGTH NAME NUM_ROWS BLOCKS SIZE_KB BLOCKS ANALYZED AVG_SPACE COUNT COMPRESSION

-------------------- --------------- ---------- --------------- -------- -------- ---------- ------ ------------------- --------- ------------ -----------

P_20220101 '2022-02-01' 12 TS_FNC_TAB 46816 238 1.86KB 0 2023-03-21 18:51:42 0 0 DISABLED

P_20220201 '2022-03-01' 12 TS_FNC_TAB 3136 46 .36KB 0 2023-03-21 18:51:42 0 0 DISABLED

P_20220301 '2022-04-01' 12 TS_FNC_TAB 3472 46 .36KB 0 2023-03-21 18:51:42 0 0 DISABLED

P_20220401 '2022-05-01' 12 TS_FNC_TAB 3360 46 .36KB 0 2023-03-21 18:51:42 0 0 DISABLED

P_20220501 '2022-06-01' 12 TS_FNC_TAB 3472 46 .36KB 0 2023-03-21 18:51:42 0 0 DISABLED

P_20220601 '2022-07-01' 12 TS_FNC_TAB 3360 46 .36KB 0 2023-03-21 18:51:42 0 0 DISABLED

P_MAX MAXVALUE 8 TS_FNC_TAB 421644 2148 16.78KB 0 2023-03-22 22:00:16 0 0 DISABLED



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

do

sqlplus / as sysdba << EOF

set line 999

--查看锁表对象，及锁表模式

select a.object_id,

a.locked_mode,

a.session_id,

b.object_name,

b.subobject_name

from v\\$locked_object a,

dba_objects b

where a.object_id = b.object_id;

--查看基本锁信息

select /*+rule*/ type,id1,id2,lmode from v\\$lock where type in('TX','TM');

exit;

EOF

sleep 1

done

3.分区过程中通过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: 0



LOCK TABLE "SPLIT_TEST" PARTITION ("P_MAX") IN EXCLUSIVE MODE NOWAIT 

  


call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 1 0.00 0.00 0 0 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 0 0.00 0.00 0 0 0 0

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 2 0.00 0.00 0 0 0 0



Misses in library cache during parse: 1

Optimizer mode: CHOOSE

Parsing user id: 139 (recursive depth: 1)

...........

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS



call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 2 0.00 0.00 0 5 0 0

Execute 2 0.00 0.00 0 3289 28 0

Fetch 0 0.00 0.00 0 0 0 0

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 4 0.01 0.01 0 3294 28 0



Misses in library cache during parse: 1



Elapsed times include waiting on following events:

Event waited on Times Max. Wait Total Waited

---------------------------------------- Waited ---------- ------------

SQL*Net message to client 2 0.00 0.00

SQL*Net message from client 2 25.25 46.06

Disk file operations I/O 1 0.00 0.00

log file sync 1 0.02 0.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: 0



LOCK TABLE "SPLIT_TEST" PARTITION ("P_MAX") IN EXCLUSIVE MODE NOWAIT 

  


call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 1 0.00 0.00 0 0 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 0 0.00 0.00 0 0 0 0

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 2 0.00 0.00 0 0 0 0



Misses in library cache during parse: 1

Optimizer mode: CHOOSE

Parsing user id: 139 (recursive depth: 1)

..................



OBJECT_ID LOCKED_MODE SESSION_ID OBJECT_NAME SUBOBJECT_NAME

---------- ----------- ---------- -------------------------------------------------------- ------------------------------

18 3 1141 OBJ$

232492 6 1141 SPLIT_TEST P_MAX

232485 3 1141 SPLIT_TEST

SQL> SQL> 

TY ID1 ID2 LMODE

-- ---------- ---------- ----------

TX 524317 766685 6

TM 232492 0 6

TM 18 0 3

TM 232485 0 3

而对该情况下的split partition操作的10046进行分析，也可以明显的看出两点

1.会对被拆分分区进行lock table操作（即我们看到的p_max分区的6级锁）；

2.会伴随大量的数据操作，因此此时的操作会比较慢。

SQL ID: 35w7ssw5nck41 Plan Hash: 0



LOCK TABLE "SPLIT_TEST" PARTITION ("P_MAX") IN EXCLUSIVE MODE NOWAIT 

  


call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 1 0.00 0.00 0 0 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 0 0.00 0.00 0 0 0 0

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 2 0.00 0.00 0 0 0 0



Misses in library cache during parse: 1

Optimizer mode: CHOOSE

Parsing user id: 139 (recursive depth: 1)

...........

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS



call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 2 0.00 0.01 0 0 0 0

Execute 2 4.85 6.18 1 2184 1324842 422574

Fetch 0 0.00 0.00 0 0 0 0

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 4 4.86 6.20 1 2184 1324842 422574



Misses in library cache during parse: 1



Elapsed times include waiting on following events:

Event waited on Times Max. Wait Total Waited

---------------------------------------- Waited ---------- ------------

SQL*Net message to client 2 0.00 0.00

SQL*Net message from client 2 34.10 49.93

Disk file operations I/O 7 0.00 0.00

direct path write 69 0.00 0.01

direct path sync 3 0.08 0.16

log buffer space 53 0.13 1.13

reliable message 2 0.00 0.00

enq: RO - fast object reuse 1 0.00 0.00

db file sequential read 1 0.00 0.00

enq: CR - block range reuse ckpt 1 0.02 0.02

log file sync 1 0.00 0.00

### 4.如何优雅的做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中，分区表是一个很好的解决大表性能的问题方案，但是随之带来的维护量和维护难度也随之上升。我们能做到最好的就是尽可能的解决监控盲点，做到提前运维，提前发现风险，避免让数据进入到默认分区中。