DataPump Import (IMPDP) Is Slow When Importing Partitioned Tables (文档 ID 1073195.1) 转到底部转到底部


In this Document
Symptoms
Changes
Cause
Solution
References


APPLIES TO:


Oracle Database - Enterprise Edition - Version 10.1.0.2 to 11.2.0.1 [Release 10.1 to 11.2]
Oracle Database - Enterprise Edition - Version 11.2.0.3 to 11.2.0.3 [Release 11.2]
Oracle Database - Enterprise Edition - Version 11.2.0.4 to 11.2.0.4 [Release 11.2]
Information in this document applies to any platform.
***Checked for relevance on 28-MAR-2013***
SYMPTOMS


When importing partitioned tables into existing partitioned tables with PARALLEL > 1 there will be Worker processes waiting for an exclusive table lock which is indicated by event "enq: TM - contention".


To confirm the issue, execute the following queries during the Impdp client session reports:


Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
 


-- DW process which is holding the exclusive lock
select a.sid, a.type, a.id1, b.program 
from   v$lock a, v$session b 
where  a.sid = b.sid and 
       a.type = 'TM' and 
       a.lmode = 6 and 
       b.program like '%DW%'; 


-- DW process(es) requesting an exclusive lock
select a.sid, a.type, a.id1, b.program, b.event 
from   v$lock a, v$session b
where  a.sid = b.sid and 
       a.type = 'TM' and 
       a.request = 6 and 
       b.program like '%DW%';


-- Affected table 
select c.owner, c.object_name, b.program, b.event 
from   v$lock a, v$session b, dba_objects c 
where  a.sid = b.sid and 
       a.type = 'TM' and 
       a.id1 = c.object_id and 
       a.request = 6 and 
       b.program like '%DW%';
The following example will illustrate the scenario:


impdp tc1/****** DIRECTORY=test_dp DUMPFILE=test%U.dmp tables=tc1.t1 TABLE_EXISTS_ACTION=APPEND PARALLEL=10


Or:


impdp tc1/****** DIRECTORY=test_dp DUMPFILE=test%U.dmp tables=tc1.t1 TABLE_EXISTS_ACTION=TRUNCATE PARALLEL=10


-- There will be a single DW process which is holding an exclusive lock on the table


-- DW process which is holding the exclusive lock
select a.sid, a.type, a.id1, b.program 
from   v$lock a, v$session b
where  a.sid = b.sid and 
       a.type = 'TM' and 
       a.lmode = 6 and 
       b.program like '%DW%';


  SID TY ID1        PROGRAM
----- -- ---------- -------------------------
  115 TM     282361 ORACLE.EXE (DW02)


-- Several other worker processes are waiting until the exclusive lock is released.


-- DW process(es) requesting an exclusive lock
select a.sid, a.type, a.id1, b.program, b.event 
from   v$lock a, v$session b
where  a.sid = b.sid and 
       a.type = 'TM' and 
       a.request = 6 and 
       b.program like '%DW%';


  SID TY ID1        PROGRAM                EVENT 
----- -- ---------- ---------------------- ------------------------
  116 TM     282361 ORACLE.EXE (DW07)      enq: TM - contention
  136 TM     282361 ORACLE.EXE (DW03)      enq: TM - contention
  138 TM     282361 ORACLE.EXE (DW09)      enq: TM - contention
  152 TM     282361 ORACLE.EXE (DW04)      enq: TM - contention


-- The query will report the affected table
select c.owner, c.object_name, b.program, b.event
from   v$lock a, v$session b, dba_objects c
where  a.sid = b.sid and 
       a.type = 'TM' and 
       a.id1 = c.object_id and 
       a.request = 6 and 
       b.program like '%DW%';


OWNER OBJECT_NAME          PROGRAM                 EVENT 
----- -------------------- ----------------------- ---------------------------
TC1   T1                   ORACLE.EXE (DW04)       enq: TM - contention
TC1   T1                   ORACLE.EXE (DW09)       enq: TM - contention
TC1   T1                   ORACLE.EXE (DW03)       enq: TM - contention
TC1   T1                   ORACLE.EXE (DW07)       enq: TM - contention
 


Note: A TM table lock is usually acquired during the execution of a transaction when referencing a table with a DML statement to prevent the object from being dropped or altered during the execution of the transaction.
CHANGES


This issue occurs if the following is true:


- Imported table is a partitioned table
- Data Pump Import (Impdp) uses parameter PARALLEL > 1 and TABLE_EXISTS_ACTION=APPEND or TRUNCATE


CAUSE


This is produced by unpublished Bug 8448327.


SOLUTION


The issue is fixed in release 11.2.0.2 and 12.1. There is no workaround.


Please check, if patch 8448327 is available for your RDBMS version and platform.


Important 


Please read the README file delivered with the patch. There are post-install steps to be completed, otherwise the fix won't work.