Boost Oracle Data Pump Export/Import Speed: Key Parameters and Settings

Data Pump parameters that impact performance

access_method : Choose between direct_path and external_table when the default method is slow; testing each method is the only way to know which is faster.

cluster=n : In RAC environments setting cluster=n can noticeably speed up basic Data Pump operations; it only affects Data Pump API calls.

data_options=disable_append_hint : Safe to use only when importing into existing tables/partitions with very few objects (≤10) and no concurrent sessions selecting from those objects. Available from Oracle 11.2.0.1 onward.

estimate=statistics (vs blocks): statistics is faster and sufficiently accurate for most cases; blocks gives a more precise size estimate but takes longer.

exclude=comment : Skipping comments reduces execution time when they are not needed.

exclude=statistics : Omitting statistics shortens the export; after import, run DBMS_STATS.GATHER_DATABASE_STATS manually. To avoid hangs on small tables, temporarily disable automatic stats collection:

exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name=>'auto optimizer stats collection');
exec DBMS_AUTO_TASK_ADMIN.ENABLE(client_name=>'auto optimizer stats collection');
exec sys.dbms_scheduler.DISABLE('GATHER_STATS_JOB');
exec sys.dbms_scheduler.ENABLE('GATHER_STATS_JOB');

network_link : Limits parallelism; use only as a last resort because it is usually slower than using dump files.

parallel : Setting parallel=N (N>1) can improve performance if multiple CPUs are available and dump files are not a bottleneck. Ensure the number of dump files is at least equal to the parallel degree.

query and remap_* : Both increase load proportionally to the amount of data queried.

Database parameters that affect Data Pump performance

aq_tm_processes=0 : Setting to 0 may degrade advanced queue operations and thus Data Pump performance; set to a value >0 if queues are used.

deferred_segment_creation=true : Useful for imports; it avoids allocating space for empty tables. No significant effect on exports. Effective from 11.2.0.2.

filesystemio_option : In ACFS environments, any value other than NONE can slow down exports.

NLS_CHARACTERSET / NLS_NCHAR_CHARACTERSET : Mismatches between source and target can force single‑process imports for partitioned tables, reducing parallelism.

NLS_COMP / NLS_SORT : Setting both to BINARY can speed up Data Pump; apply per‑session via a logon trigger:

CREATE OR REPLACE TRIGGER sys.expdp_nls_session_settings AFTER LOGON ON DATABASE DECLARE V_MODULE VARCHAR2(60); BEGIN SELECT SYS_CONTEXT('USERENV','MODULE') INTO V_MODULE FROM DUAL; IF UPPER(V_MODULE) LIKE 'UDE%' THEN EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_COMP="BINARY"'; EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_SORT="BINARY"'; END IF; END; /

parallel_force_local=true : In RAC, this improves performance and avoids parallel DML bugs; available from 11.2.0.2.

streams_pool_size : To avoid bug 17365043, set to the value returned by the following query and apply with ALTER SYSTEM:

SELECT 'ALTER SYSTEM SET STREAMS_POOL_SIZE='||(MAX(TO_NUMBER(TRIM(c.ksppstvl)))+67108864)||' SCOPE=SPFILE;' 
FROM sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c 
WHERE a.indx=b.indx AND a.indx=c.indx 
  AND LOWER(a.ksppinm) IN ('__streams_pool_size','streams_pool_size');

_memory_broker_stat_interval=999 : Raising this value reduces the frequency of resize operations in slow Data Pump environments.

Table‑level DDL parameters influencing Data Pump

network_link + securefiles : Using network_link with tables that have LOB columns stored as SecureFiles generates a large amount of undo data and can severely degrade performance.

securefiles vs basicfiles : SecureFiles allow parallel export/import of LOB tables, while BasicFiles do not.

Table‑level DML considerations

Concurrent sessions that lock rows or tables can cause the Data Pump engine to wait, making exports of frequently updated tables slower than those of idle tables.

By understanding and adjusting these parameters, DBA teams can significantly reduce Data Pump export/import times and avoid common performance bottlenecks.