I have noticed differences in executions between data pump imports lately, and I am trying to determine what is causing the differences. I am using impdp to move data between databases, and remap the schemas. It seems like I have seen two different things happen when I run the import in parallel, and I am not sure what is causing the differences.

Sometimes I see the job running and it is importing a bunch of tables side by side, other times the import process is only importing a few tables at a time, but using parallel inserts for each table. Using Parallel inserts on just a few tables at a time seems to cut my overall run time substantially, but I can't figure out why it only runs like that sometimes and not others.

  • Check the degree parameter on the tables on which it imports in parallel. You have to carefully distinguish when you say "in parallel". Is it importing with PX threads (due to setting on table)? or importing individual partitions in parallel (you have partitioned table and have used parallel commandline parameter on impdp)? No matter what, if table contains LOB, it will always go in serial mode. Can you try to identify and also look carefully at your impdp command for clues. – Raj May 22 '17 at 19:42
  • Thanks for the feedback. I took a look and found that tables in both environments generally use 'PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT )'. I took a look, and one database has parallel_degree_limit set to 'CPU' the other is set to 32. I am seeing the parallel inserts from the data pump import in the environment with parallel_degree_limit set to CPU. – Patrick May 23 '17 at 12:30

Your Answer

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

Browse other questions tagged or ask your own question.