Subscribe YouTube Channel For More Live Tutorials

Oracle 19c introduces the MAX_DATAPUMP_PARALLEL_PER_JOB parameter to control and limit the number of parallel threads a single Data Pump job can use. This parameter enhances resource management, preventing a single Data Pump job from monopolizing the system.

How It Works

Purpose:

Controls the maximum number of parallel worker processes (threads) a single Data Pump job can use.

Ensures equitable resource allocation across multiple concurrent Data Pump jobs in a busy system.

Scope:

The parameter can be set at the system level or for specific PDBs in a multitenant environment.

Default Value:

The default value is 50.

Behavior:

When a Data Pump job is started with the PARALLEL parameter, Oracle checks MAX_DATAPUMP_PARALLEL_PER_JOB.

If the specified PARALLEL value exceeds the MAX_DATAPUMP_PARALLEL_PER_JOB limit, Oracle reduces it to the maximum allowed by this parameter.

Steps to Configure and Use

1. View the Current Setting

To check the current value of MAX_DATAPUMP_PARALLEL_PER_JOB:

SHOW PARAMETER MAX_DATAPUMP_PARALLEL_PER_JOB;

2. Modify the Parameter

You can adjust the parameter at the system level or PDB level.

  • System-Level Setting
ALTER SYSTEM SET MAX_DATAPUMP_PARALLEL_PER_JOB = 20 SCOPE=BOTH;
  • PDB-Level Setting (in Multitenant Environment): Connect to the specific PDB and set the parameter:
ALTER SYSTEM SET MAX_DATAPUMP_PARALLEL_PER_JOB = 10 SCOPE=BOTH;

3. Start a Data Pump Job

When starting a Data Pump export or import job, you can specify the PARALLEL parameter:

When starting a Data Pump export or import job, you can specify the PARALLEL parameter:

expdp user/password DIRECTORY=dp_dir DUMPFILE=file_%U.dmp PARALLEL=30

If PARALLEL=30 exceeds MAX_DATAPUMP_PARALLEL_PER_JOB=20, Oracle automatically limits the job to 20 worker processes.

Best Practices

  • Set Reasonable Limits:
    • Avoid setting the value too high to prevent resource contention.
    • Consider the number of CPU cores and available I/O bandwidth.
  • Monitor Resource Usage:
    • Use DBA_DATAPUMP_JOBS and OS-level tools to observe CPU and I/O usage during Data Pump operations.
  • Multitenant Environments:
    • Configure MAX_DATAPUMP_PARALLEL_PER_JOB individually for each PDB to prevent one tenant from monopolizing system resources.