Oracle 11.2.0.4 on a 4-node RAC cluster on Exadata
I have been tasked with moving a large volume of partitioned data from one tablespace to another in preparation for a 12c upgrade. The tablespace contains around 500,000 segments (table sub/partitions, local index segments, a small number of LOBs), totalling around 15TB in size.
Functionally, this is working fine. I've written a PL/SQL program to construct and execute statements of the form
ALTER TABLE table MOVE PARTITION part TABLESPACE new_tablespace
COMPRESS FOR QUERY HIGH --optional
PARALLEL 8 --optional
UPDATE INDEXES (
index1 (PARTITION TABLESPACE new_tablespace),
index2 (PARTITION TABLESPACE new_tablespace)
);
The problem is it's taking too long to move the partitions. At best, for small segments, I can move around 100 table partitions per minute. For larger segments (>100MB), this can be as little as 4 partitions per minute.
The database is a busy data warehouse, with large overnight loads, a small amount of intraday loads, and a fair amount of query activity during the day. This means that I have to be careful to maintain indexes during the load, and to not introduce any significant contention for other processes. As a result, I can't run too many concurrent jobs, as the library cache contention causes delays to queries. The data must be recoverable, so NOLOGGING is not an option.
Apologies for such an open-ended question, but I'm looking for any suggestions on optimising the process. Anyone have any experience of moving similar volumes, or suggestions on how to improve the throughput?