My Oracle Support Banner

Slow DataPump Import (IMPDP) For A Partitioned Table (Doc ID 2014960.1)

Last updated on MAY 20, 2021

Applies to:

Oracle Database Backup Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Cloud Service - Version N/A and later
Oracle Database - Enterprise Edition - Version 11.2.0.3 to 12.1.0.2 [Release 11.2 to 12.1]
Oracle Database Cloud Schema Service - Version N/A and later
Information in this document applies to any platform.

Symptoms

  Extremely slow performance is observed while running an Import DataPump (IMPDP) job against a highly partitioned table with 143 partitions. The import parameter file looks like:

TABLES=<SCHEMA_NAME>.<TABLE_NAME>:<PARTITION_NAME>
PARTITION_OPTIONS=DEPARTITION
REMAP_TABLE=<SCHEMA_NAME>.<TABLE_NAME>:<PARTITION_NAME>:<TABLE_NAME>
CONTENT=ALL
SKIP_UNUSABLE_INDEXES=YES
DIRECTORY=EXPDP
DUMPFILE=<DUMP_NAME>_%U.dmp
LOGFILE=<LOG_NAME>.log
PARALLEL=8
DATA_OPTIONS=skip_constraint_errors

 
  The SQL found in DW00 generated trace file has the following form:

INSERT /*+ APPEND PARALLEL("<table name>",<parallel degree>)+*/ INTO RELATIONAL("<schema name>"."<table name>") ...PARTITION <partition_name>
FROM "ET$...." KU$


  The DataPump job was loading only approx 5% (or approx 5GB) per hour, on a 700GB table. The performance issue is introduced by the fact that the DML workers processes are not running in parallel even after setting up PARALLEL DML and PARALLEL=8.
Even if the parallel slaves are being launched:

PROGRAM    COMMAND    Username    QC/Slave    Slave Set    SID    SERIAL#    QC      SID    REQ_DOP    ACT_DOP
============================================================================================================
oracle <SERVICE_NAME> (P000)    2     - p000    (Slave)    1    787    10351    1044    8    8
oracle <SERVICE_NAME> (P001)    2     - p001    (Slave)    1    853    1203    1044    8    8
oracle <SERVICE_NAME> (P002)    2     - p002    (Slave)    1    915    50799    1044    8    8
oracle <SERVICE_NAME> (P003)    2     - p003    (Slave)    1    981    11441    1044    8    8
oracle <SERVICE_NAME> (DW00)    2       SYS     QC        1044    56901    1044            
oracle <SERVICE_NAME> (P000)    2     - p000    (Slave)    1    788    12009    1044    8    8
oracle <SERVICE_NAME> (P001)    2     - p001    (Slave)    1    850    50447    1044    8    8
oracle <SERVICE_NAME> (P002)    2     - p002    (Slave)    1    917    9857    1044    8    8
oracle <SERVICE_NAME> (P003)    2     - p003    (Slave)    1    980    39721    1044    8    8


the problem is that PARALLEL DML is disabled thus all of those parallel slaves are doing nothing on the INSERT. From below you can very clearly see that the PDML_STATUS is DISABLED:

INST_ID LOGON_TIME USERNAME STATUS PDML_STATUS PROGRAM SQL_ID
===================================================================================================
1 11/4/2014 16:17:03 A11P11 SYS ACTIVE ENABLED udi@<SERVICE_NAME> (TNS V1-V3) 7wn3wubg7gjds
2 11/4/2014 16:17:01 A11P11 SYS ACTIVE DISABLED oracle@<2 11/4/2014 16:17:01 A11P11 SYS ACTIVE DISABLED oracle@<SERVICE_NAME>g (P000) asmy11x7ya7ws
2 11/4/2014 16:17:01 A11P11 SYS ACTIVE DISABLED oracle@<SERVICE_NAME> (P003) asmy11x7ya7ws
2 11/4/2014 16:17:01 A11P11 SYS ACTIVE DISABLED oracle@<SERVICE_NAME> (P002) asmy11x7ya7ws
1 11/4/2014 16:17:04 A11P11 SYS ACTIVE ENABLED oracle@<SERVICE_NAME> (DW00) asmy11x7ya7ws
1 11/4/2014 16:17:07 A11P11 SYS ACTIVE DISABLED oracle@<SERVICE_NAME> (P001) asmy11x7ya7ws
1 11/4/2014 16:17:07 A11P11 SYS ACTIVE DISABLED oracle@<SERVICE_NAME>g (P000) asmy11x7ya7ws
1 11/4/2014 16:17:07 A11P11 SYS ACTIVE DISABLED oracle@<SERVICE_NAME> (P003) asmy11x7ya7ws
1 11/4/2014 16:17:07 A11P11 SYS ACTIVE DISABLED oracle@<SERVICE_NAME> (P002) asmy11x7ya7ws
1 11/4/2014 16:17:04 A11P11 SYS ACTIVE ENABLED oracle@<SERVICE_NAME> (DM00) bjf05cwcj5s6p

 
  As per worker trace, it is confirmed that the insert operation is loading data serially :

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name            | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                 |                 |  8168 |  4817K|     4   (0)| 00:00:01 |        |      |            |
|   1 |  LOAD AS SELECT                  | <NAME>     |       |       |            |          |        |      |            |
|   2 |   ERROR LOGGING                  | <NAME>     |       |       |            |          |        |      |            |
|   3 |    PX COORDINATOR                |                 |       |       |            |          |        |      |            |
|   4 |     PX SEND QC (RANDOM)          | :TQ10000        |  8168 |  4817K|     4   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   5 |      ERROR LOGGING QC (RANDOM)   | :TQ10000        |       |       |     4   (0)| 00:00:01 |  Q1,00 | PCWP | QC (RAND)  |
|   6 |       PX BLOCK ITERATOR          |                 |  8168 |  4817K|     4   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   7 |        EXTERNAL TABLE ACCESS FULL| ET$03D4837F0001 |  8168 |  4817K|     4   (0)| 00:00:01 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------------------

 
and the query coordinator(QC) is running alter session to enable the parallel dml:

 
This information shows that the insert is being serialized as a result of PARALLEL DML being disabled and the performance issue is introduced by this behavior.

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.