Datapump is used for Instantiating Oracle GoldenGate with an Initial Load.

Client is facing performance issues with datapump for Index Organized Table (IOT) with approximately 1.9B rows.

The root cause of the issue is source (DW) and target (OLTP) have different partition design for IOT.

Source: PARTITION BY HASH and Target: PARTITION BY VALUES

This is like trying to fit a square peg into a round hole.

After several triages, import time was improved by more than 50%.

Here are the details and triages.

Database Version: 19.12.2.0.0

-------------------------------------------------------
### This is the parameter file for export.
-------------------------------------------------------

exclude=STATISTICS
compression=ALL
# Is it necessary to export staging?
schemas=staging,s2,s3,s4,s5
flashback_scn=61727639035
# There are 8 CPUs and possible/typical to use 1.5-2.0 times the CPU and monitor.
# Using more CPUs was never tested due to time constraints.
# Recalled DBA was apprehensive to take risk in production.
parallel=8
content=DATA_ONLY
# Added for improvements to avoid 1 process doing all the work.
# Tested using 1G/2G and no huge improvements.
filesize=4G
logfile=expdp.log
dumpfile=schema%U.dmp
directory=dpump_dir

-------------------------------------------------------
### This is the parameter file for import.
-------------------------------------------------------

table_exists_action=TRUNCATE
# Added for performance improvements since there is no standby database.
transform=DISABLE_ARCHIVE_LOGGING:Y
logtime=ALL
metrics=Y
# There are 8 CPUs and possible/typical to use 1.5-2.0 times the CPU and monitor. 
# Using more CPUs was never tested due to time constraints and risks. 
parallel=8
cluster=N
schemas=staging,s2,s3,s4,s5
# STATISTICS was already exclude from import.
# Exclude is not necessary, since objects were never exported.
exclude=STATISTICS,REF_CONSTRAINT,GRANT,INDEX,TRIGGER
content=DATA_ONLY
logfile=impdp.log
dumpfile=schema%U.dmp
directory=dpump_dir

-------------------------------------------------------
### Here are the export dump files.
-------------------------------------------------------

Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_09 is:
/export/schema01.dmp
/export/schema02.dmp
/export/schema03.dmp
/export/schema04.dmp
/export/schema05.dmp
/export/schema06.dmp
/export/schema07.dmp
/export/schema08.dmp
/export/schema09.dmp
/export/schema10.dmp
/export/schema11.dmp
/export/schema12.dmp
/export/schema13.dmp
/export/schema14.dmp
/export/schema15.dmp
/export/schema16.dmp
/export/schema17.dmp
/export/schema18.dmp
/export/schema19.dmp

-------------------------------------------------------
### Here is the import timing for IOT.
-------------------------------------------------------

16-MAR-23 20:25:48.971: W-4 . . imported "H01" 3.946 GB 474235896 rows in 12183 seconds using external_table
16-MAR-23 14:42:56.470: W-7 . . imported "H02" 3.952 GB 475007010 rows in 7113 seconds using external_table
16-MAR-23 23:45:07.344: W-8 . . imported "H03" 3.945 GB 474120834 rows in 11956 seconds using external_table
16-MAR-23 17:02:44.346: W-6 . . imported "H04" 3.949 GB 474655428 rows in 8386 seconds using external_table

16-MAR-23 23:58:02.815: Job "SYSTEM"."SYS_IMPORT_SCHEMA_02" completed at Thu Mar 16 23:58:02 2023 elapsed 0 11:18:20

There are probably more options to test and tune; however, there is time constraints to have imported completed. Futhermore, how much more gain can be achieved?