Data Pump Export Hanging In RAC At ESTIMATE Phase And Slow Performance (Doc ID 1948926.1)

Last updated on SEPTEMBER 12, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.3 to 11.2.0.4 [Release 11.2]
Information in this document applies to any platform.

Symptoms

You are performing a Data Pump Export (expdp) in a RAC environment on 11.2.0.3 and the export is very slow and seems to take the most time during the estimate phase.

Tracing of the data worker process shows that we are hanging in the following query:

SELECT operation, master_id, state, flags, inst_id, serialnum, 
  NVL(msg_ctrl_queue,'') 
FROM
 gv$datapump_job WHERE owner_name = :1 AND job_name = :2


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        5      0.00       0.00          0          0          0           0
Execute  11243     17.64      63.58          0          0          0           0
Fetch    11242     44.16   11122.06          0          0          0       11242  
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    22490     61.81   11185.65          0          0          0       11242

Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: RULE
Parsing user id: SYS   (recursive depth: 4)
Number of plan statistics captured: 5

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  PX COORDINATOR  (cr=0 pr=0 pw=0 time=147102 us)
         0          0          0   PX SEND QC (RANDOM) :TQ10000 (cr=0 pr=0 pw=0 time=0 us)
         0          0          0    VIEW  GV$DATAPUMP_JOB (cr=0 pr=0 pw=0 time=0 us)
         0          0          0     FIXED TABLE FULL X$KUPVJ (cr=0 pr=0 pw=0 time=0 us)

 

From the export output, we see hanging in the estimation phase.
The TKPROF output from the DW trace shows in the "Elapsed times include waiting on following events" section the  "reliable message" event with very high wait statistics.

EXAMPLE:

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  library cache lock                              1        0.00          0.00
  library cache pin                               1        0.00          0.00
  PX Deq: reap credit                        253636        0.01          5.26
  PX Deq: Join ACK                            22486        0.02         19.52
  PX Deq: Parse Reply                         22486        0.02         14.94
  PX Deq: Execute Reply                       22486        0.02          2.27
  PX Deq: Signal ACK RSG                      22486        0.03          2.70
  reliable message                           777162        1.38      11053.09 <<<<----------------------High waits
  PX Deq: Signal ACK EXT                      22484        0.03          3.62
  IPC send completion sync                    11242        0.00          3.27
  PX Deq: Slave Session Stats                 22484        0.36          3.30
  enq: PS - contention                        11312        0.00          5.92
  KJC: Wait for msg sends to complete          1288        0.00          0.02
  latch: shared pool                             19        0.00          0.00
  latch: enqueue hash chains                     15        0.00          0.00
  latch: ges resource hash list                   8        0.00          0.00
  latch free                                      8        0.00          0.00
********************************************************************************

 

 

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