My Oracle Support Banner

Datapump DB Link Import Is Very Slow at Estimate Phase When PARALLEL_MAX_SERVERS>0 (Doc ID 392689.1)

Last updated on AUGUST 04, 2018

Applies to:

Oracle Server - Enterprise Edition - Version: to - Release: 10.2 to 11.1
Information in this document applies to any platform.
***Checked for relevance on 23-SEP-2011***


Database import via database link is extremely slow when PARALLEL_MAX_SERVERS > 0 is enabled in the source database. Actually, the situation is caused by the estimate phase itself and not by the actual import.

It could be that the same occurs only for the export operation.

Reproducing test case:

connect test/test@SOURCE
drop table test;
create table test
   n number,
   c varchar2(20),
   d date,
   t timestamp
) tablespace users
partition by range(n)
   partition test1 values less than (10),
   partition test2 values less than (20),
   partition test3 values less than (30),
   partition testmax values less than(maxvalue)
insert into test.test values(1,'aaaaaaaaaaaaaaaaaaa', sysdate, sysdate);

  i number;
  for i in 1..23 loop
    insert into test.test select * from test.test;
  end loop;
alter table test.test parallel 4;

-- prepare DB link and directory for import in target database and test it

sqlplus system/<password>
create database link src connect to system identified by <password> using 'SRC';
select username from user_users@src;
create directory dpump_dir as '/home/oracle/expdp';
grant read, write on directory dpump_dir to system;

-- perform network import

#> impdp system/<password> LOGFILE=implink.log DIRECTORY=dpump_dir FULL=Y NETWORK_LINK=SRC PARALLEL=4

-- monitor select statements in SOURCE database

select s.username, a.sql_text, s.status
from   v$session s, v$sqlarea a
where  s.username is not null and
       s.sql_address = a.address;

and you will see more PX sessions executing long running statement


This statement is executed during estimation of export size prior exporting of real data. It consumes unacceptable amount of time. PX slaves are waiting on direct path io - doing full table scans. Using ESTIMATE=STATISTICS has no effect.

The event 10046 at level 12 trace file would show lots of waits on "PX qref latch" event for:

PARSING IN CURSOR #29 len=63 dep=0 uid=5 oct=3 lid=5 tim=1126899056543630 hv=1825333222


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

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