Datapump Import Performance Improvement for IOT with 1.9B rows

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

Source: PARTITION BY HASH and Target: PARTITION BY VALUES

IOT has approximately 1.9B rows.

Version 19.12.2.0.0

### References:

In What Order Are Indexes Built During Datapump Import (IMPDP) and How to Optimize the Index Creation (Doc ID 1966442.1)	

Normally index creation in a schema level import will follow this order:
1. Metadata import (user, roles & system privileges)
2. Objects like type, sequences and related grants
3. Tables, table data, table grants
4. Indexes

### 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 to use up to 1.5xCPU and monitor.
# FUD in play.
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 to use up to 1.5xCPU and monitor.
# FUD in play.
parallel=8
cluster=N
schemas=staging,s2,s3,s4,s5
# STATISTICS was already exclude from import.
# Exclude is not necessary, since will import everything.
exclude=STATISTICS,REF_CONSTRAINT,GRANT,INDEX,TRIGGER
content=DATA_ONLY
logfile=impdp.log
dumpfile=schema%U.dmp
directory=dpump_dir

### Here is the export dmp.
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 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

The result for import was 50% faster for IOT with ~1.90B rows.

There are probably more options to test and tune; however, would be too time consuming.

Writing this blog post, I realized I have made the mistake of not requesting export/import parameter files and logs as there are redundancies for export and import parameters.

Advertisement