Oracle数据库分区表SPLIT操作导致归档疯涨

问题现象：

按时间列范围分区的表，设置有maxvalue，经检查发现99%的数据全都集中在P_MAX分区，数据分布严重不均匀，考虑将P_MAX数据按月插入到新分区，执行下面操作：

ALTER TABLE CJC.RANGE_PART_CJC split PARTITION P_MAX at (TO_DATE('2023-11-30','YYYY-MM-DD')) into (partition part_202311,partition P_MAX);

执行后，前台卡住，后台归档疯涨 ......

之前考虑到小于2023-11-30的数据很少，只有两条数据，split 操作以为只是将这两条数据插入到新的分区，实际上并不是这样。

环境说明：

DB:Oracle 11.2.0.4.0

问题重现：

创建测试数据

create tablespace cjc datafile '/oradata/test/cjc.dbf' size 10M autoextend on;
create user cjc identified by "a" default tablespace cjc;
grant dba to cjc;
conn cjc/a

开启数据库补充日志，用于后续日志挖掘

select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI from v$database;
alter database add supplemental log data;

创建范围分区表 range_part_cjc

create table range_part_cjc(id int,c_date date,contents varchar2(10))
partition by range (c_date)
(
partition p1 values less than (TO_DATE('2023-10-10 00:00:00','YYYY-MM-DD HH24:MI:SS')),
partition p2 values less than (TO_DATE('2023-10-11 00:00:00','YYYY-MM-DD HH24:MI:SS')),
partition p3 values less than (TO_DATE('2023-10-12 00:00:00','YYYY-MM-DD HH24:MI:SS')),
partition p4 values less than (TO_DATE('2023-10-13 00:00:00','YYYY-MM-DD HH24:MI:SS')),
partition p5 values less than (TO_DATE('2023-10-14 00:00:00','YYYY-MM-DD HH24:MI:SS')),
partition p6 values less than (TO_DATE('2023-10-15 00:00:00','YYYY-MM-DD HH24:MI:SS')),
partition p_max values less than ( maxvalue )
);

插入数据：

insert into range_part_cjc values(1,TO_DATE('2023-10-10 01:00:32','YYYY-MM-DD HH24:MI:SS'),'a');
insert into range_part_cjc values(2,TO_DATE('2023-10-10 02:00:32','YYYY-MM-DD HH24:MI:SS'),'b');
insert into range_part_cjc values(3,TO_DATE('2023-10-13 03:00:32','YYYY-MM-DD HH24:MI:SS'),'c');
insert into range_part_cjc values(4,TO_DATE('2023-11-01 04:00:32','YYYY-MM-DD HH24:MI:SS'),'d');
insert into range_part_cjc values(5,TO_DATE('2023-11-01 05:00:32','YYYY-MM-DD HH24:MI:SS'),'e');
insert into range_part_cjc values(6,TO_DATE('2023-12-10 06:00:32','YYYY-MM-DD HH24:MI:SS'),'f');
insert into range_part_cjc values(7,TO_DATE('2023-12-11 07:00:32','YYYY-MM-DD HH24:MI:SS'),'g');
insert into range_part_cjc values(8,TO_DATE('2023-12-12 08:00:32','YYYY-MM-DD HH24:MI:SS'),'h');
insert into range_part_cjc values(9,TO_DATE('2023-12-13 09:00:32','YYYY-MM-DD HH24:MI:SS'),'i');
insert into range_part_cjc values(10,TO_DATE('2023-12-14 10:00:32','YYYY-MM-DD HH24:MI:SS'),'j');
insert into range_part_cjc values(10,TO_DATE('2023-12-15 10:00:32','YYYY-MM-DD HH24:MI:SS'),'j');
insert into range_part_cjc values(10,TO_DATE('2023-12-16 10:00:32','YYYY-MM-DD HH24:MI:SS'),'j');
insert into range_part_cjc values(10,TO_DATE('2023-12-17 10:00:32','YYYY-MM-DD HH24:MI:SS'),'j');
insert into range_part_cjc values(10,TO_DATE('2023-12-18 10:00:32','YYYY-MM-DD HH24:MI:SS'),'j');
insert into range_part_cjc values(10,TO_DATE('2023-12-19 10:00:32','YYYY-MM-DD HH24:MI:SS'),'j');
insert into range_part_cjc values(10,TO_DATE('2023-12-20 10:00:32','YYYY-MM-DD HH24:MI:SS'),'j');
......
commit;

查看数据

set pagesize 100
select id,to_char(c_date,'YYYY-MM-DD HH24:MI:SS') c_date,contents from range_part_cjc;


ID C_DATE CONTENTS
---------- ------------------- ----------
1 2023-10-10 01:00:32 a
2 2023-10-10 02:00:32 b
3 2023-10-13 03:00:32 c
4 2023-11-01 04:00:32 d
5 2023-11-01 05:00:32 e
6 2023-12-10 06:00:32 f
7 2023-12-11 07:00:32 g
8 2023-12-12 08:00:32 h
9 2023-12-13 09:00:32 i
10 2023-12-14 10:00:32 j
......

查看分区

set line 300
col HIGH_VALUE for a100
select partition_name,HIGH_VALUE from dba_tab_partitions where table_name='RANGE_PART_CJC';
PARTITION_NAME HIGH_VALUE
------------------------------ ----------------------------------------------------------------------------------------------------
P1 TO_DATE(' 2023-10-10 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P2 TO_DATE(' 2023-10-11 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P3 TO_DATE(' 2023-10-12 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P4 TO_DATE(' 2023-10-13 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P5 TO_DATE(' 2023-10-14 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P6 TO_DATE(' 2023-10-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P_MAX MAXVALUE




7 rows selected.

查看分区数据量

SELECT COUNT(*) FROM CJC.RANGE_PART_CJC; ---10485776
SELECT COUNT(*) FROM CJC.RANGE_PART_CJC PARTITION(P1); ---0
SELECT COUNT(*) FROM CJC.RANGE_PART_CJC PARTITION(P2); ---2
SELECT COUNT(*) FROM CJC.RANGE_PART_CJC PARTITION(P3); ---0
SELECT COUNT(*) FROM CJC.RANGE_PART_CJC PARTITION(P4); ---0
SELECT COUNT(*) FROM CJC.RANGE_PART_CJC PARTITION(P5); ---1
SELECT COUNT(*) FROM CJC.RANGE_PART_CJC PARTITION(P6); ---0
SELECT COUNT(*) FROM CJC.RANGE_PART_CJC PARTITION(P_MAX); ---10485773

大多数数据集中在P_MAX分区。

添加索引

创建local非前缀索引(分区键不是索引第一列)

主键值不能创建local分区索引，因为local分区索引不能保证全局唯一性，可以创建global分区索引。

create index i_local_01 on RANGE_PART_CJC(ID,C_DATE) local;

创建全局索引

create index i_global_01 on RANGE_PART_CJC(CONTENTS,C_DATE) global;

查看索引信息

set line 300
col INDEX_NAME for a30
col TABLE_NAME for a25
col COLUMN_NAME for a30
col INDEX_OWNER for a20
SELECT INDEX_OWNER,INDEX_NAME,TABLE_NAME,COLUMN_NAME FROM DBA_IND_COLUMNS WHERE TABLE_NAME='RANGE_PART_CJC' ORDER BY 2,4;

INDEX_OWNER INDEX_NAME TABLE_NAME COLUMN_NAME
-------------------- ------------------------------ ------------------------- ------------------------------
CJC I_GLOBAL_01 RANGE_PART_CJC CONTENTS
CJC I_GLOBAL_01 RANGE_PART_CJC C_DATE
CJC I_LOCAL_01 RANGE_PART_CJC C_DATE
CJC I_LOCAL_01 RANGE_PART_CJC ID

查看索引状态

SELECT PARTITION_NAME,INDEX_NAME,STATUS FROM DBA_IND_PARTITIONS WHERE INDEX_OWNER='CJC' AND INDEX_NAME IN ('I_GLOBAL_01','I_LOCAL_01');
PARTITION_NAME INDEX_NAME STATUS
------------------------------ ------------------------------ --------
P1 I_LOCAL_01 USABLE
P2 I_LOCAL_01 USABLE
P3 I_LOCAL_01 USABLE
P4 I_LOCAL_01 USABLE
P5 I_LOCAL_01 USABLE
P6 I_LOCAL_01 USABLE
P_MAX                          I_LOCAL_01                     USABLE


7 rows selected.

SELECT INDEX_NAME,STATUS FROM DBA_INDEXES WHERE TABLE_NAME='RANGE_PART_CJC';
INDEX_NAME STATUS
------------------------------ --------
I_LOCAL_01 N/A
I_GLOBAL_01 VALID

SPILT PARTITION操作

使用10046跟踪操作过程

SQLPLUS / AS SYSDBA
SET TIMING ON;
ALTER SYSTEM SWITCH LOGFILE;
ALTER SESSION SET nls_date_format='DD-MM-YYYY HH24:MI:SS';
ALTER SESSION SET tracefile_identifier='10046B';
ALTER SESSION SET max_dump_file_size = unlimited;
ALTER SESSION SET timed_statistics = true;
ALTER SESSION SET statistics_level=all;
ALTER SESSION SET events '10046 trace name context forever, level 12';
ALTER SESSION SET "_px_trace" = low , messaging;
ALTER TABLE CJC.RANGE_PART_CJC split PARTITION P_MAX at (TO_DATE('2023-11-30','YYYY-MM-DD')) into (partition part_202311,partition P_MAX);
ALTER SESSION SET events '10046 trace name context off';
ALTER SESSION SET "_px_trace" = none;

耗时16分钟

将P_MAX分区中小于2023-11-30的放在part_202311分区，大于等于2023-11-30的放在P_MAX分区。

查看数据量，将p_max中两条数据插入到新分区。

SELECT COUNT(*) FROM CJC.RANGE_PART_CJC PARTITION(P_MAX); ---10485771
SELECT COUNT(*) FROM CJC.RANGE_PART_CJC PARTITION(PART_202311); ---2

查询表监控,统计信息没更新，不准确

SELECT TABLE_OWNER,TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,INSERTS,UPDATES,DELETES FROM dba_tab_modifications WHERE TABLE_NAME='RANGE_PART_CJC';
SELECT * FROM sys.mon_mods$ WHERE obj#=92846;
OBJ# INSERTS UPDATES DELETES TIMESTAMP FLAGS DROP_SEGMENTS
---------- ---------- ---------- ---------- ------------------- ---------- -------------
92846 10485773 0 0 25-12-2023 16:05:06 0 0

查看 10046 trace

oracle@SATEST-AW-001:/oracle/app/oracle/diag/rdbms/test/test/trace$ls -lrth *10046B*
-rw-r----- 1 oracle oinstall 3.5K Dec 25 15:59 test_ora_2952136_10046B.trm
-rw-r----- 1 oracle oinstall 300K Dec 25 15:59 test_ora_2952136_10046B.trc

tkprof test_ora_2952136_10046B.trc 08.trc
tkprof test_ora_2952136_10046B.trc 09.trc sys=no

vi 08.trc

********************************************************************************
SQL ID: fcwtdvc6adr2a Plan Hash: 1554261478


ALTER TABLE CJC.RANGE_PAR


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 6.25 16.39 2 26302 28455 10485773
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 6.25 16.39 2 26302 28455 10485773


Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 86
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 LOAD AS SELECT (cr=28692 pr=0 pw=26151 time=15471596 us)
10485773 10485773 10485773 PARTITION RANGE SINGLE PARTITION: 7 7 (cr=26210 pr=0 pw=0 time=1977135 us cost=2 size=2378 card=82)
10485773 10485773 10485773 TABLE ACCESS FULL RANGE_PART_CJC PARTITION: 7 7 (cr=26210 pr=0 pw=0 time=794551 us cost=2 size=2378 card=82)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
Disk file operations I/O 10 0.00 0.00
direct path write 825 0.00 0.07
control file sequential read 60 0.00 0.00
db file sequential read 8 0.00 0.00
Data file init write 30 0.00 0.00
db file single write 3 0.00 0.00
control file parallel write 9 0.00 0.01
rdbms ipc reply 3 0.00 0.00
log file switch completion 5 0.10 0.51
log file switch (checkpoint incomplete) 10 1.88 8.46
direct path sync 1 0.06 0.06
reliable message 4 0.00 0.00
enq: RO - fast object reuse 2 0.91 0.91
enq: CR - block range reuse ckpt 2 0.00 0.00
log file sync 1 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 19.29 19.29
********************************************************************************

通过10046 trace可以看到，在进行spilt分区时，Execute 对应rows 10485773，接近全表数据量，大量的direct path write、log file switch (checkpoint incomplete)等待事件，说明并不是只移动了两行数据。

日志挖掘

切换归档

SQL> alter system switch logfile;

查看归档文件，移动两行数据，产生了214MB归档文件。

oracle@cjcdb-001:/redis/oradata/arch$ls -lrth
total 214M
-rw-r----- 1 oracle oinstall 39M Dec 25 15:59 1_1752_1130341000.dbf
-rw-r----- 1 oracle oinstall 39M Dec 25 15:59 1_1753_1130341000.dbf
-rw-r----- 1 oracle oinstall 39M Dec 25 15:59 1_1754_1130341000.dbf
-rw-r----- 1 oracle oinstall 39M Dec 25 15:59 1_1755_1130341000.dbf
-rw-r----- 1 oracle oinstall 39M Dec 25 15:59 1_1756_1130341000.dbf
-rw-r----- 1 oracle oinstall 20M Dec 25 16:00 1_1757_1130341000.dbf


/redis/oradata/arch/1_1752_1130341000.dbf
/redis/oradata/arch/1_1753_1130341000.dbf
/redis/oradata/arch/1_1754_1130341000.dbf
/redis/oradata/arch/1_1755_1130341000.dbf
/redis/oradata/arch/1_1756_1130341000.dbf
/redis/oradata/arch/1_1757_1130341000.dbf

--11g 开始日志挖掘(和10g语法稍有差别)

EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>'/redis/oradata/arch/1_1752_1130341000.dbf',Options=>dbms_logmnr.new);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>'/redis/oradata/arch/1_1753_1130341000.dbf',Options=>dbms_logmnr.addfile);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>'/redis/oradata/arch/1_1754_1130341000.dbf',Options=>dbms_logmnr.addfile);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>'/redis/oradata/arch/1_1755_1130341000.dbf',Options=>dbms_logmnr.addfile);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>'/redis/oradata/arch/1_1756_1130341000.dbf',Options=>dbms_logmnr.addfile);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>'/redis/oradata/arch/1_1757_1130341000.dbf',Options=>dbms_logmnr.addfile);
EXEC DBMS_LOGMNR.START_LOGMNR(OPTIONS=>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
CREATE TABLE T1225C AS SELECT * FROM V$LOGMNR_CONTENTS;
EXEC DBMS_LOGMNR.END_LOGMNR;
---Elapsed: 00:03:17.40

查看表数据量

SELECT COUNT(*) FROM T1225C; ---10486320

查看归档中执行次数多的SQL

set line 300
set pagesize 1000
col xx for a100
select count(*),substr(sql_redo,1,100) xx from t1225C group by substr(sql_redo,1,100) order by 1 desc;

COUNT(*) XX
---------- ----------------------------------------------------------------------------------------------------
10486163
54 set transaction read write;
44 commit;
10 rollback;
3 insert into "SYS"."OBJ$"("OBJ#","DATAOBJ#","OWNER#","NAME","NAMESPACE","SUBNAME","TYPE#","CTIME","MT
2 update "SYS"."SCHEDULER$_JOB" set "JOB_STATUS" = '1', "NEXT_RUN_DATE" = TO_TIMESTAMP_TZ('26-DEC-23 1
2 insert into "SYS"."DEFERRED_STG$"("OBJ#","PCTFREE_STG","PCTUSED_STG","SIZE_STG","INITIAL_STG","NEXT_
2 insert into "SYS"."SCHEDULER$_JOB_RUN_DETAILS"("LOG_ID","LOG_DATE","REQ_START_DATE","START_DATE","RU
2 insert into "SYS"."SEG$"("FILE#","BLOCK#","TYPE#","TS#","BLOCKS","EXTENTS","INIEXTS","MINEXTS","MAXE
2 insert into "SYS"."SCHEDULER$_EVENT_LOG"("LOG_ID","LOG_DATE","TYPE#","NAME","OWNER","CLASS_ID","OPER
2 update "SYS"."SEG$" set "TYPE#" = '5', "BLOCKS" = '1024', "EXTENTS" = '1', "INIEXTS" = '1024', "MINE
2 update "SYS"."SCHEDULER$_JOB" set "JOB_STATUS" = '3', "LAST_START_DATE" = TO_TIMESTAMP_TZ('25-DEC-23
2 insert into "SYS"."INDPART$"("OBJ#","DATAOBJ#","BO#","PART#","HIBOUNDLEN","HIBOUNDVAL","FLAGS","TS#"
2 update "SYS"."SEG$" set "TYPE#" = '3', "BLOCKS" = '1024', "EXTENTS" = '1', "INIEXTS" = '1024', "MINE
2 update "SYS"."SEG$" set "TYPE#" = '3', "BLOCKS" = '32640', "EXTENTS" = '103', "INIEXTS" = '8', "MINE
1 delete from "SYS"."SEG$" where "FILE#" = '8' and "BLOCK#" = '2193' and "TYPE#" = '3' and "TS#" = '8'
1 ALTER TABLE CJC.RANGE_PART_CJC split PARTITION P_MAX at (TO_DATE('2023-11-30','YYYY-MM-DD')) into (p
1 update "SYS"."PARTOBJ$" set "PARTCNT" = '8' where "OBJ#" = '92839' and "PARTCNT" = '7' and ROWID = '
1 update "SYS"."TABPART$" set "HIBOUNDVAL" = 'TO_DATE('' 2023-11-30 00:00:00'', ''SYYYY-MM-DD HH24:MI:
1 update "SYS"."AUD$" set "SQLBIND" = NULL, "SQLTEXT" = NULL where "SESSIONID" = '88667' and "ENTRYID"
1 insert into "SYS"."AUD$"("SESSIONID","ENTRYID","STATEMENT","TIMESTAMP#","USERID","USERHOST","TERMINA
1 update "SYS"."INDPART$" set "HIBOUNDVAL" = 'MAXVALUE' where "OBJ#" = '92859' and "DATAOBJ#" = '92859
1 insert into "SYS"."TABPART$"("OBJ#","DATAOBJ#","BO#","PART#","HIBOUNDLEN","HIBOUNDVAL","TS#","FILE#"
1 update "SYS"."OBJ$" set "OBJ#" = '92846', "DATAOBJ#" = '92858', "TYPE#" = '19', "CTIME" = TO_DATE('2
1 update "SYS"."SEG$" set "TYPE#" = '3', "BLOCKS" = '26624', "EXTENTS" = '1', "INIEXTS" = '1024', "MIN
1 update "SYS"."TAB$" set "DATAOBJ#" = NULL, "TS#" = '0', "FILE#" = '0', "BLOCK#" = '0', "BOBJ#" = NUL
1 update "SYS"."SEG$" set "TYPE#" = '5', "BLOCKS" = '26624', "EXTENTS" = '97', "INIEXTS" = '8', "MINEX
1 update "SYS"."OBJ$" set "OBJ#" = '13588', "DATAOBJ#" = NULL, "TYPE#" = '66', "CTIME" = TO_DATE('24-0
1 delete from "SYS"."SEG$" where "FILE#" = '8' and "BLOCK#" = '3378' and "TYPE#" = '3' and "TS#" = '8'
1 update "SYS"."IND$" set "DATAOBJ#" = '92856', "TS#" = '8', "FILE#" = '8', "BLOCK#" = '55602', "INDME
1 update "SYS"."TABPART$" set "DATAOBJ#" = '92858', "PART#" = '81', "TS#" = '8', "FILE#" = '8', "BLOCK
1 update "SYS"."PARTOBJ$" set "PARTCNT" = '8' where "OBJ#" = '92848' and "PARTCNT" = '7' and ROWID = '
1 update "SYS"."SCHEDULER$_EVENT_LOG" set "ADDITIONAL_INFO" = NULL where "LOG_ID" = '6935' and "LOG_DA
1 delete from "SYS"."OBJ$" where "OBJ#" = '92855' and "DATAOBJ#" = '92855' and "OWNER#" = '86' and "NA
1 update "SYS"."OBJ$" set "OBJ#" = '92839', "DATAOBJ#" = NULL, "TYPE#" = '2', "CTIME" = TO_DATE('25-12
1 delete from "SYS"."INDPART$" where "OBJ#" = '92855' and "DATAOBJ#" = '92855' and "BO#" = '92848' and
1 update "SYS"."SCHEDULER$_EVENT_LOG" set "ADDITIONAL_INFO" = NULL where "LOG_ID" = '6936' and "LOG_DA
1 Unsupported
1 update "SYS"."INDPART$" set "HIBOUNDVAL" = 'TO_DATE('' 2023-11-30 00:00:00'', ''SYYYY-MM-DD HH24:MI:
1 update "SYS"."OBJ$" set "OBJ#" = '13589', "DATAOBJ#" = NULL, "TYPE#" = '66', "CTIME" = TO_DATE('24-0
         1 update "SYS"."OBJ$" set "OBJ#" = '1', "DATAOBJ#" = '92865', "TYPE#" = '0', "CTIME" = TO_DATE('24-08-
41 rows selected.

可以看到sql_redo为NULL占比最大，10486163和表数据量相近。

SELECT COUNT(*) FROM CJC.RANGE_PART_CJC; ---10485776

查看sql_redo is null 对应的对象信息

COL USERNAME FOR A15
SELECT ROW_ID,USERNAME,SEG_TYPE,DATA_OBJ# FROM T1225C WHERE sql_redo IS NULL AND ROWNUM