How to Speed Up EXPDP/IMPDP Data Pump Jobs Performance in Oracle

December 14, 2018

There are some Data Pump Parameters that Significantly affect the export and import Performance. To make expdp and impdp backup faster, you must read this article and set the below parameters based on your requirement and the database configuration. 

Parameters Affecting Datapump Performance:

  • STREAMS_POOL_SIZE

To avoid “STREAMS AQ: ENQUEUE BLOCKED ON LOW MEMORY”  error, you must set the  STREAMS_POOL_SIZE value returned by the result set of the following query.

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');


To Check the Current Settings:
SQL> show parameter STREAMS_POOL_SIZE


To Change the STREAMS_POOL_SIZE:
SQL> ALTER SYSTEM SET STREAMS_POOL_SIZE=100M SCOPE=both;
System altered.
  • CLUSTER=N

In a RAC environment it can improve the speed of Data Pump API based operations.

  • EXCLUDE=STATISTICS

I would recommend to exclude the statistics during export because data pump jobs can hang, sometimes indefinitely, when concurrent statistics generation for a same table is being performed by both the Data Pump engine and by any other users.

  • PARALLEL=Number_of_CPUs

 You can use parallel to speedup the datapump jobs. 

  • AQ_TM_PROCESSES=0

When set explicitly to a value of 0, it can negatively impact the speed of Advanced Queue operations, which in turn can negatively affect Data Pump operations since Data Pump uses Advanced Queueing. Either leave this parameter unset or set this parameter to a value that is greater than 0.

  • PARALLEL_FORCE_LOCAL=TRUE

In a RAC environment it can improve the speed of Data Pump API based operations and avoid some Parallel DML bugs at the same time.

Must Read: