Oracle data pump import parallel configuration

Question

I am trying to track down a cause for a difference in oracle impdp processing, and I am not finding anything, so I am wondering if anyone here can explain the cause of the differences.

At times I have seen times where using the parallel=x parameter in oracle datapump will cause either multiple tables to be inserted at once, up to the value of 'x', or other times will use parallel threads to import a single table.

I have not been able to track down what might be causing the difference in performance, and I am wondering if anyone has an explanation, or even just a direction to point me to. It would be helpful to determine why it runs in a given matter.

For instance right now I am monitoring a single table, data_only import that was started with parallel=8 in the command, but the import job is only using a single thread to do the import, as shown by the following hint the insert query /+ PARALLEL("XXXXX",1)+/. If the process would use the maximum 8 threads specified to should run much faster.

Answer 1

Two common reasons:

1. Each worker thread uses a seperate dumpfile and requires exclusive access to it. If you have fewer dumpfiles than the degree of parallelism you provided, the parallel threads will not be fully utilized. If you have a single dump file, only 1 worker thread can be active.

2. Tables that contain Basicfile LOB columns are not be exported or imported in parallel. This is a known restriction and documented in: DataPump Export/Import Of LOBs Are Not Executed in Parallel (Doc ID 1467662.1)

Workarounds include:

• seperate partitions can be imported in parallel
• start multiple data pump jobs and distribute data between them using the QUERYparameter.