Datapump Import With Both PARALLEL>0 And CONTENT=METADATA_ONLY Has Bad Performance (Doc ID 1467188.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.3 and later
Information in this document applies to any platform.

Symptoms

When running a DataPump import job with only one worker (DW, for example, when metadata is imported), the performance is slow when PARALLEL>0.

 

Datapump import parameter PARALLEL>0 and CONTENT=METADATA_ONLY:

Parameter file:
   CONTENT=METADATA_ONLY
   PARALLEL=6

 

Log file:
   Longest time spent on following:
   Processing object type SCHEMA_EXPORT/TABLE/TABLE
        Completed 4448 TABLE objects in 502 seconds
   Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
        Completed 22703 INDEX objects in 4073 seconds

This shows that the DataPump import of metadata took 68 minutes for creating indexes.

SQL Tracing shows the database worker is spending most of the time on the following:

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
   PX Deq Credit: send blkd                   183984        1.99         75.07
   PX qref latch                                3153        0.06          0.96
   PX Deq: Join ACK                           272436        0.28       1307.80
   PX Deq: Parse Reply                        272436        0.25       1067.83
   PX Deq: Execute Reply                     1089744        1.51         72.39
   PX Deq: Table Q qref                       136218        0.00          3.24
   PX Deq: Signal ACK EXT                     272436        0.01         13.13
   PX Deq: Slave Session Stats                272436        0.02         21.89

 

Datapump import parameter without PARALLEL and only CONTENT=METADATA_ONLY:

Parameter file:
   CONTENT=METADATA_ONLY

 

   Longest time spent on following:
   Processing object type SCHEMA_EXPORT/TABLE/TABLE
        Completed 4448 TABLE objects in 580 seconds
   Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
        Completed 22703 INDEX objects in 1244 seconds

This shows that the DataPump import of metadata took 21 minutes for creating indexes.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms