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

Last updated on JUNE 12, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.3 to 12.1.0.2 [Release 11.2 to 12.1]
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:

USERID='/ as sysdba'
TABLES=CSUSER.REPOS_CLAIM_NEW:CLAIM_P1404
PARTITION_OPTIONS=DEPARTITION
REMAP_TABLE=CSUSER.REPOS_CLAIM_NEW:CLAIM_P1404:CLAIM_P1404
CONTENT=ALL
SKIP_UNUSABLE_INDEXES=YES
DIRECTORY=EXPDP
DUMPFILE=exp_csprod2_refresh_1_%U.dmp
LOGFILE=imp_csprod2_refresh_1.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 fhdbdb01.fairhealth.org (P000)    2     - p000    (Slave)    1    787    10351    1044    8    8
oracle fhdbdb01.fairhealth.org (P001)    2     - p001    (Slave)    1    853    1203    1044    8    8
oracle fhdbdb01.fairhealth.org (P002)    2     - p002    (Slave)    1    915    50799    1044    8    8
oracle fhdbdb01.fairhealth.org (P003)    2     - p003    (Slave)    1    981    11441    1044    8    8
oracle fhdbdb01.fairhealth.org (DW00)    2       SYS     QC        1044    56901    1044            
oracle fhdbdb02.fairhealth.org (P000)    2     - p000    (Slave)    1    788    12009    1044    8    8
oracle fhdbdb02.fairhealth.org (P001)    2     - p001    (Slave)    1    850    50447    1044    8    8
oracle fhdbdb02.fairhealth.org (P002)    2     - p002    (Slave)    1    917    9857    1044    8    8
oracle fhdbdb02.fairhealth.org (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@fhdbdb01.fairhealth.org (TNS V1-V3) 7wn3wubg7gjds
2 11/4/2014 16:17:01 A11P11 SYS ACTIVE DISABLED oracle@fhdbdb02.fairhealth.org (P001) asmy11x7ya7ws
2 11/4/2014 16:17:01 A11P11 SYS ACTIVE DISABLED oracle@fhdbdb02.fairhealth.org (P000) asmy11x7ya7ws
2 11/4/2014 16:17:01 A11P11 SYS ACTIVE DISABLED oracle@fhdbdb02.fairhealth.org (P003) asmy11x7ya7ws
2 11/4/2014 16:17:01 A11P11 SYS ACTIVE DISABLED oracle@fhdbdb02.fairhealth.org (P002) asmy11x7ya7ws
1 11/4/2014 16:17:04 A11P11 SYS ACTIVE ENABLED oracle@fhdbdb01.fairhealth.org (DW00) asmy11x7ya7ws
1 11/4/2014 16:17:07 A11P11 SYS ACTIVE DISABLED oracle@fhdbdb01.fairhealth.org (P001) asmy11x7ya7ws
1 11/4/2014 16:17:07 A11P11 SYS ACTIVE DISABLED oracle@fhdbdb01.fairhealth.org (P000) asmy11x7ya7ws
1 11/4/2014 16:17:07 A11P11 SYS ACTIVE DISABLED oracle@fhdbdb01.fairhealth.org (P003) asmy11x7ya7ws
1 11/4/2014 16:17:07 A11P11 SYS ACTIVE DISABLED oracle@fhdbdb01.fairhealth.org (P002) asmy11x7ya7ws
1 11/4/2014 16:17:04 A11P11 SYS ACTIVE ENABLED oracle@fhdbdb01.fairhealth.org (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                  | CLAIM_P1402     |       |       |            |          |        |      |            |
|   2 |   ERROR LOGGING                  | CLAIM_P1402     |       |       |            |          |        |      |            |
|   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.

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