oracle_expdp/oracle_imdp parameter "PARALLEL"

Question

The oracle_expdp_current_lib_ue and oracle_impdp_current_lib_a_ue have PARALLEL=2 and PARALLEL=4 parameters. Can the run time of these procs be reduced by increasing these values?

Answer

The parameter PARALLEL of the Oracle Data Pump utilities expdp (Export) and impdp (Import) specifies the maximum number of processes which are actually executing the export or import job.

My Oracle Support note 365459.1 "Parallel Capabilities of Oracle Data Pump" states the following general guideline for what should be considered when using the PARALLEL parameter:

-----------------------------------------------------
-- Set the degree of parallelism to two times the number of CPUs, then tune from there.
-- For Data Pump Export, the PARALLEL parameter value should be less than or equal to the number of dump files.
-- For Data Pump Import, the PARALLEL parameter value should not be much larger than the number of files in the dump file set.

Users want to optimize the use of Data Pump's parallel capabilities so that export and import jobs run as efficiently as possible.
If the PARALLEL parameter and the wildcard dump file template are used, and the job has a large amount of data (not metadata),
then performance over Original Export and Original Import will be much improved.

Data Pump uses the PARALLEL parameter as the maximum allowed parallelism for the job,
with the maximum number of active workers and Parallel Execution Processes at any given time.
If a user is monitoring the workers, there will be times when workers will be busy and other times when some workers may be idle.
This is expected and means that Data Pump is behaving properly.
-----------------------------------------------------


Conclusion:
===========
(1)
Regarding the export scripts

oracle_expdp_current_lib
oracle_expdp_current_lib_ue
oracle_expdp_table

in the Aleph Upgrade Express Kit this guideline implies the following:

In each of these scripts the parameter DUMPFILE includes the substitution variable %U. This means that Data Pump Export will determine the optimal degree of parallelism for the export job and create enough dump files for the parallel export processes. Therefore the customer should set the parameter PARALLEL in these scripts to twice the number of CPUs on the Oracle database server.


(2)
Regarding the import script oracle_impdp_current_lib_a_ue in the Aleph Upgrade Express Kit this guideline implies that the customer should set the parameter PARALLEL to a value which is not much larger than the number of dump files previously created by the corresponding export step in the Aleph Upgrade Express Kit.

Category: System Management (500)

• Article last edited: 2/17/2015