Speed-up expdp/impdp

 
1)Identify known issues/bugs and apply the required patches pro-actively on the souce & target servers
Checklist For Slow Performance Of DataPump Export (expdp) And Import (impdp) (Doc ID 453895.1)








2) Establish a dedicate connection between source and Target servers ( if feasible)
2.1. Avoid public network to transfer the data
        Open dedicated bandwidth between the source & Target server ( ip-ip copy: to speed up the copy over High Bandwidth card ) 

2.2.     Listener to be created on this network

2.3.     Create a DBLINK between source and target using this  network
2.4.     Do network based exp/imp
2.5      Don't copy LOB using network copy
ref: Performance Problems When Transferring LOBs Using IMPDP With NETWORK_LINK (Doc ID 1488229.1)








3)  Unload data only. Once data is unloaded, Later create indexes, constraints( enable novalidate if you are sure data is intact)
  ( Please let IMPDP create the partitioned table Ref: Slow DataPump Import (Impdp) Performance using Parallel Option with Pre-created Partitioned Tables (Doc ID 2098931.1))








4) In-case if you have RAC, Try to avail parallelism by running parallel exp/imp from different instances.







5) split into Multiple jobs

  5.1) Large tables can be typically exported or imported individually and smaller tables were grouped together into several batches with parallel options.







6) Table with LOB columns
   6.1) Isolate the BIG LOB tables ( don't club it together with the other small tables )
   6.1) Enable CACHE to improvise the import performance.
   6.2) Logically split the tables into multiple exp/imp jobs based on application logic using primary key or unique key.
   6.3 use DATA_OPTIONS=DISABLE_APPEND_HINT to avoid TM lock while doing multiple
import on the same lob table.
  6.4) Avoid Network COPY for LOB
  Ref: Performance Problems When Transferring LOBs Using IMPDP With NETWORK_LINK (Doc ID 1488229.1)







7) In-case if you want to use multiple imp on the same table
   7.1) use DATA_OPTIONS=DISABLE_APPEND_HINT to avoid TM lock while doing multiple import on the same lob table.





8) Undocumented parameters
    _disable_logging ( skip redo generation, it is very dangerous command , in-case if instance crash, data may get corrupted, end up doing reimport again)






9) estimate enough PGA for  creating indexes or enabling constraint at end of data unloading.