I recently observed that the total time taken to split an empty partition, in 10.2.0.5, has increased by many fold—i.e. from under 30 seconds to 60 minutes.
SPLIT PARTITION operation is, simply, creating two new partitions and redistributing the rows from the partition being split into the two new partitions. Often one new partition contains all the rows from the partition being split, while other does not contain any rows; Oracle database engine can detect such situations and can optimize the split operation, such optimization is referred as fast SPLIT PARTITION operation.
Database Administrator’s Guide, 10g Release 2, explains how the split works and the conditions should be met to achieve fast SPLIT PARTITION operation. Oracle support document 378138.1 mentions that accurate statistics should be gathered not just on the (sub) partition to be split, but on the indexes of the table as well to achieve fast SPLIT PARTITION. The reason for this is that oracle executes recursive queries on the (sub)partition to be split in order to see the number of rows AT <value> and >= AT <value>, so that it can be determined whether one of the resulting partitions is empty.
Also, Oracle support document 1268714.1 shows how to perform a fast SPLIT PARTITION and how to verify with a simple test case.
- First split of an empty partition took about 6 seconds.
-- Partition statistics before 1st split
SELECT dtp.table_name, dtp.partition_name, dtp.num_rows
FROM dba_tab_partitions dtp
WHERE dtp.table_name = 'RAM_SLOW_PART_TAB2'
AND dtp.partition_name IN ('RSLP2_SEP_2017',
'RSLP2_OCT_2017',
'RSLP2_NOV_2017',
'RSLP2_DEC_2017',
'RSLP2_MAX_PART');
TABLE_NAME PARTITION_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
RAM_SLOW_PART_TAB2 RSLP2_SEP_2017 0
RAM_SLOW_PART_TAB2 RSLP2_MAX_PART 0
2 rows selected.
Elapsed: 00:00:00.08
-- Perform 1st Split
ALTER TABLE RAM_SLOW_PART_TAB2
SPLIT PARTITION RSLP2_MAX_PART
AT (TO_DATE ('2017-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
INTO (PARTITION RSLP2_OCT_2017, PARTITION RSLP2_MAX_PART);
Table altered.
Elapsed: 00:00:05.77
- However, a subsequent split of empty partition took about 28 minutes; as a result of database engine executing recursive queries to obtain number of rows due to missing statistics.
-- Partition statistics after 1st split
SELECT dtp.table_name, dtp.partition_name, dtp.num_rows
FROM dba_tab_partitions dtp
WHERE dtp.table_name = 'RAM_SLOW_PART_TAB2'
AND dtp.partition_name IN ('RSLP2_SEP_2017',
'RSLP2_OCT_2017',
'RSLP2_NOV_2017',
'RSLP2_DEC_2017',
'RSLP2_MAX_PART');
TABLE_NAME PARTITION_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
RAM_SLOW_PART_TAB2 RSLP2_OCT_2017
RAM_SLOW_PART_TAB2 RSLP2_SEP_2017 0
RAM_SLOW_PART_TAB2 RSLP2_MAX_PART
3 rows selected.
Elapsed: 00:00:00.08
-- Perform 2nd Split
ALTER TABLE RAM_SLOW_PART_TAB2
SPLIT PARTITION RSLP2_MAX_PART
AT (TO_DATE ('2017-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
INTO (PARTITION RSLP2_NOV_2017, PARTITION RSLP2_MAX_PART);
Table altered.
Elapsed: 00:27:30.04
- As you see, below, if you gather statistics on empty partition, time taken by the database engine to perform a fast SPLIT PARTITION operation is under 2 seconds.
-- Gather statistics for MAX partition
BEGIN
DBMS_STATS.GATHER_TABLE_STATS (ownname => 'LABUSER',
tabname => 'RAM_SLOW_PART_TAB2',
partname => 'RSLP2_MAX_PART',
granularity => 'PARTITION');
END;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.99
-- Partition statistics before 3rd split
SELECT dtp.table_name, dtp.partition_name, dtp.num_rows
FROM dba_tab_partitions dtp
WHERE dtp.table_name = 'RAM_SLOW_PART_TAB2'
AND dtp.partition_name IN ('RSLP2_SEP_2017',
'RSLP2_OCT_2017',
'RSLP2_NOV_2017',
'RSLP2_DEC_2017',
'RSLP2_MAX_PART');
TABLE_NAME PARTITION_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
RAM_SLOW_PART_TAB2 RSLP2_OCT_2017
RAM_SLOW_PART_TAB2 RSLP2_NOV_2017
RAM_SLOW_PART_TAB2 RSLP2_SEP_2017 0
RAM_SLOW_PART_TAB2 RSLP2_MAX_PART 0
4 rows selected.
Elapsed: 00:00:01.41
-- Perform 3rd Split
ALTER TABLE RAM_SLOW_PART_TAB2
SPLIT PARTITION RSLP2_MAX_PART
AT (TO_DATE ('2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
INTO (PARTITION RSLP2_DEC_2017, PARTITION RSLP2_MAX_PART);
Table altered.
Elapsed: 00:00:01.68
-- Partition statistics after 3rd split
SELECT dtp.table_name, dtp.partition_name, dtp.num_rows
FROM dba_tab_partitions dtp
WHERE dtp.table_name = 'RAM_SLOW_PART_TAB2'
AND dtp.partition_name IN ('RSLP2_SEP_2017',
'RSLP2_OCT_2017',
'RSLP2_NOV_2017',
'RSLP2_DEC_2017',
'RSLP2_MAX_PART');
TABLE_NAME PARTITION_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
RAM_SLOW_PART_TAB2 RSLP2_OCT_2017
RAM_SLOW_PART_TAB2 RSLP2_NOV_2017
RAM_SLOW_PART_TAB2 RSLP2_DEC_2017
RAM_SLOW_PART_TAB2 RSLP2_SEP_2017 0
RAM_SLOW_PART_TAB2 RSLP2_MAX_PART
5 rows selected.
Elapsed: 00:00:00.09
In short, to perform a fast SPLIT PARTITION operation, ensure statistics are made available to database engine for the partition to be split and on indexes of the table when splitting a partition.