1

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.

|improve this question
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.
|improve this answer
  • Neither of those reasons seem to fit my situation. The table does not have any LOB columns. The dump that I am using as the source for this import is made up of 10 files. While its possible that the table was written just to a single file, based on the file sizes, that is doubtful. – Patrick Aug 10 '17 at 19:10
  • it is explainable if table is non partitioned and if you look in dba_tables, degree column will have a 1. – Raj Aug 11 '17 at 12:22
  • Raj, I checked and most tables are set to one. Can this be changed? – Patrick Aug 14 '17 at 15:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.